"Unstructured Data" stage reads excel data wrongly

[pran.praveen]

I am reading excel file through "Unstructured data" stage available in DS 9.1 but it reads data wrongly, instead of varchar it read it as exponent, even though I have defined all columns as VARCHAR in DataStage. (I have tried with char, nchar but result is same).

Example : In excel I have a column called C_ID and it has Alphanumeric data, for data value 511010001, 513018202 it reads it as "5.11010001E8", "5.13018202E8".

Please help me, If any one knows how to overcome this problem.

[dsetlteam]

I tried using char,varchar,nvarchar etc. at my end but it still gives the data in the way that you are seeing...I searched for any environment variable that might help resolve the issue but there doesn't seem to be any. Can you take the issue to your administrator or raise a PMR with IBM?

Please keep us updated with the resolution

Cheers!

[stuartjvnorton]

What format is it set to in excel? General?

[yannish]

We faced the same issue and the resolution was in Excel. As the data had been in some point of time in numeric format in the column Excel refused to make it text. The workaround was to create a new column, make it as text, then copy data from old column to Notepad and finally copy the data back to new text column. Now the data came in to DataStage without conversion to exponential format.

[kethya11]

I am faced same issue.In excel sheet change the option then number showed fully then save the excel sheet.I remember right of the column double click then save.Once saved the file dont open again before loading the data