Data truncation error when importing to SQL from CSV/Excel

If you are importing large (greater than 8 rows) amounts of data into a SQL database via the SQL Server Import and Export Wizard you may run across this error:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column “ColumnName” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
(SQL Server Import and Export Wizard)

This is due to a wonderful setting within Excel in your registry that tells it to only check the first 8 rows of data within your spreadsheet to determine the column size for the remainder of all the data. The fix for this is to modify your registry to set it from 8 to 0. When it’s set to 0 it will check the entire spreadsheet. This may cause some performance issues during the initial data import if the file is extremely large. Here is the the registry key to search for (there may be more than one that needs to be set):