Page 1 of 1
Truncating zero's in .csv(excel file)
Posted: Tue Apr 29, 2008 3:17 pm
by tom
Hi,
When I am writing to a .csv file through datastage with varchar data which contains the value as
0000018
0000019
0000020
0000A10
It is truncating to
19
19
20
0000A10
I could view the data in editplus without trucation of zero's.If the data contain any alpabetic character then is writing properly.
Any one please let me know,Why this is happening and any technique to preserve this zero's while writing to a .csv file.
Thanks in advance.
tom
Posted: Tue Apr 29, 2008 3:20 pm
by ray.wurlod
Are you saying that the zeroes are truncated when you open the CSV file with Excel, or that DataStage is truncating them?
What do you see when you open the CSV file with, say, vi?
Try using string rather than a numeric data type.
Posted: Tue Apr 29, 2008 3:38 pm
by tom
Thanks for your response ray.
Zeroes are getting truncated when opening the output CSV file with Excel.I
could view the data with zero's in vi editor.Datasage is
not truncating.
I am writing this column as string datatype only.
Could you please let me know why zero's is truncating when opening
with excel?
Thanks
tom
Posted: Tue Apr 29, 2008 3:56 pm
by ds_developer
This is an Excel issue that has nothing to do with DataStage. What happens is when Excel opens a .csv file it tries to guess at how you want to see the data. This is an automatic data conversion that I have not discovered how to disable.
The best way I've found to deal with it is to create a custom import of the data. This won't be every step, but here is the idea:
a. open Excel to a blank spreadsheet
b. go to Data|Import External Data|Import Data and browse to your .csv
c. this will start an Import wizard
d. on the format step, pick the problematic column and choose Text
e. finish the wizard
You can then save this document as a template which if you open later will prompt you to see if you want to update the data from the .csv. So each time the .csv has new data, you would just open this template and it will do the custom import steps on the .csv and display the sheet. You can then save this as a .xls.
Hope this helps.
John
Posted: Tue Apr 29, 2008 4:15 pm
by ray.wurlod
Or you can Format the Cells in question in Excel.
Posted: Wed Apr 30, 2008 7:54 am
by ds_developer
Formatting the column after opening the .csv does not give me the leading zeros in Excel 2003 - maybe it does in other versions. The only way I've found to do it is by setting up a custom import of the file using Import External Data.
John
Posted: Thu May 01, 2008 3:41 am
by PhilHibbs
ray.wurlod wrote:Or you can Format the Cells in question in Excel.
You can't format it to restore exactly the same number of leading zeros. If your data is inconsistent, for example...
Code: Select all
0001
0002
0003
0000001
0000002
0000003
then Excel just gets rid of all the zeros and there's no way to get them back. Correct me if I'm wrong of course, does it keep the zeros somewhere safe?
Oh, and another word of warning to anyone dealing with numeric values with leading zeros - don't use the DataStage comparison operator to compare two values! Use the COMPARE() function. DataStage will compare "001" and "000001" as being equal.
Posted: Thu May 01, 2008 3:52 am
by ArndW
Can you not explicit declare them as VarChar() columns, then they get quoted in the .csv file.
Posted: Thu May 01, 2008 5:56 am
by PhilHibbs
ArndW wrote:Can you not explicit declare them as VarChar() columns, then they get quoted in the .csv file.
Quotes around the values in the csv makes no difference to the way Excel handles them.
Posted: Thu May 01, 2008 6:46 am
by ArndW
I tried it and you are correct, then I played around with the CSV format and found that
does import correctly. Could you change your job to write out this field format?
Posted: Thu May 01, 2008 8:05 am
by PhilHibbs
ArndW wrote:I tried it and you are correct, then I played around with the CSV format and found that
does import correctly. Could you change your job to write out this field format?
That isn't a CSV file. It may work in Excel, but it isn't CSV and probably won't work in other places. Certainly it would be impossible to get SAP LSMW to accept files in that format.
Posted: Thu May 01, 2008 8:09 am
by ArndW
That's the first mention of SAP that I saw in this thread. I thought you wanted Excel to keep the formatting and that is what I played with. Can't help you with LSMW.
Posted: Thu May 01, 2008 8:16 am
by PhilHibbs
ArndW wrote:That's the first mention of SAP that I saw in this thread. I thought you wanted Excel to keep the formatting and that is what I played with. Can't help you with LSMW.
Well, that's my take on the situation - the OP said he is writing CSV and that Excel is not reading it correctly. I was just pointing out that the ;= format is not CSV so it might break something else that needs to read the same outputs. The OP is probably just loading it into Excel to check that it looks ok before passing it on to another system, CMIIW.