posts - 220, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

Excel Import Ignores Data

I've been trying to import data from an Excel spreadsheet using DTS.  I've been having problems with sparsely populated columns.  Basically if the first eight rows are blank it ignores any other values in the following rows.  This is because the Excel driver is only examing the first eight rows to determine data types and such.  There's a registry entry you can change to force the driver to search the entire spreadsheet before making any data type determinations.  I've got mine set to “0” which scans the entire spreadsheet.  I haven't had any performance problems and it's fixed a number of annoying problems.

Print | posted on Friday, February 04, 2005 9:55 AM | Filed Under [ SQL Server Stuff ]

Feedback

Gravatar

# re: Excel Import Ignores Data

When I hit this bug, I used search/replace to ensure there was a blank in unused columns, saved as CSV.

The import from there was easy.
2/24/2005 8:34 AM | SamC
Gravatar

# re: Excel Import Ignores Data

If you are just doing the import as a one off then exporting Excel workbooks to csv files and then importing those into SQL gets around the issue.
5/19/2006 8:22 AM | Martin
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET