Excel import converts serial no to float

[Custardptt]

Hi All

I'm having a little trouble with SQL Server (2008R2) converting 10 digit serial numbers into Float (with 6 digit precision) whilst importing them from Excel.

I'm using the OPENROWSET method, and doing an 'INSERT INTO' a staging table. If I just do a SELECT and get the results printed to the window everythings fine. If I do an INSERT INTO, then look at the staging table, 1234567890 turns into 1.23456e9. Tried using SELECT str(SerialNo,10) on the table but this gives me 1234560000. The column on the table is nvarchar(100) The column in excel is 'Text' ('Text' or 'General', seems to make no difference)

I'm sure someone has had the problem before so apologies for timewasting but google brings so much stuff back on this I can't find the answer.

Import script is here

declare @InFileName nvarchar(255); -- normally this would be delivered by a loop
set @InFileName = 'File1-20130409-01.xls'
declare @InFileDir nvarchar(255);
set @InFileDir = 'C:\CompareTest\Load'
declare @InExcelString nvarchar(1000);

/**Get Data **/
set @InExcelString = 'INSERT INTO [KGUX_Store].[dbo].[Import_SetX01_Raw] SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @InFileDir + '\' + @InFileName + ''' , ''SELECT * FROM [Sheet1$]'')'
EXEC(@inExcelString)

Using:
set @InExcelString = 'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @InFileDir + '\' + @InFileName + ''' , ''SELECT * FROM [Sheet1$]'')'
EXEC(@inExcelString)

This prints the results to screen and they look fine, so either the 'INSERT INTO' casues the retrival to operate differently or the problem lies with SQL server itself but I had thought that SQL Server would not implicitly convert datatypes.

Do any of you have any thoughts?

Thanks in advance

Pete

 

[Custardptt]

OK,

Think I found my answer here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54271

Seems SQL Server samples 8 rows of the data prior to doing the import and decides what sort of data it is based on it's sample. (I also discovered that it's not the first rows it samples.)

Unless someone has some better information, it seems that the only way to get it to behave is to add a text portion (prefix or suffix) to the serial no, then remove it post import. This will ensure that the field is recognised as text. It's a lot more messing about than I wanted though.

Oh Excel, let me count the ways I loathe you....

Cheers All

 

[gmmastros]

try this:

set	@InExcelString = 'INSERT INTO [KGUX_Store].[dbo].[Import_SetX01_Raw] SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;[!]IMEX=1;[/!]Database=' + @InFileDir + '\' + @InFileName + ''' , ''SELECT * FROM [Sheet1$]'')'
EXEC(@inExcelString)

I believe "IMEX=1" causes the columns to be treated as text.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom