tFileInputDelimited - string type, numeric value

[Anonymous]

Hi there,

I'm using tFileInputDelimited to read csv file :

CSV file data

schema

As we can see in above screenshots, CustomerNumber field contains alphanumeric values(some might be numeric and some might be string). I want the values as it is so I used String type.

But why its giving '0000001' instead of '1' ?

Output

Please help. Thanks !

[Anonymous · Accepted solution]

You are looking at the CSV file in Excel and not in it's untainted form. Can you look at it using something like notepad? I suspect you will see that Excel is altering the value to remove the zeros

[cterenzi]

What I wouldn't give for a way to turn off that "feature"...

[Anonymous]

You are right @rhall. Checked with notepad and 0's are prepended there. Thanks for pointing me out. Such a silly thing !!

[Anonymous]

No problem. It is also wise to avoid opening CSV files in Excel. Excel can completely change the CSV format if you save them within Excel. This can cause big issues with debugging

[cterenzi]

Some other issues with opening csv files directly with Excel:

• loss of precision of large integers or extended decimal values
• changes to date formats
• loss of quote enclosures
• loss of special characters (depends on the default character set used by Excel)

If you have no alternative means to examine a csv file, at the very least you should "Import" the data.  In Excel 2010, this is done from the Data tab -> Get External Data -> From Text

Import everything as text to see what it (almost) looks like in the file.  You still won't see quote enclosures, but you'll at least avoid issues like 0000001 being represented as 1 and get to see the date formats in use by the file.