Truncation Issue with Importing Text File to SQL Server (“Text was truncated or…”)Posted: August 17, 2017
You can use right click your database name >>Tasks>>Import data… to launch SQL Server Import and Export Wizard >>click on Next>> choose data source >>pick Flat File Source >> browse to the location of your text file>> there are many settings you can play with and I am describing them here. You can go with default for most settings and give it a try.
Sometimes you may run into an annoying truncation error:
“Data conversion failed. The data conversion for column “xxxxxx” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page “
One or more columns in your text file have larger column sixe than the target table. You can follow the error message to increase your target table column size. The default for all columns are 50.
If you load your text file into a staging table, you can quickly change the default size to a larger one to finish up the loading process.
…. pick Flat File Source >> Choose a data source : browse to the location of your text file >> click on Advanced. Choose the first column and then hold down the shift key to select all columns you want to change size >> Change the Outputcolumnwidth size from 50 to a larger number, for example 100 or 2000. All column sizes will be changed to the new size.
Continue with the process and the truncation error should be fixed.
Another way to import text file is a method that I prefer to use.
Use bcp to import text file data with a format file into staging table. First to create a target table with proper column size and run bcp to create a format file and run bcp command with the format file to load the text file very quickly. The target table columns should have big enough size to receive the text file data to avoid truncation error