Tuesday, February 10, 2009

SQL won't import from Excel? Truncation error? It's rubbish, that's what it is...

It sounds like the JET/Access driver is looking at a sample of the data in your spreadsheet and making an assumption on the data types from that. Because there are likely no rows with data > 256 chars (say) in the first x rows, it’s reading it as char(256). –i.e. It’s inferring data type and length from a sample of your source data, it’s not looking at the destination at this point.
In order to work around this then try the TypeGuessRows registry key as detailed in http://support.microsoft.com/kb/189897 - which allows you to tweak the sample set the driver uses in trying to determine the data type/length. Setting it to zero is a broad brush, but it's an instant fix.
If that doesn’t work, then export from excel to CSV and use a SCHEMA.ini file to tell the Text driver explicitly what the data types/lengths are (see http://msdn.microsoft.com/en-us/library/ms709353.aspx).
A useful resource for this sort of stuff is http://www.connectionstrings.com, see the pages on Excel and text file connection strings/settings.
(Apologies to Dunc for publishing the fix as my own (ah, if only))