Export to CSV and leading zeros

jimmymac

Reputable Poster
We are on 9.0, tools 8.98.42.

We have a report that is defined to export to csv. However, I've just noticed that values that might have a leading zero, such as Tax ID or Zip code, when the csv is opened in Excel those leading zeros are gone.

I know its attribute of the excel columns I guess, but just changing the column formatting after its displayed doesn't do anything.

Anyone run across this before and have any suggestion on how to not lose those leading zeros?

Thanks.
 

jdecoder

Well Known Member
try using Excel formula
Example : "=RIGHT(F7,LEN(F7)-1)"

Will only work with UBE not exports...

but you can have a column with the formula and have a custom gird and make it public using user preference...
 

eydeak

Reputable Poster
It is usually Excel which is dropping the leading zeros in fields it thinks are numeric. Excel thinks it is smart and if it looks like a number treats it like a number.

You can verify whether or not the leading zeros are in the CSV by opening the CSV file in notepad first.

You can force Excel to open it the way you want by changing the extension to TXT. You can no longer double click the file to open it in Excel, but if you start Excel and do a file open it will walk you through the text import wizard. Make sure you identify any column with significant leading zeros as text.

You can try posting something in a Microsoft Excel forum somewhere. It's been a few years since I checked those forums to see if there is a way to turn off that feature in Excel.
 

sschroth69

Member
Try something like this:

RV LITM = concat(concat('="',rtrim([BC 2nd Item Number],' ')),'"')

I used it all the time because we had leading zeros in our part numbers. I found this info many years ago courtesy of another post on JDELIST.

It's hard to read the quotes so I cut and paste the expression when I need it.
 

Gov

Guest
With my past experience I guess the double quotes shows up in CSV if you directly use them. Following is the code we used, if my memory serves correct it worked.

// Write record in csv file.
VA evt_szStartingQ_DL01 = "=(""
VA evt_szEndingQ_DL01 = "")"
//
RV Vendor Lot Display_DL01 = concat(concat([VA evt_szStartingQ_DL01],rtrim([BC VENDOR LOT NO. (F57MPSRL)(57VLOT)]," ")),[VA evt_szEndingQ_DL01])
 

Natarajan.S

Member
Hi jimmymac,

I cameacross this issue in our E1 system where report truncates the preceding 0's in the order#. (Ex 009845)

Solution:
Concat single quote leading the variable in E1 report hence there is no modification required in Excel separately.

i.e Order number = concat (" ' ",Ordernumber)
(Ex: '009845)

You can test directly in an excel.

1. Open excel
2. Type 02 in a cell and hit enter. Result will be 2
3. Type '02 in a cell and hit enter. Result will be 02
 
Top