Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

Question

I m trying to import a flat file into an oledb target sql server database   here s the field that s giving me trouble     here are the properties of that flat file connection  specifically the field     here s the error message       Source - 18942979103 txt  424   Error  Data conversion failed  The   data conversion for column  recipient-name  returned status value 4   and status text  Text was truncated or one or more characters had no   match in the target code page      What am I doing wrong

User · Answer

Not really a technical solution  but SQL Server 2017 flat file import is totally revamped  and imported my large-ish file with 5 clicks  handled encoding   field length issues without any input from me

User · Answer

Here is what fixed the problem for me  I did not have to convert to Excel  Just modified the DataType when choosing the data source to  text stream   Figure 1   You can also check the  Edit Mappings  dialog to verify the change to the size  Figure 2    Figure 1    Figure 2

User · Answer

While an approach proposed above   chookoos  here in this q amp a convert to Excel workbook  and import resolves those kinds of issues  this solution this solution in another q amp a is excellent because you can stay with your csv or tsv or txt file  and perfom the necessary fine tuning without creating a Microsoft product related solution

User · Answer

I was receiving the same error  You need to go increase the column length while importing the data for particular column  Choose a data source  gt  gt  Advanced  gt  gt  increase the column from default 50 to 200 or more  It worked for me

User · Answer

None of the above worked for me   I SOLVED my problem by saving my source data  save as  Excel file as a single xls Worksheet Excel 5 0 95 and imported without column headings   Also  I created the table in advance and mapped manually instead of letting SQL create the table

User · Answer

I ve resolved it by checking the  UNICODE checkbox  Click on below Image link

User · Answer

SQl Management Studio data import looks at the first few rows to determine source data specs    shift your records around so that the longest text is at top

User · Answer

SQL Server may be able to suggest the right data type for you  even when it does not choose the right type by default  - clicking the  Suggest Types  button  shown in your screenshot above  allows you to have SQL Server scan the source and suggest a data type for the field that s throwing an error  In my case  choosing to scan 20000 rows to generate the suggestions  and using the resulting suggested data type  fixed the issue

User · Answer

I had similar problem against 2 different databases  DB2 and SQL   finally I solved it by using CAST in the source query from DB2  I also take advantage of using a query by adapting the source column to varchar and avoiding the useless blank spaces     CAST RTRIM LTRIM COLUMN NAME   AS VARCHAR 60  CCSID UNICODE     FOR SBCS DATA   COLUMN NAME   The important issue here is the CCSID conversion

User · Answer

I solved this problem by ORDERING my source data  xls  csv  whatever  such that the longest text values on at the top of the file   Excel is great   use the LEN   function on your challenging column   Order by that length value with the longest value on top of your dataset   Save   Try the import again

User · Answer

It usually because in connection manager it may be still of 50 char   hence I have resolved the problem by going to Connection Manager--  Advanced and then change to 100 or may be 1000 if its big enough

User · Answer

I know this is an old question  The way I solved it - after failing by increasing the length or even changing to data type text - was creating an XLSX file and importing  It accurately detected the data type instead of setting all columns as varchar 50   Turns out nvarchar 255  for that column would have done it too

[sql] Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to ssis