Truncating zero's in .csv(excel file)
Moderators: chulett, rschirm, roy
Truncating zero's in .csv(excel file)
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
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
Devlopers corner
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
What do you see when you open the CSV file with, say, vi?
Try using string rather than a numeric data type.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Devlopers corner
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
You can't format it to restore exactly the same number of leading zeros. If your data is inconsistent, for example...ray.wurlod wrote:Or you can Format the Cells in question in Excel.
Code: Select all
0001
0002
0003
0000001
0000002
0000003
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.
Last edited by PhilHibbs on Thu May 01, 2008 5:58 am, edited 1 time in total.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
Can you not explicit declare them as VarChar() columns, then they get quoted in the .csv file.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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?
Code: Select all
="00000";="0000";="000"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
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.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?Code: Select all
="00000";="0000";="000"
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
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.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.
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant