DSXchange

Hi,
I have a file which has some data as 1234E567. When I output it as a .csv file it is displayed as 1234E+112. Somehow the character "E" is read as a formula in excel. What I need is 1234E567. I tried putting the string in double quotes but again the data is displayed as 1234E567". If I put the string in single quote it is displayed as '1234E567. How can this be resolved?

Thanks.

What resolution are you after? Do you want the exponent or is this just a string with an 'E' in it? And the problem is strictly an Excel problem when you open the file in that tool?

Hi Craig,
I donot want the exponent format data. I want the data as is..1234E567. The data is displayed correctly in datastage and I realize this is an excel problem. But if there is any solution please share.
Thanks.

Seems to be a known Excel "issue" when you do an automatic import by, say, double-clicking on a .csv file. Doesn't seem to be a good, automated solution other there, found this as one possibility:

http://www.pcreview.co.uk/forums/thread-1049984.php

Otherwise, it seems like you'd need to create a .txt file rather than a .csv to force the process through the Text Import Wizard. Teach your customers to change this column from 'General' to 'Text' during the import and it will import correctly.

If you want to explore, perhaps try what I did - Google for a phrase like "excel import csv with exponent". Not really a DataStage problem but if you can find a solution that involves how the file is create it, you should be able to handle it via DataStage. Otherwise, you are in Microsoft's hands.

As per your above post how can we append ACSII 9 at the end of the column?

Field : CHAR(9)

Appending the ASCII 9 doesnot work. Has anyone implemented this before? I mean not the appending part but the converting data as is part.

Huh, it actually worked for me when I tested it. As long as you didn't mind the 'extra' character in the field, that is. [shrug]

The conversion mentioned was manual, as noted there doesn't seem to be any automated solution out there. Or did you want to convert it from scientific notation to... not scientific notation?

I did the following..and no the extra character is fine as long as the data comes without exponent.

Fieldname : CHAR(9).

Did I miss something?

No, that's literally what I did as well and it allows the field to import as text with the 'E' in the string intact - isn't that what you mean by "without exponent"? What version of Excel do you have?

Hi all,

Found a solution for this.

"=":DQuote(FieldName)

This will import the Text field as is into the .csv file thus eliminating how letter 'E' is changed into exponent.
I will mark this as resolved.

Nice.