I'm running into a problem using DTS to import Excel files. It seems that
DTS only looks at the first X number of rows to determine the datatype for
each column. The problem that I have is that many of my columns contain
NULLs in the first hundred rows and then some data that I wish to import.

Is there a way to have DTS examine the entire column to determine the
datatype?

Thanks in advance,
Ken

The short answer is no. The Excel driver will only look at the first 8 rows
to determine the data type in the column.

You could create a staging table with all varchar columns and import the
Excel worksheet to this table and then from this table to the table you
want to actually move the table to. Or you could go from Excel to a text
file and then into SQL Server.

Rand
This posting is provided "as is" with no warranties and confers no rights.

Drat! That's what I was afraid of. I thought I had recalled a post some time
ago about a way to accomplish this, but I guess it was just wishful
thinking.

Thanks for the reply,
Ken


"Rand Boyd [MSFT]" <rboyd (AT) onlinemicrosoft (DOT) com> wrote

Ken,

Let me clarify the previous response... BY DEFAULT, the Excel ISAM driver
scans 8 rows to guess at data types. According to an Appendix in the
Microsoft Jet Database Engine Programmer's Guide, you can set this value to
0 (zero) to force the engine to scan all rows in the worksheet...however its
"guess" will still be the "majority" data type, so NULLs, or mixed text and
numbers, could still cause a problem. You may also want to review the
little-known IMEX=1 option, to force the import of mixed types as text. This
can be added to an OLE DB connection string in the "Extended Properties"
argument.

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

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

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

"Ken" <kcoakley (AT) macomberreports (DOT) com> wrote

Douglas,

Thank you for the clarification. I believe the fix you mention from the Jet
Database Engine Programmer's Guide is what I was speaking of in my prior
post. Fortunately for me, my Excel columns will always contain a single data
type as well as NULLs, so this fix would work just fine.

I don't suppose you have a link to that article.

Thanks again,
Ken



"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote in
message news:uK8aoWeQEHA.3524 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Ken,

The Jet prog. guide used to be included in its entirety in the MSDN Library,
then it was dropped a couple years ago already. I quoted it mostly because
other sources that talk about the Registry value in question (TypeGuessRows)
rarely mention the "0 = all rows" option. Anything of value about the Excel
driver in that book should also be found somewhere in my list of Excel/ADO
Knowledge Base articles, below. Happy importing.

USING ADO AND ADO.NET WITH EXCEL: Resources and Known Issues
June 2003

General
-------
Q326548 HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default...;EN-US;Q326548
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default...;EN-US;Q257819
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default...;EN-US;Q303814
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
http://support.microsoft.com/default...;EN-US;Q278973
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default...;EN-US;Q195951

Transferring Data into Excel
----------------------------
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/default...;EN-US;Q247412
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/default...;EN-US;Q295646
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
http://support.microsoft.com/default...;EN-US;Q246335
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default...;EN-US;Q319951
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q306125

Known Issues
------------
Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/default...;EN-US;Q319998
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
http://support.microsoft.com/default...;EN-US;Q316809
Q314763 FIX: ADO Inserts Data into Wrong Columns in Excel
http://support.microsoft.com/default...;EN-US;Q314763
Q316475 PRB: "Operation Must Use an Updateable Query" Error Message
http://support.microsoft.com/default...;EN-US;Q316475
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
http://support.microsoft.com/default...;EN-US;Q300948
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
http://support.microsoft.com/default...;EN-US;Q294410
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
http://support.microsoft.com/default...;EN-US;Q293828
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
http://support.microsoft.com/default...;EN-US;Q288343
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
http://support.microsoft.com/default...;EN-US;Q246167
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
http://support.microsoft.com/default...;EN-US;Q211378
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
http://support.microsoft.com/default...;EN-US;Q194124
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
http://support.microsoft.com/default...;EN-US;Q189897

Known Issues - .Net-specific
----------------------------
Q316831 PRB: Cannot Configure Data Connection to Excel Files in VS .NET
http://support.microsoft.com/default...;EN-US;Q316831
Q316756 PRB: Error w/ ADO.NET OLEDbDataAdapter to Modify Excel Workbook
http://support.microsoft.com/default...;EN-US;Q316756

VB.Net
------
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .NET
http://support.microsoft.com/default...;EN-US;Q318373
Q316934 HOW TO: Use ADO.NET to Retrieve and Modify Records in Excel
http://support.microsoft.com/default...;EN-US;Q316934
Q306022 HOW TO: Transfer Data to Excel Workbook Using Visual Basic .NET
http://support.microsoft.com/default...;EN-US;Q306022
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
http://support.microsoft.com/default...;EN-US;Q302094

Web/ASP
-------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q319180
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
http://support.microsoft.com/default...;EN-US;Q276488
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default...;EN-US;Q257757
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
http://support.microsoft.com/default...;EN-US;Q199841
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default...;EN-US;Q195951

Web/ASP.Net - .Net-specific
---------------------------
Q317719 HOW TO: Export Data in DataGrid on an ASP. NET WebForm to Excel
http://support.microsoft.com/default...;EN-US;Q317719
Q311731 HOW TO: Query and Display Excel Data by Using ADO.NET, VB .NET
http://support.microsoft.com/default...;EN-US;Q311731
Q308247 HOW TO: Use ASP.NET to Query and Display Database Data in Excel
http://support.microsoft.com/default...;EN-US;Q308247
Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
http://support.microsoft.com/default...;EN-US;Q307603

SQL Server/DTS
--------------
Q321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/default...;EN-US;Q321686
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default...;EN-US;Q319951
Q306397 HOWTO: Use Excel w/ SQL Linked Servers & Distributed Queries
http://support.microsoft.com/default...;EN-US;Q306397
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q306125
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
http://support.microsoft.com/default...;EN-US;Q281517
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default...;EN-US;Q257757
Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
http://support.microsoft.com/default...;EN-US;Q236605
Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
http://support.microsoft.com/default...EN-US;Q231880t
Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns
http://support.microsoft.com/default...;EN-US;Q207446

XML
---
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
http://support.microsoft.com/default...;EN-US;Q285891
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
http://support.microsoft.com/default...;EN-US;Q278976

XML - .Net-specific
-------------------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default...;EN-US;Q319180
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
http://support.microsoft.com/default...;EN-US;Q307021

----------------------------------------------------------------------------
------------
This list is maintained and updated from time to time by DOUGLASL.
The main contributors of content are DOUGLASL (12 articles) and LORITU (10).
----------------------------------------------------------------------------
------------
Disclaimer: This document is provided "AS IS" with no warranties, and
confers no rights.

Good to see you again Douglas. Great resource list you've posted here. I
did not catch the earlier parts of this thread as I've been elsewhere but
did you also add

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

Excel treats First Row of Data as Headers
(http://www.sqldts.com/default.aspx?255)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote in
message news:e2BziMQREHA.3628 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Douglas,

Thanks so much for the links. I appreciate your help.

Ken


"Douglas Laudenschlager [MS]" <douglasl (AT) online (DOT) microsoft.com> wrote in
message news:e2BziMQREHA.3628 (AT) TK2MSFTNGP12 (DOT) phx.gbl...