Month: February 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 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
A useful resource for this sort of stuff is, see the pages on Excel and text file connection strings/settings.
(Apologies to Dunc for publishing the fix as my own (ah, if only))