Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  nodelist  faq  login

Never make anything simple and efficient when a way can be found to make it complex and wonderful.


programming / comp.lang.tcl / Re: Excel to CSV - unexpected implicit conversions

SubjectAuthor
* Excel to CSV - unexpected implicit conversionsaldo.w....@gmail.com
+- Re: Excel to CSV - unexpected implicit conversionsRalf Fassel
+* Re: Excel to CSV - unexpected implicit conversionsapn
|`* Re: Excel to CSV - unexpected implicit conversionsaldo.w....@gmail.com
| `- Re: Excel to CSV - unexpected implicit conversionsAshok
`* Re: Excel to CSV - unexpected implicit conversionsPaul Obermeier
 `- Re: Excel to CSV - unexpected implicit conversionsALX

1
Subject: Excel to CSV - unexpected implicit conversions
From: aldo.w....@gmail.com
Newsgroups: comp.lang.tcl
Date: Thu, 28 Apr 2022 23:04 UTC
X-Received: by 2002:ad4:5a03:0:b0:456:5533:4ab1 with SMTP id ei3-20020ad45a03000000b0045655334ab1mr6112587qvb.24.1651187059105;
Thu, 28 Apr 2022 16:04:19 -0700 (PDT)
X-Received: by 2002:a05:6870:a707:b0:e2:cc85:d98 with SMTP id
g7-20020a056870a70700b000e2cc850d98mr220423oam.131.1651187058802; Thu, 28 Apr
2022 16:04:18 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.tcl
Date: Thu, 28 Apr 2022 16:04:18 -0700 (PDT)
Injection-Info: google-groups.googlegroups.com; posting-host=93.44.166.97; posting-account=CpQfUQoAAACWuSZdT5zfmIK7a0FfbQ0B
NNTP-Posting-Host: 93.44.166.97
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
Subject: Excel to CSV - unexpected implicit conversions
From: aldo.w.b...@gmail.com (aldo.w....@gmail.com)
Injection-Date: Thu, 28 Apr 2022 23:04:19 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 25
View all headers
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


Subject: Re: Excel to CSV - unexpected implicit conversions
From: Ralf Fassel
Newsgroups: comp.lang.tcl
Date: Fri, 29 Apr 2022 08:15 UTC
References: 1
Path: i2pn2.org!i2pn.org!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: ralf...@gmx.de (Ralf Fassel)
Newsgroups: comp.lang.tcl
Subject: Re: Excel to CSV - unexpected implicit conversions
Date: Fri, 29 Apr 2022 10:15:09 +0200
Lines: 22
Message-ID: <yga5ymstieq.fsf@akutech.de>
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain
X-Trace: individual.net imq/cknADC2QiTduotqffgkdwKj257vPD3jfMOE4DLeS/KHsY=
Cancel-Lock: sha1:YftF9zH1PY2dI3mnS7cXlAxv4QA= sha1:xFuPKAO/v+WNhaUAp26AFww1Wyg=
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.3 (gnu/linux)
View all headers
* "aldo.w....@gmail.com" <aldo.w.buratti@gmail.com>
| # --------
| 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'


Subject: Re: Excel to CSV - unexpected implicit conversions
From: apn
Newsgroups: comp.lang.tcl
Organization: A noiseless patient Spider
Date: Fri, 29 Apr 2022 10:16 UTC
References: 1
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: palm...@yahoo.com (apn)
Newsgroups: comp.lang.tcl
Subject: Re: Excel to CSV - unexpected implicit conversions
Date: Fri, 29 Apr 2022 15:46:50 +0530
Organization: A noiseless patient Spider
Lines: 35
Message-ID: <t4gduk$ki9$1@dont-email.me>
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 29 Apr 2022 10:16:52 -0000 (UTC)
Injection-Info: reader02.eternal-september.org; posting-host="70f1ee2521c75dc4769364e829adbd95";
logging-data="21065"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/0xfZ/p9XYfPKGObm3WMIM"
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
Thunderbird/91.7.0
Cancel-Lock: sha1:gK52q2tPi37HUlgXCBXoceoiXIg=
In-Reply-To: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
Content-Language: en-US
View all headers
On 4/29/2022 4:34 AM, aldo.w....@gmail.com 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


Subject: Re: Excel to CSV - unexpected implicit conversions
From: aldo.w....@gmail.com
Newsgroups: comp.lang.tcl
Date: Fri, 29 Apr 2022 16:22 UTC
References: 1 2
X-Received: by 2002:a05:6214:19ca:b0:456:39e3:d4a0 with SMTP id j10-20020a05621419ca00b0045639e3d4a0mr18494187qvc.114.1651249358191;
Fri, 29 Apr 2022 09:22:38 -0700 (PDT)
X-Received: by 2002:a05:6830:1489:b0:605:e8f6:5047 with SMTP id
s9-20020a056830148900b00605e8f65047mr37865otq.185.1651249357910; Fri, 29 Apr
2022 09:22:37 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.tcl
Date: Fri, 29 Apr 2022 09:22:37 -0700 (PDT)
In-Reply-To: <t4gduk$ki9$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=5.171.9.56; posting-account=CpQfUQoAAACWuSZdT5zfmIK7a0FfbQ0B
NNTP-Posting-Host: 5.171.9.56
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com> <t4gduk$ki9$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ad170178-1874-470b-b772-d9921a713b4fn@googlegroups.com>
Subject: Re: Excel to CSV - unexpected implicit conversions
From: aldo.w.b...@gmail.com (aldo.w....@gmail.com)
Injection-Date: Fri, 29 Apr 2022 16:22:38 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 53
View all headers
Il giorno venerdì 29 aprile 2022 alle 12:16:56 UTC+2 apn ha scritto:
On 4/29/2022 4:34 AM, aldo.w....@gmail.com 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 ?


Subject: Re: Excel to CSV - unexpected implicit conversions
From: Ashok
Newsgroups: comp.lang.tcl
Organization: A noiseless patient Spider
Date: Fri, 29 Apr 2022 17:09 UTC
References: 1 2 3
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: palm...@yahoo.com (Ashok)
Newsgroups: comp.lang.tcl
Subject: Re: Excel to CSV - unexpected implicit conversions
Date: Fri, 29 Apr 2022 22:39:32 +0530
Organization: A noiseless patient Spider
Lines: 30
Message-ID: <t4h64g$uc2$1@dont-email.me>
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
<t4gduk$ki9$1@dont-email.me>
<ad170178-1874-470b-b772-d9921a713b4fn@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 29 Apr 2022 17:09:36 -0000 (UTC)
Injection-Info: reader02.eternal-september.org; posting-host="70f1ee2521c75dc4769364e829adbd95";
logging-data="31106"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1//cUaTjlTVatxLWt/BY4zx"
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
Thunderbird/91.8.0
Cancel-Lock: sha1:FcsA13PFCsJSemBPZmZbhDcDqWc=
In-Reply-To: <ad170178-1874-470b-b772-d9921a713b4fn@googlegroups.com>
Content-Language: en-US
View all headers
On 4/29/2022 9:52 PM, aldo.w....@gmail.com 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.


Subject: Re: Excel to CSV - unexpected implicit conversions
From: Paul Obermeier
Newsgroups: comp.lang.tcl
Organization: A noiseless patient Spider
Date: Fri, 29 Apr 2022 18:40 UTC
References: 1
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: oberme...@poSoft.de (Paul Obermeier)
Newsgroups: comp.lang.tcl
Subject: Re: Excel to CSV - unexpected implicit conversions
Date: Fri, 29 Apr 2022 20:40:37 +0200
Organization: A noiseless patient Spider
Lines: 44
Message-ID: <t4hbf5$bgt$1@dont-email.me>
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 29 Apr 2022 18:40:37 -0000 (UTC)
Injection-Info: reader02.eternal-september.org; posting-host="8cc22c9bef212186598e914a34efe552";
logging-data="11805"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX188Gh98fX6pkXC1aMmUm06lpdCprDyHtAo="
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101
Thunderbird/91.8.1
Cancel-Lock: sha1:x3jeSB+e0PIuySf12fzUCn8Qbf8=
In-Reply-To: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com>
View all headers
Am 29.04.2022 um 01:04 schrieb aldo.w....@gmail.com:
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




Subject: Re: Excel to CSV - unexpected implicit conversions
From: ALX
Newsgroups: comp.lang.tcl
Date: Fri, 29 Apr 2022 22:25 UTC
References: 1 2
X-Received: by 2002:a05:6214:29cf:b0:458:568:1337 with SMTP id gh15-20020a05621429cf00b0045805681337mr994750qvb.93.1651271148896;
Fri, 29 Apr 2022 15:25:48 -0700 (PDT)
X-Received: by 2002:a05:6870:c5a5:b0:da:ab36:48dd with SMTP id
ba37-20020a056870c5a500b000daab3648ddmr675365oab.150.1651271148622; Fri, 29
Apr 2022 15:25:48 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.lang.tcl
Date: Fri, 29 Apr 2022 15:25:48 -0700 (PDT)
In-Reply-To: <t4hbf5$bgt$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=2a01:598:c82e:b37f:b487:8b76:ff3b:e35c;
posting-account=K0QZTwoAAAAwQRJto1jj7aZo6CrAijkw
NNTP-Posting-Host: 2a01:598:c82e:b37f:b487:8b76:ff3b:e35c
References: <7e8de465-df2c-4071-afc5-b4e5c08aa3b1n@googlegroups.com> <t4hbf5$bgt$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <a165dd0b-020b-4263-9383-e096d2972c31n@googlegroups.com>
Subject: Re: Excel to CSV - unexpected implicit conversions
From: a.scho...@gmx.de (ALX)
Injection-Date: Fri, 29 Apr 2022 22:25:48 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 7
View all headers
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


1
rocksolid light 0.7.2
clearneti2ptor