Data conversion failed error when running import/export wizard SQL Server

Error: Data conversion failed. The data conversion for column returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.

It is no fun getting ridiculous errors when you are trying to quickly import a csv file into a database (flat file import). Unfortunately though that is what can happen.

How to troubleshoot

Firstly check the error message so that you know which field you are having the problem with.

For truncation errors first check the porperties of your source data types. You need to make sure there are not smaller than the destination data types.

In a recent case I had the target column was varchar(max) however the source property was set to a string of 50 character length.

One thing you can do is try to pump the data into a staging table first, then use pure SQL to add it an existing table.

import export error

So to sum up.

  • Identify the problem field
  • Check the format of the source file and either fix up the data types, or pump directly into a staging table
  • Dates can be messy so easier to import into a varchar column in a staging table

If you have any favorite tips or techniques, why not share them with us?

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.