Import data from Excel to SQL server using DTS Wizard - 01-20-2005 , 10:07 AM
I am using DTS Wizard to Import data from Excel to a new
table in SQL server. After import, I notice that some data
values in Excel are replaced by NULL in SQL table without
any warning or error. That makes me very nervous. It means
that I cannot reply on the DTS successful import. It's
eating data without knowing!!! I took further closer look.
In "Select Source Tables and Views" window, after selected
the source sheet and clicked preview button, in "View
Data" window, those missing data are already eaten. Does
anyone has similar experience before? Any work around?
Thanks in advance,
Re: Import data from Excel to SQL server using DTS Wizard - 01-20-2005 , 01:55 PM
You may well be seeing this
Excel Inserts Null Values
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"Jenny Wang" <anonymous (AT) discussions (DOT) microsoft.com> wrote
Re: Import data from Excel to SQL server using DTS Wizard - 01-21-2005 , 05:39 AM
One way round this is to use the sql openrowset in a sql task eg -
INSERT INTO DBTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=NO;
IMEX=1;Database=<Excel.xls>', 'SELECT * FROM [Table])
I much prefer this option as it reduces some of hte DTS/ Excel bugs.