Working with SQL Server is such rewarding and most of the times, I get a chance to revisit and explore more into some of these errors. Long time ago, I had written a blog post to read data from CSV/text file and insert into SQL Server Table using BULK INSERT command. You can read it here: SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
There have been many comments on that blog with error received by my readers. This blog post is to fix few errors mentioned over there.
Msg 4860, Level 16, State 1
This is one of the most common error reported by readers. Let’s have a look at the steps to reproduce the same:
CREATE TABLE library_books_loan
(
student_id VARCHAR(10) NOT NULL,
book_id VARCHAR(10) NOT NULL,
branch_id SMALLINT NOT NULL,
id_no VARCHAR(10) NOT NULL,
date_out DATE NULL DEFAULT NULL,
due_date DATE NULL DEFAULT NULL,
date_in DATE NULL DEFAULT NULL
)
Here is the sample text file I want to insert into this table:
student_id,book_id,branch_id,id_no,date_out,due_date,date_in<> 1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01<> 2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16<> 3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22<> 4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12<> 5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01<> 6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16<> 7,1861003730,3,9034,2014-04-17,2014-05-01,NULL<> 12,0911625607,2,9018,2014-04-19,2014-05-03,NULL<>
We can save the file as C:\Temp\Books_Library.txt. Here is the command which we will run to import the data.
BULK INSERT library_books_loan
FROM 'C:\Temp\Books_Library.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '<>',
FIRSTROW=2
The common mistake which is done by the user is, file gets saved locally (on their computer) and command is executed on remote instance of SQL Server. Here is the error which you would receive if you do this.
Msg 4860, Level 16, State 1, Line 12 Cannot bulk load. The file "C:\Temp\Books_Library.txt.txt" does not exist.
So please make sure that file exists on the machine where SQL Server is running and path is correct on server itself.
Msg 4864, Level 16, State 1
If file is saved correctly on the server and BULK INSERT is tried then we are likely to get the below error messages:
Msg 4864, Level 16, State 1, Line 12 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 7 (date_in). Msg 4864, Level 16, State 1, Line 12 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 7 (date_in).
If we read error message correctly, it is complaining about row 8 and row 9. Column is date_in. If we look back at data, we can see that NULL is provided as a value.
This means that SQL Server is treating that value as a string “NULL” and trying to insert that into a column which is defined as the date.
Here is the simple explanation by the demo.
CREATE TABLE SQLAuth (d DATE)
GO
INSERT INTO SQLAuth VALUES ('NULL')
GO
Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.
Solution
Modify the text file and don’t pass any value to the column. It would be treated as NULL automatically. Here is the modified version
student_id,book_id,branch_id,id_no,date_out,due_date,date_in<> 1,0399147020,1,9019,2013-11-22,2013-12-06,2013-12-01<> 2,0030059380,4,9007,2013-12-01,2013-12-15,2013-12-16<> 3,0671880756,5,9018,2013-12-08,2013-12-22,2013-12-22<> 4,0911625291,3,9013,2014-01-02,2014-01-16,2014-01-12<> 5,0688161995,5,9022,2014-02-10,2014-02-24,2014-03-01<> 6,0911625291,2,9011,2014-03-03,2014-03-17,2014-03-16<> 7,1861003730,3,9034,2014-04-17,2014-05-01,<> 12,0911625607,2,9018,2014-04-19,2014-05-03,<>
If we run the same command now, data should be inserted. As highlighted, we can see NULL values inserted on our destination table.
These are some common errors one can get using BULK INSERT command. In case you are facing other errors, please comment and I shall try to respond it in a future post for sure.
Reference: Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
I have tried this as :
–create table emp(empid int, empname varchar(10))
bulk insert emp
from ‘C:\Users\Desktop\rt.txt’
with
(
fieldterminator = ‘ ‘,
rowterminator = ”,
firstrow = 1
)
select * from emp
my text file consist data as (fieldterminator is tab)
empid empname
1 anuj,
2 kepin
But still i am getting same error.. Why.
Error is Msg 4864, Level 16, State 1, Line 6
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (empid).
Dave,
Works perfectly — as long as the server knows the file path!!
Thanks!!
Hi all!! Also please check source and destination column lengths. I got this error message due to data truncation. Increasing source column length resolved the problem.
Thanks!
Hi Pinal,
I am facing the same issue. But my concern is ….In my text files I have 125 columns and i want to import data into SQL Server table. In text files I have field values which some times exceeding the size/length defined in the destination table….I dont want to increase size/length in the destination table to accomodate those values instead i want to seperate all those values in seperate files/table.
As per Nishad Reply from prevoius message I dont want to increase size/length of the column in the destination table, instead i want to seperate all those values in seperate files/table.
Could please help on this
Could any one reply on this please ?
Hi Pinal,
I’m working on importing csv file to sql table and got error Msg 4864, Level 16, State 1 – Bulk load data conversion error for the column with money data type.
In sql my column “EstimatedValue” with format L lenght = 8 and scale = 4 (ex : 10000.0000)
In my csv file the value when i export from sql is 10000.
The error message as below
“Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 11 (EstimatedValue).
Msg 2627, Level 14, State 1, Line 1”
Please could you help on that?
Thank you .
Anh Mai
can I?
You write above:
> The common mistake which is done by the user is, file gets saved locally (on their computer) and command
> is executed on remote instance of SQL Server. Here is the error which you would receive if you do this.
> Msg 4860, Level 16, State 1, Line 12
> Cannot bulk load. The file “C:\Temp\Books_Library.txt.txt” does not exist.
> So please make sure that file exists on the machine where SQL Server is running
> and path is correct on server itself.
What we would really expect from you SQL Server gurus, is to help the product advance. Your above comment
does not do that at all.
What is happening here, is that SQL SERVER HAS A LIMITATION. Thats the net effect, and anything else is
what we call excuses, excuses.
SQL Server cannot read from my own C: drive, the file has to be placed in the server side.
Believe us, we have no idea where the server side is, we have our own file, and we want to load it, and thats that.
Even if we knew where the server is, it would be impossible for us to place the file there.
Whats needed here is, folks like you, to support the product and tell Microsoft to fix it.
This is a limitation, a serious one too, which however we believe MS could and should fix.
Have you tried placing this file on the desktop or in your documents folder and then telling the bulk insert command to look there?
The text file has 4 columns (sometimes 5 or 6) and destination table has 25 columns (all nvarchar(max). How will BULK INSERT WORK IN THIS CASE
Use SUBSTRING function.
Hi,
I have a table CREATE TABLE [dbo].[D256M] (
[EDI_FILE_TYPE] VARCHAR(6) DEFAULT ‘ ‘ NOT NULL,
[EDI_FILE_NAME] VARCHAR(40),
[EDI_FILE_PATH] VARCHAR(120),
[EDI_FILE_SERVER_FLAG] VARCHAR(1),
[EDI_FILE_COUNT] DECIMAL(5,0),
[EDI_FILE_AUDIT_FLAG] VARCHAR(1),
[EDI_FILE_NAME_FIXED] VARCHAR(1),
[EDI_FILE_PATH_FIXED] VARCHAR(1),
CONSTRAINT [PK_D256M] PRIMARY KEY CLUSTERED (
[EDI_FILE_TYPE]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and when i try to insert data into this table using text file below is text file data
NVREP|NULL|NULL|NULL|1|Y|NULL|NULL
P11D|P11DYJ|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|20|Y|Y|Y
P11DB|P11DB_.EDI|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|1|Y|Y|Y
P14|NULL|NULL|NULL|NULL|NULL|NULL|NULL
P160|NULL|NULL|NULL|NULL|NULL|NULL|NULL
P35|NULL|NULL|NULL|NULL|NULL|NULL|NULL
P45(1)|P451YJ.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|105|Y|NULL|NULL
P45(3)|P453YJ.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|98|Y|NULL|NULL
P46|P46YJ.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|98|Y|NULL|NULL
P46EXP|P46EXP.txt|NULL|NULL|NULL|NULL|NULL|NULL
P46PEN|P46PEN.txt|NULL|NULL|NULL|NULL|NULL|NULL
P6|NULL|NULL|NULL|NULL|NULL|NULL|NULL
P9|NULL|NULL|NULL|NULL|NULL|NULL|NULL
RTIEAS|EAS.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|7|Y|Y|Y
RTIEPS|EPS.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|43|Y|Y|Y
RTIFPS|FPS.TXT|C:\RESOUR~1\YJLIVE\RLDATA\EDI\OUTBOUND\|Y|238|Y|Y|Y
RTIGEN|NULL|NULL|NULL|1|Y|NULL|NULL
RTINVR|NVR_.TXT|NULL|Y|1|Y|Y|Y
SL1|NULL|NULL|NULL|NULL|NULL|NULL|NULL
SL2|NULL|NULL|NULL|NULL|NULL|NULL|NULL
WNU|NULL|NULL|NULL|NULL|NULL|NULL|NULL
WTC|NULL|NULL|NULL|NULL|NULL|NULL|NULL
I think here NULL is taken as 4 character value and its not getting inserted
BULK INSERT D256M
FROM ‘K:\livetotest\D256M.TXT’
WITH (FIELDTERMINATOR = ‘|’ , ROWTERMINATOR = ‘\n’ , KEEPNULLS)
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 13, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 19, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 20, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 21, column 5 (EDI_FILE_COUNT).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 22, column 5 (EDI_FILE_COUNT).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
Hi sir
i have a csv file with date as 20171116 (it is nov 16,2017) i am getting message
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 25 (ShipDt).
shipdt in table is date type column, how i can get the date from csv file to this field. Please note i cannot change the contents of csv file.
thanks for your help
I am getting the same error ” Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (areafips).”. However, there are no null values. Areafips has a value of 12345. I am using a xml format file. Is the error more likely in the data or the format file?