excel import question

[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

[Koverhage]

I think you should use
"'"+cUPCCode (" + ' + ")

[k-insis]

OP you might use formatting after import ?

oSheet := oBook:ActiveSheet
oSheet:Columns("C"):NumberFormat := "######################" //unsure about correct format for UPC

I think you should use
"'"+cUPCCode (" + ' + ")

[Koverhage]

before
the ' should be the first char of the upc string in the csv file

[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.

[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.

[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 :="")

[Koverhage]

Local cUPC := chr(39) + dbf->UPC
The ' forces Excel to use the value as text

[BruceN]

Thanks.. I'll try that tomorrow - it's turkey day here and have family over

[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