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)


You are here because you probably get strange errors while trying to upload a text or comma delimited file to SQL Server using their import and export utility.

Among the many errors  you are receiving, one of the most discernible ones is:

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)

After going back and editing your mappings by increasing the nvarchar size or even changing the field type to text, you are still receiving this error.

Well below is a depiction of the steps that you should take either during your initial upload of the text file or after the error is received.  Please note that to get to these different screens, use the navigation on the left hand side column.  The options are General, Columns, Advanced and Preview.

First, you’ll arrive at the place were you choose the flat file that you will be uploading to SQL server.  If the column names are in the first row, be sure to check the check box for that.

Choosing Flat File Source in SQL SERVER import wizard

Choosing Flat File Source in SQL SERVER import wizard

Next we can preview the data and set the delimiter if it is wrong.

Set your column and row delimiters in SQL Server Import Utility

Set your column and row delimiters in SQL Server Import Utility

This next one is the important one, the one that will keep you from getting that error.  Click on “Advanced” and you will be taken to a page where you will be setting the column properties.  Here, you will go to the column that is causing problems based on your error result, or you can proactively change the column type before you finish out the import wizard.  In this example, I know that I my column Provider_Name needs to be greater than the default nvarchar(50) value set by SQL server.   So I changed mine to 255.

Advanced Section - Flat File import - SQL Server

Advanced Section - Flat File import - SQL Server

Lastly, you want to preview your data.  Although this step is optional, it is still good to view it in case any characters within your data acted as a delimiter wrongfully, thus throwing off your rows and columns.

Preview your Flat File data as it will go into SQL Server

Preview your Flat File data as it will go into SQL Server

Once you have completed that, finish out the wizard like you normally would.   Be sure that you drop the table that was created in anticipation of the previous export attempt.  Otherwise you will be facing another error.

Good luck.

18 Comments

Filed under Computer - Technical, SQL

18 responses to “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)

  1. Rid

    Heyy,

    thanks for this. I was getting really frustrated with the constant error no matter what I did.
    Just to add a note. Also check for “text qualifier” in the general section; ” * – / all are inclusive so make sure if you any of these in your columns then add that.
    Also, make sure that you go to the particular column in the advanced settings to change the “text qualified” to “false”. This worked for me and I could load all the data without a single error.

    Thanks again for the above post.

  2. Thank You – You are a legend – three hours of frustation fixed

  3. Thank you so much! Problem finally solved!

  4. Meg

    Thank you, thank you, thank you! This fixed the problem.

  5. ikeumunna

    Well… shame on Microsoft. Any field size limits should be driven by the source table.

  6. Rajdev

    Thanks a lot,This fixed the problem.

  7. michael

    how about for special characters? or chinese characters?

    • During the import process [where you choose your data source], you may want to change the Locale to reflect what type of Chinese you are needing as well as the code page. Also, if the field that you are importing contains paragraph style text, be sure to make that a memo field, as opposed to a varchar.

      If you simply have a few Chinese characters among mainly Latin text, then try simply checking the Unicode box at the [Choose your data source prompt]. Also, continue to be mindful of determining if you need the field to be Memo or varchar.

  8. Cengiz

    Thanks Jongen from Holland.

  9. I had the same problem. The reason why it was happening for me was because of the way the CSV file was produced. After the last field there was another comma and after it, there were a lot of blank spaces before the end of the line. The wizard was detecting these spaces as a separate column and it was trying to import it. The first thing i did was to try and delete it from “Advanced” but for some reason it was trying to import it as the previous column (i dont know why – possibly a bug). The way i solved it was to go to advanced, set the “Text qualified” to false and then the max length to 8000. After that in the mappings to the DB table I simply ignored the column and tada – that was it working. I spent two hours over fighting this.

  10. Pingback: From MySQL to SQL server for PHP devs « Alex Ivaylov – a web developer

  11. Erica

    Thank you dude!!

  12. Thanks a ton !! cant thank you enough …

  13. thanks for taking the time to write this up, really helped when i had the same issue

  14. Jack Kerwin

    This is still helpful after all these years, thank you!

Comment on this

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s