SQL Server's Data Transformation Services (DTS) can convert data to and from a variety of formats, either for use within SQL Server or to export SQL Server data to another program. One common form of data for import is an Excel spreadsheet, since Excel is in essence a sort of database and many people use Excel to create quick-and-dirty table-structured data when Access would be overkill. (Many people have also used Excel to store structured data simply because there are used to it, or because they don't have Access.)
One of the features of the Microsoft Excel ODBC driver is a function that guesses at the data type for a column by reading the first eight records (or rows) of the table in question. The reason it only reads the first eight rows and not the whole spreadsheet is to save time and processing power; for the most part, the first eight rows will usually give a good enough hint as to what data type each column will be. (If you don't want to rely on this mechanism, then be sure to specify the column types manually.)
Some spreadsheets do not have a consistent format, or are sparsely populated and this can be a problem for the Excel import filter. The biggest problem is if the first eight or more rows of the spreadsheet are blank. In this situation, the auto-detection algorithm used to determine the column types will fail.
One possible workaround is with a Registry fix used by the Excel ODBC driver which forces the driver to scan the entire spreadsheet to determine column types. Under HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet3.5EnginesExcel, create or edit the DWORD value TypeGuessRows and set it to 0 to force the Excel ODBC driver to scan the entire spreadsheet. (Setting it to any other value will cause the driver to scan that many rows to guess at the column type; 0 means scan everything.)
Any computer fast enough to run SQL Server shouldn't experience a performance problem when importing spreadsheets and it makes the detection work a lot more reliable.
Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!
This was first published in February 2005