Seven Stars

tFileInputDelimited - string type, numeric value

Hi there,

 

I'm using tFileInputDelimited to read csv file :

 

CSV file dataCSV file data

 

schemaschema

 

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' ?

 

OutputOutput

 

Please help. Thanks !

  • Data Integration
Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

Re: tFileInputDelimited - string type, numeric value

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

Rilhia Solutions
5 REPLIES
Eleven Stars

Re: tFileInputDelimited - string type, numeric value

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

Rilhia Solutions
Ten Stars

Re: tFileInputDelimited - string type, numeric value

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

Re: tFileInputDelimited - string type, numeric value

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

Re: tFileInputDelimited - string type, numeric value

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

Rilhia Solutions
Ten Stars

Re: tFileInputDelimited - string type, numeric value

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.