Exponent

dslisa · Post by **dslisa** » Tue Nov 11, 2008 5:40 pm

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.

chulett · Post by **chulett** » Tue Nov 11, 2008 6:06 pm

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?

dslisa · Post by **dslisa** » Tue Nov 11, 2008 8:24 pm

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.

chulett · Post by **chulett** » Tue Nov 11, 2008 10:53 pm

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.

dslisa · Post by **dslisa** » Thu Nov 13, 2008 5:42 am

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

chulett · Post by **chulett** » Thu Nov 13, 2008 8:55 am

Field : CHAR(9)

dslisa · Post by **dslisa** » Mon Nov 24, 2008 10:58 am

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

chulett · Post by **chulett** » Mon Nov 24, 2008 11:13 am

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?

dslisa · Post by **dslisa** » Mon Nov 24, 2008 11:27 am

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?

chulett · Post by **chulett** » Mon Nov 24, 2008 11:41 am

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?

dslisa · Post by **dslisa** » Wed Nov 26, 2008 12:45 pm

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.

chulett · Post by **chulett** » Wed Nov 26, 2008 3:05 pm

Nice.