formatting of imported data

[michdaw]

Hi,

I am wondering how to format my data so when it is imported into excel it is formatted properly. My data is this:

N,E,MICHELLE DAW ,5061111111,CCCCC,222222222 ,NB,NA,2122200220001,IO1000 ,340000001,09-19-2002,09-17-2002,5222222001

The number field 2122200220001 doesn't import correctly, i have to select the column and format to a number no decimals.

Please Help! :-{}

 

[mscallisto]

Does it import as 2.12E+12 instead of 2122200220001?

If it does it's because the fiels is greater than 11 digits in length and is being converted to scientific notation, also known as E-notation.

2.12E+12 means the decimal point is actually 12 places to the right of where it currently shows. This is an engineering/scientific method of displaying numbers that are very large or very small.

Yes I believe you must select the column and format it the way you are doing unless someone else knows how to force e-notation from not happening when importing.

 

[schat]

michdaw
i think mscallisto's right. one option is to import as text. but then changing it back to number format may be trickier - i think if you import as text, just changing the cell format from the format menu won't change the underlying format ie. it will still be read as text.

maybe this is no problem, but if it is, in a blank cell, put a '1', select this cell, copy, select the data you have imported and go to paste special (edit menu) and select 'multiply'. hit ok and it should change the format of all the data to numbers.
hth
schat

 

[mscallisto]

Actually Shat It should be no problem for Michdaw to continue doing what's being done currently.

The data will show up as 2.12E+12 but will convert to 2122200220001 when the format is changed to numeric no decimals.

The question remains, how can this be done on import, eliminating the need to change the format later.

It may not be possible, two steps may be needed.

 

[WindowWizard]

Be very careful when using scientific notation and large numbers. For example, 16 digit account numbers (such as those on credit cards) will be converted to E+ notation but only 15 significant digits are converted. Thus, the final digit of the account number will be lost and always displayed as zero when converted to text.

Just another reference point for those considering other inputs during import/export operations.

WW

 

[mscallisto]

Very good point WindowWizard and very true, I should have mentioned it!

For this particular case it should not apply but still a very good catch.

 

[michdaw]

Thank you everyone....

I tried to "change" the import file so there are double quotes around the effected data ie:
N,E,MICHELLE DAW ,5061111111,CCCCC,222222222 ,NB,NA,"2122200220001",IO1000 ,340000001,09-19-2002,09-17-2002,5222222001

Now when it imports it has quotes around that data.

I am meeting with my "user" today to determine what they want to do.... (format it or have it in quotes).

THANKS AGAIN for confirming everything for me! :-D

 

[Gavona]

If you change the normal style of the worksheet after the import to include a number format (not 'general') that might help to simply and quickly sort your issue.