Scientific Notation Problem 1

[HowardHammerman]

I have a program that exports data to *.csv format.
One of the important ID fields is a six-character text field. A few of the IDs look like this:
1000E0
1000E1
1000E2
1000E4
etc.
When I bring the *.csv into Excel (either 2003 or the latest version) the values get converted to sientific notation:
1.00E+03
1.00E+04
1.00E+05
etc.
Selecting the column and converting it to text does not help.
I get
100000
10000000
etc. (I might have the number of zeros wrong).

The client MUST have the data as a six-character text field with the original values. And the file format must be *.xls
or *.xlsx.

Any ideas?

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[SkipVought]

Hi,

This is an example of Billl Gates trying to be helpful.

Do NOT OPEN, repeat, DO NOT OPEN the .csv file as a workbook!

Rather IMPORT the .csv file into a workbook. I assume you have 2007. on a new sheet, Data > Get External Data > From Text...

This enables you to use the COMMA DELIMITER to parse the input AND explicitly specify that the data in the offending column is imported as TEXT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

FYI,

This is one example, of several, where Excel CONVERTS data, based on the context of the entered value.

For instance, if you enter 1/2, Excel assumes that you want to enter a DATE. (It't NOT a fraction, because Excel does not allow fractions as a number, to be entered that way.) Here's the conversion: it assumes m/d/current year as a structure and converts the data accordingly into a NUMBER, formatted as a DATE.

In your case, it sees text that has the nnnEnn structure of a number displayed with scientific notation. Hence the CONVERSION to a NUMBER and the FORMAT.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HowardHammerman]

Thank you Skip.

Yes, a number followed by E and then an integer indicates scientific notation.


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[lionelhill]

Before I read a previous SkipVought reply to a similar question, I used to rename .csv files to .txt to achieve a simlar end. Excel employs its brain-cells only on csv and not on txt files.

 

[SkipVought]

lionelhill, do you REALLY want to leave to chance the import and possible CONVERSION of your data? I would choose an explicit approch EVERY time, that I can control, rather than allowing some unknown process to make decisions about my data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Skip, before I read a previous answer of yours, that's what I used to do! Now I do it your way... (thanks!)

 

[SkipVought]

Sorry, I misread your reply.

Too many spinning plates, bouncing balls & hoops.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}