MS SQL Server DTS - Missing column values importing from Excel

Author

Missing column values importing from Excel

John Ballesteros

2005-04-23, 1:23 pm

I am trying to import some rows from a Excel sheet to a SQL Server table, but
when the first 200 rows on the sheet does not have values for a column, the
dts asumes that the whole column does not have values and import a NULL value
into the table, even if the column has values on the 250th row. If the Excel
sheet has a value on that column on the first 200 row, the DTS imports the
data right.

What can I do to make my DTS work right no matter if there is a value on the
first 200 rows or not?

Thanks for your help

Allan Mitchell

2005-04-25, 8:24 pm

Have a look at this article and see if it helps

Excel Inserts Null Values
(http://www.sqldts.com/default.aspx?254)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com

"John Ballesteros" <John Ballesteros@discussi
ons.microsoft.com> wrote in message
news:29E2FC42-7079-4134-AA72- 5A52AA9A9A87@microso
ft.com...
>I am trying to import some rows from a Excel sheet to a SQL Server table, but
> when the first 200 rows on the sheet does not have values for a column, the
> dts asumes that the whole column does not have values and import a NULL value
> into the table, even if the column has values on the 250th row. If the Excel
> sheet has a value on that column on the first 200 row, the DTS imports the
> data right.
>
> What can I do to make my DTS work right no matter if there is a value on the
> first 200 rows or not?
>
> Thanks for your help

Douglas Laudenschlager [MS]

2005-04-26, 8:24 pm

John,

You could cause the driver to sample all available rows instead of a small
number by entering 0 (zero) as the value of
HKEY_LOCAL_MACHINE\S
OFTWARE\Microsoft\Je
t\4. 0\Engines\Excel\Type
GuessRows in
the Registry.

281517 PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer
Overflow
http://support.microsoft.com/?id=281517

Without experimenting, I'm not certain whether "Import Mode" would resolve
this particular problem, but that involves adding "IMEX=1" in the Extended
Properties string for the Jet provider.

194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/?id=194124

Hope this helps,

-Doug

--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

Sponsored Links