I have been importing data from Excel to SQL Server using the SQL Server Import Wizard. Usually there is no problem but I have found a bug.
1. To reproduce: Try to import a column containing more than 255 characters text, optimistically set the destination datatype to nvarchar(max) and the failure options to “ignore” on error and “ignore” on truncation.
2. I expect the Wizard to run successfully; nvarchar(max) has a 2GB limit and if I exceed this (I don’t) then it should ignore the error and truncate anyway
3. Actually the Wizard fails with the error ”Text was truncated or one or more characters had no match in the target code page.”
This is a known bug to do with the way the wizard samples the source at the beginning. There are two workarounds,
- ensure that there is a row at the top of the record set with the maximum width OR
- You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. (from social.msdn)
I used A.
Things that didn’t work,
- defining the table with the right field lengths first and assuming the wizard is not buggy
- exporting to CSV and using flat file inport
- anything to do with the code page, this is red herring.
