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.
Next we can preview the data and set the delimiter if it is wrong.
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.
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.
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.