Tip

Troubleshooting an Excel data import malfunction

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

Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.