How to Resolve: Bulk load data conversion error (type mismatch or invalid character for the specified codepage)…

This post helps you how to resolve the BULK INSERT-related error in SQL Server: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row… , column … 

 

Reproducing the issue with an example

Consider an example where you have a text file which you want to import in SQL Server using BULK INSERT:

 

Then, let’s say you created the corresponding table in order to import the data:

CREATE TABLE SampleDB.dbo.TestTable
(
id int,
code varchar(50),
descr varchar(50)
);
GO

 

Next, you run the below T-SQL statement in order to import the data in the above table:

BULK INSERT SampleDB.dbo.TestTable
   FROM 'c:\pathToFile.txt'  
   WITH   
      (  
         FIELDTERMINATOR =',',  
         ROWTERMINATOR ='\n'  
      );
GO

 

Right after you run the above T-SQL script, you get the below error message:

Msg 4864, Level 16, State 1, Line 9
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (id).

 

The answer can be obvious, but also you might need some time to figure it out. The issue in the above example is that you are trying to import the column headers as data!

 

---

Learn what’s new in SQL Server 2022. Enroll to the course!

Check our online course titled “SQL Server 2022: What’s New – New and Enhanced Features”
and learn all about the new features and enhancements in SQL server 2022!
(special limited-time discount included in link).

Learn More

---

How to resolve the issue

The issue can be easily resolved by including the “FIRSTROW=2” option in your T-SQL code:

BULK INSERT SampleDB.dbo.TestTable
   FROM 'c:\pathToFile.txt'  
   WITH   
      (  FIRSTROW = 2,
         FIELDTERMINATOR =',',  
         ROWTERMINATOR ='\n'  
      );
GO

 

Featured Online Courses:

• SQL Server 2022: What’s New – New and Enhanced Features 
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

 

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• How to Resolve: Cannot Connect to WMI Provider (SQL Server Configuration Manager)
• The Database Engine system data directory in the registry is not valid
• An error occurred in a SQL Server Service Broker/Database Mirroring transport connection endpoint (9642) – How to Resolve
• Error converting data type varchar to numeric
• Error converting data type varchar to float
• There is not enough space on the disk. (mscorlib)
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
• ORDER BY items must appear in the select list if SELECT DISTINCT is specified
• There is no SQL Server Failover Cluster Available to Join
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• The SELECT permission was denied on the object ‘extended_properties’, database ‘mssqlsystemresource’, schema ‘sys’.
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
• Rule “Setup account privileges” failed – How to Resolve
• … all SQL Server troubleshooting articles

 

Featured Database Productivity Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Learn more

 

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

 

Rate this article: (5 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub