Page 1 of 2
excel import question
Posted: Tue Nov 22, 2022 10:15 am
by BruceN
I import a csv file into excel ( oBook := oExcel:workbooks:Open(cSDir+ '\' + infile) )
Everything works, except that I have a text field in the csv file I create and import that contains what appears to be a large number (it's actually a UPC code.) When excel loads the csv file, it converts the UPC to scientific notation (like 8.2134E+11.) I tried wrapping the number in single quotes, but that gets loaded with the quotes ( '82134576544'.)
How can I format the UPC in the csv so excel will know it's text not number?
thanks...
bruce
Re: excel import question
Posted: Tue Nov 22, 2022 11:55 pm
by Koverhage
I think you should use
"'"+cUPCCode (" + ' + ")
Re: excel import question
Posted: Wed Nov 23, 2022 1:15 am
by k-insis
OP you might use formatting after import ?
oSheet := oBook:ActiveSheet
oSheet:Columns("C"):NumberFormat := "######################" //unsure about correct format for UPC
Koverhage wrote: ↑Tue Nov 22, 2022 11:55 pm
I think you should use
"'"+cUPCCode (" + ' + ")
Re: excel import question
Posted: Wed Nov 23, 2022 7:59 am
by Koverhage
before
the ' should be the first char of the upc string in the csv file
Re: excel import question
Posted: Wed Nov 23, 2022 10:17 am
by BruceN
I don't understand... UPC code is in the middle of a string of values separated by commas
If upc for an item is '1234567890' then the line would look like:
[format]
productID, price, vendor, UPC, description, date sold
[data]
ITEM123, 45.75, VENDORNAME, 1234567890, Left Handed Metric Widget, 10/15/2022
The price comes in as number, upc comes in as number not text.
Re: excel import question
Posted: Thu Nov 24, 2022 12:12 am
by Koverhage
Bruce,
[data]
ITEM123, 45.75, VENDORNAME, 1234567890, Left Handed Metric Widget, 10/15/2022
The price comes in as number, upc comes in as number not text.
No matter what valeus upc has.
Your delimiter is comma
you must send the upc as a string + the first character '
[data]
ITEM123, 45.75, VENDORNAME, "'"+str(1234567890), Left Handed Metric Widget, 10/15/2022
The price comes in as number, upc comes in as number not text.
Re: excel import question
Posted: Thu Nov 24, 2022 6:14 am
by BruceN
It IS a string... the field I get the data from in the dbf file is a character field (CHAR 20) and the variable I use is a character (local cUPC :="")
Re: excel import question
Posted: Thu Nov 24, 2022 8:07 am
by Koverhage
Local cUPC := chr(39) + dbf->UPC
The ' forces Excel to use the value as text
Re: excel import question
Posted: Thu Nov 24, 2022 8:29 am
by BruceN
Thanks.. I'll try that tomorrow - it's turkey day here and have family over
Re: excel import question
Posted: Fri Nov 25, 2022 1:21 pm
by BruceN
RESULTS......
If I wrap the upc string with single quotes, it goes as text... but with the quotes around it (shows in excel as '1234567890', not just the numbers. If I try chr(39) before the string, it shows as the number with a single quote before it.
if I try the column formatting, it crashes on that line (it actually is column D, not C)
oBook := oExcel:workbooks:Open(cSDir+ '\' + infile) // loads excel and date fine
oBook:Columns("D"):NumberFormat := "#################" // (crash)
also tried:
oBook:Columns("D"):TextFormat := "!!!!!!!!!!!!!!!!!!!!"
and it crashed as well