I just want to convert Excel files (*.xls) to csv in a batch way.
Here is the proc I'm using (using cawt package)
# ------------------------------
package require cawt
proc xls2csv {xlsFile sheetIdx csvFile} {
set appId [Excel::OpenNew false]
set workbookId [Excel::OpenWorkbook $appId $xlsFile]
set worksheetId [Excel::GetWorksheetIdByIndex $workbookId $sheetIdx]
Excel::SaveAsCsv $workbookId $worksheetId $csvFile xlCSV

Excel::Close $workbookId
Excel::Quit $appId false
}
# --------
BUT the resulting csv is not what I'm expecting. ...
Here are the problems:
1) csv separator is "," . I want a ";" ( solved with a post-processing with the "csv" packaged")
2) Large numbers are printed as "1,234.56" ( I'd like 1234.56)
3) Dates are printed as "mm/dd/yyyy" e.g. "12/31/2000" (I'd like "31/12/2000)

I'm a bit frustated with these implicit conversions, and I didn't find a way with "cawt", nor with other tools (I also tried vbs scripts) to tweak these conversions.

Did anybody experienced and solved these "internationalization" problems ?

Thanks
ABU

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

* "[email protected]" <[email protected]>
| # --------
| BUT the resulting csv is not what I'm expecting. ...
| Here are the problems:
| 1) csv separator is "," . I want a ";" ( solved with a post-processing with the "csv" packaged")
| 2) Large numbers are printed as "1,234.56" ( I'd like 1234.56)
| 3) Dates are printed as "mm/dd/yyyy" e.g. "12/31/2000" (I'd like "31/12/2000)

| I'm a bit frustated with these implicit conversions, and I didn't find
| a way with "cawt", nor with other tools (I also tried vbs scripts) to
| tweak these conversions.

| Did anybody experienced and solved these "internationalization" problems ?

I think you need to set this in Excel in some Options dialog.

https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

Note the section "Change the default list separator". If you're lucky,
you make the changes once interactively and they persist for batch use.

R'

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 4/29/2022 4:34 AM, [email protected] wrote:

I just want to convert Excel files (*.xls) to csv in a batch way.
Here is the proc I'm using (using cawt package)
# ------------------------------
package require cawt
proc xls2csv {xlsFile sheetIdx csvFile} {
set appId [Excel::OpenNew false]
set workbookId [Excel::OpenWorkbook $appId $xlsFile]
set worksheetId [Excel::GetWorksheetIdByIndex $workbookId $sheetIdx]
Excel::SaveAsCsv $workbookId $worksheetId $csvFile xlCSV

Excel::Close $workbookId
Excel::Quit $appId false
}
# --------
BUT the resulting csv is not what I'm expecting. ...
Here are the problems:
1) csv separator is "," . I want a ";" ( solved with a post-processing with the "csv" packaged")
2) Large numbers are printed as "1,234.56" ( I'd like 1234.56)
3) Dates are printed as "mm/dd/yyyy" e.g. "12/31/2000" (I'd like "31/12/2000)

I'm a bit frustated with these implicit conversions, and I didn't find a way with "cawt", nor with other tools (I also tried vbs scripts) to tweak these conversions.

Did anybody experienced and solved these "internationalization" problems ?

Thanks
ABU

Given Excel's propensity to use control panel regional settings, I think
you would be happier not exporting directly from Excel if you want a
"standard" locale-independent format. Instead get the data via cawt's GetRow*/GetTable* methods and then output the csv yourself using either
the tclcsv extension or the csv module in tcllib.

/Ashok

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 4/29/2022 9:52 PM, [email protected] wrote:

Excel::GetRowValues only partially solves these problems ..
Now large numbers like "1.234,56" (Italian notation) are printed as "1234.56 (good)
but
dates like "30/07/2018" e.g 30-JULY-2018) are printed as numbers ( -> 43311.0 ) and when the resulting csv is opened in Excel these dates are rendered as "07/30/2018" (which is not a valid date (at least in Italy)).

I could have been clearer. What I should have said is that after you use GetRow* etc. you will get back data as a list as COM data values. You
can then format them as you wish. In most cases the formatting will be a
no-op but for some types you need to do explicit formatting. For
example, the Date type in COM is actually a double with the integer part
being days since some epoch (don't recall which) and the fractional part
is the fraction of 24 hours representing time. You can convert it to
something more palatable in Tcl with twapi's (since you are already
using CAWT) variant_time_to_timelist command

(bin) 2 % twapi::variant_time_to_timelist 43311.0
2018 7 30 0 0 0 0 1

You can then format to taste.

I wonder why when I manually, open "a.xsl" and then do "Save as.." " a.csv", the data are correct, whilst there's no way to do this with an automation ... is there same missing Excel API ?

There is a parameter to control this to a limited extent, though I am
not sure if that is exposed through CAWT or not. It controls whether the
export uses the local regional settings as in control panel or some
fixed syntax. The latter may not match what you are looking for either
hence my opinion that formatting yourself is the safer option.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Il giorno venerdì 29 aprile 2022 alle 12:16:56 UTC+2 apn ha scritto:

On 4/29/2022 4:34 AM, [email protected] wrote:

I just want to convert Excel files (*.xls) to csv in a batch way.
Here is the proc I'm using (using cawt package)
# ------------------------------
package require cawt
proc xls2csv {xlsFile sheetIdx csvFile} {
set appId [Excel::OpenNew false]
set workbookId [Excel::OpenWorkbook $appId $xlsFile]
set worksheetId [Excel::GetWorksheetIdByIndex $workbookId $sheetIdx] Excel::SaveAsCsv $workbookId $worksheetId $csvFile xlCSV

Excel::Close $workbookId
Excel::Quit $appId false
}
# --------
BUT the resulting csv is not what I'm expecting. ...
Here are the problems:
1) csv separator is "," . I want a ";" ( solved with a post-processing with the "csv" packaged")
2) Large numbers are printed as "1,234.56" ( I'd like 1234.56)
3) Dates are printed as "mm/dd/yyyy" e.g. "12/31/2000" (I'd like "31/12/2000)

I'm a bit frustated with these implicit conversions, and I didn't find a way with "cawt", nor with other tools (I also tried vbs scripts) to tweak these conversions.

Did anybody experienced and solved these "internationalization" problems ?

Thanks
ABU

Given Excel's propensity to use control panel regional settings, I think
you would be happier not exporting directly from Excel if you want a "standard" locale-independent format. Instead get the data via cawt's GetRow*/GetTable* methods and then output the csv yourself using either
the tclcsv extension or the csv module in tcllib.

/Ashok

Excel::GetRowValues only partially solves these problems ..
Now large numbers like "1.234,56" (Italian notation) are printed as "1234.56 (good)
but
dates like "30/07/2018" e.g 30-JULY-2018) are printed as numbers ( -> 43311.0 ) and when the resulting csv is opened in Excel these dates are rendered as "07/30/2018" (which is not a valid date (at least in Italy)).

I wonder why when I manually, open "a.xsl" and then do "Save as.." " a.csv", the data are correct, whilst there's no way to do this with an automation ... is there same missing Excel API ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 29.04.2022 um 01:04 schrieb [email protected]:

I just want to convert Excel files (*.xls) to csv in a batch way.
Here is the proc I'm using (using cawt package)
# ------------------------------
package require cawt
proc xls2csv {xlsFile sheetIdx csvFile} {
set appId [Excel::OpenNew false]
set workbookId [Excel::OpenWorkbook $appId $xlsFile]
set worksheetId [Excel::GetWorksheetIdByIndex $workbookId $sheetIdx]
Excel::SaveAsCsv $workbookId $worksheetId $csvFile xlCSV

Excel::Close $workbookId
Excel::Quit $appId false
}
# --------
BUT the resulting csv is not what I'm expecting. ...
Here are the problems:
1) csv separator is "," . I want a ";" ( solved with a post-processing with the "csv" packaged")
2) Large numbers are printed as "1,234.56" ( I'd like 1234.56)
3) Dates are printed as "mm/dd/yyyy" e.g. "12/31/2000" (I'd like "31/12/2000)

I'm a bit frustated with these implicit conversions, and I didn't find a way with "cawt", nor with other tools (I also tried vbs scripts) to tweak these conversions.

Did anybody experienced and solved these "internationalization" problems ?

Thanks
ABU

Hi Aldo,

the only option in the Worksheet.SaveAs method, which might have influence, is the Local parameter:
(see https://docs.microsoft.com/de-de/office/vba/api/excel.worksheet.saveas)

This parameter is False by default.

So set the Local parameter to true in the call of SaveAs in CAWT procedure SaveAsCsv and see what happens:
$worksheetId -callnamedargs SaveAs \
Local [Cawt TclBool true] \
Filename $fileName \
FileFormat [Excel GetEnum $fmt]

Paul

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

You can use ooxml to import excel files. You get a tcl array, which you can export as csv afterwards.
https://fossil.sowaswie.de/ooxml

package require ooxml
array set workbook [ooxml::xl_read -datefmt "%Y-%m-%d %H:%M:%S" excel.xlsx] parray workbook

Alex

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)