Import Tool fails when inserting mixed null columns from excel
I’ve just discovered yet another glitch with the ‘Import Data’ tool within the ‘Microsoft SQL Server Management Studio’. It took me a while to figure this one out as the error message was of a slightly unhelpful wild goose chase.
Error
If you have a column within excel that contains a mix of both data and null cells, the import tool fails even if the destination column is set to ‘Nullable’ within ‘Column Mappings’.
“Text was truncated or one or more characters had no match in the target code page.”
Solution
The only work around I have found is to separate the null columns and import them separately.
As alternative I think you can place a single quote (’) in each null cell, this forces the excel engine to register the cell as text rather then a null type and import it as an empty piece of data; I’ve used this technique before to force mixed numeric and text fields to be solely text, as using ‘format cell’ and selecting ‘text’ still imports the cell as a numeric value. I haven’t tested it for this scenario, but it sounds plausible that it will have the same effect with null values.
Versions
Microsoft SQL Server Management Studio 10.0.2531.0
SQLServer 2008
Excel 2007
