excel import question

This forum is for general support of Xbase++
Message
Author
BruceN
Posts: 280
Joined: Thu Jan 28, 2010 7:46 am
Location: Slidell, LA

excel import question

#1 Post 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
There are only 10 kinds of people - those who understand binary and those who don't :)

Koverhage
Posts: 150
Joined: Mon Feb 01, 2010 8:45 am

Re: excel import question

#2 Post by Koverhage »

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

k-insis
Posts: 94
Joined: Fri Jan 28, 2011 4:07 am

Re: excel import question

#3 Post 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 (" + ' + ")

Koverhage
Posts: 150
Joined: Mon Feb 01, 2010 8:45 am

Re: excel import question

#4 Post by Koverhage »

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

BruceN
Posts: 280
Joined: Thu Jan 28, 2010 7:46 am
Location: Slidell, LA

Re: excel import question

#5 Post 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.
There are only 10 kinds of people - those who understand binary and those who don't :)

Koverhage
Posts: 150
Joined: Mon Feb 01, 2010 8:45 am

Re: excel import question

#6 Post 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.
Klaus

BruceN
Posts: 280
Joined: Thu Jan 28, 2010 7:46 am
Location: Slidell, LA

Re: excel import question

#7 Post 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 :="")
There are only 10 kinds of people - those who understand binary and those who don't :)

Koverhage
Posts: 150
Joined: Mon Feb 01, 2010 8:45 am

Re: excel import question

#8 Post by Koverhage »

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

BruceN
Posts: 280
Joined: Thu Jan 28, 2010 7:46 am
Location: Slidell, LA

Re: excel import question

#9 Post by BruceN »

Thanks.. I'll try that tomorrow - it's turkey day here and have family over :)
There are only 10 kinds of people - those who understand binary and those who don't :)

BruceN
Posts: 280
Joined: Thu Jan 28, 2010 7:46 am
Location: Slidell, LA

Re: excel import question

#10 Post 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
There are only 10 kinds of people - those who understand binary and those who don't :)

Post Reply