CSVs and leading zeros dropping

Folks,
=20
I have a report which exports a category code with leading zeros to a =
csv.
When opening this csv file in excel, excel drops the leading zeros. =
There
does not appear to be a global setting in Excel where I can set the =
format as
being text rather than 'General' which assumes numeric and drops the =
leading
zero. I have tried putting leading characters on the category code (') =
and
this appears on excel file.=20
=20
Any thoughts ???
=20
Regards,
=20
James
=20
James Halligan
Senior Applications Consultant
Software Resources Ltd,
Heron House,
Corrig Road,
Sandyford,
Dublin 18.
=20
Phone +(353) 1 207 4757 (Direct)
+(353) 1 207 4700 (Switchboard)
Fax +(353) 1 207 4777=20
Email <mailto:[email protected]> [email protected]
Web <http://www.srl.ie/> www.srl.ie
=20
=20
<http://www.srl.ie/Training_schedule.htm> =20
=20
"So shines a good deed in a weary world"=20
=20

I'm guessing that your variable is a text field in JDE? If so, try left padding with at least one space.

Excel also offers a import wizard that allows you to change the format of the column in question. Instead of opening the *.CVS file by double clicking on it, open excel and use the following steps:

1. Select "Data", "Get External Data", "Import Text File" from the menu bar at the top of the screen in Excel. (Note: You may have to change the file type from *.TXT to All Files.)

2. Select your *.CVS file and click "Import". This will launch the import wizard.

3. On the first screen, check the delimited box and click next.

4. On the following screen, check "Comma" as your delimiter. You will see a preview of how your report will look in excel. Click next.

5. On the next screen, you will see another preview of your report. Find the column with the leading Zeros and highlight it. With this column highlighted, click the "Text" box located above the preview. Notice that the column header changes from "General" to "Text". Repeat for all columns you wish to import in text format.

6. Click Finish.

Hope this helps.

If this is a report that you use on a regular basis your best option may be to use VBA to help you format the report. We have some reports with the problem that you describe and also another problem. Sometimes Excel will format the entire column based on the first value pushed into the row. If the data in the column is text but the first instance (row) contains all numbers, Excel may drop the text data in the column from the spreadsheet.

This is what we have done in the past:
In the UBE that creates the CSV file, add some special character before the problem data (I've used an underscore).
Create a Spreadsheet to open the CSV file and also create a second spreadsheet based on the first. As you read in the CSV file you can remove the underscore and format the cell or column.

If you need more information you can contact me off-line.

dave