Excel column format problem

BrianSmith

New Member
I export data from Progress to a 'csv' file,
eg
"Home Depot", 1234.45,"00012345"
"Radio Shack", 313.23,"00313333"
"London Drugs", 3554.44,"DEBITMEMO"

When I open this csv file with Excel from within Progress, the 3rd column is losing the leading zeros. This column is alphnumeric and should retain the leading zeros. If I format the 3rd column in Excel after the file is opened, the leading zeros are still gone.
How can I get these leading zeros into Excel?

Thanks, Brian
 

yizhi

New Member
I am sure there are better ways to handle this, but this is what I do.


put unformatted '="' + abc + '"'.

Hope it helps.
good luck.

Yizhi Wang
Leatherman
 

StefGay

Member
Hi,

you can also use "sylk" formart.
Create a .slk file like this

ID;PNP
C;Y1;X1;K"0001"
E

and open it with excel. In this example the number 0001 is consider as "text" for excel but perhaps there are other SYLK command you can use to perform a "real" formatting.

Regards.

Stéphane.
 

LBaliao

Member
Yizhi,

put unformatted '="' + abc + '"'.

This code resolved my issue perfectly. I needed Excel to stop removing leading zeroes. Thank you so much!

Liza:D
 

jdrouil

New Member
Here is another way.

Here is a sed unix command that works to make CSV output better. Solves the issue where numeric values in character fields get leading values chopped and large ones get turned into exponential mishmash.

Insert an equal sign in front of character fields, those enclosed in double quotes. Changes character fields from "xxx" to ="xxx". Need to run the command twice, once for the first field on a line, again for any further fields on a line.


(after creating and closing an export file filenm.csv using
export stream strnm delimiter "," command)

assign sed-cmd = "'" + 's!^\"!\="!g' + "'".
unix silent value("sed " + sed-cmd) filenm.csv > fileyy.csv.
assign sed-cmd = "'" + 's!,\"!,\="!g' + "'".
unix silent value("sed " + sed-cmd) fileyy.csv > filezz.csv.
unix silent mv filezz.csv filenm.csv.
unix silent rm fileyy.csv.




__________________________________________________________
Joseph A. Drouillard
Principal/Consultant
Avenue Systems LLC
40928 Kingsley Ln
Novi, Michigan 48377
248-894-7093
joe@avenue-systems.com
 

Sachin Acharya

New Member
Hi,

The mentioned fix resolved solution for truncation leading zero's (It retains the leading zero's after exporting to excel). Even though it throws an error for suppose the field content is having double quote (") in it. The whole content does not get exported to excel, instead it throws an error. Any comments on this ?
 
Top