Scientific Notation Problem

Question

Post reply

Scientific Notation Problem

Robert J

Mr or Mrs. 500

Points: 505
More actions
November 24, 2005 at 4:55 am

#76455

I am importing spreadsheets form users on the network through DTS.
I have an ID column that may contain 10 digit numbers or letters and numbers.
I can get the entire number to come in without converting to Scientific Notation with one combination of settings but any alpha character results in a null field.
I am able to get the alpha values to come in by adding the imex=1 switch to the extended properties of the excel ole connection in disconnected edit mode.
However when i do this the numbers are converted to scientific notation which makes them useless as an ID.
How can i get a 10 digit number AND alpha characters to both come in properly?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Are the columns in the spreadsheet set as text?

Did you change the registry setting?

Have you tried to increase the row sampling?

See http://support.microsoft.com/kb/194124

Far away is close at hand in the images of elsewhere.
Anon.

Robert J Mr or Mrs. 500 Points: 505 More actions · Answer 2

The columns are set as text and the registry is correct -

I am able to import the text and numbers - I am not getting any nulls.

with these settings though anything over a six digit number is converted to sicentific notation.

can not seem to get the text and the full 10 digit number (as text) at the same time.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

I have the same type of problem linking an Excelsheet to Access. (Office 2003)

If there are numbers or text in a column and the format is text some rows are not correct linked. Even if the format in Excel is text the format in the linked table is number. I also tried DTS with the same result. This is strange. Is it a bug? I gave up. Instead I save the Excelsheet as a tab separated text file and then link the textfile that works.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

The problem is the Excel ISAM and it's estimating of column contents and uses the type of the majority values (and value evaluation) to decide what the column type is and most of the time gets it wrong and it is hard to convince it otherwise. Gosta got the right way of doing it by saving the spreadsheet as a text file (csv, tab delimited etc).

Far away is close at hand in the images of elsewhere.
Anon.

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 5

A down-and-dirty alternative is to manually set the first cell in that column as text, by adding an alpha character in the front of the contents and then trying again.

David Burrows SSC Guru Points: 65142 More actions · Answer 6

By default the Excel ISAM examines the first eight rows and will use the majority type to determine the data type, so if the first line is alpha and the rest numeric then the datatype will be float

To limit the determination the max rows to use will have to be set to 1 for this to work

Far away is close at hand in the images of elsewhere.
Anon.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 7

Interesting. I tried that solution but it didn't work for mee.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

Prefix the contents of each value in the ID column with a single quote to make it a text field, the ISAM will then make the column nvarchar in the output table in DTS

This does mean that whatever produces the spreadsheet will have to do this or be done manually though

Far away is close at hand in the images of elsewhere.
Anon.

Bill Scott SSCrazy Points: 2338 More actions · Answer 9

try setting the column type in excel. Right click the column header, choose format cells, then choose text from the list on the Number tab. this works best before the data is placed in the sheet. i use it all the time to preserve leading zeros.