Bulk load data conversion error (truncation) for row 1, column 1 (Date) error

Question

I have an excel file that I want to bulk insert into temp table:

create table #tmptable
(
    Date varchar(10),
    Receipt varchar(50),
    Description varchar(100),
    [Card Member] varchar(50),
    [Account #] varchar(17),    
    Amount varchar(20)
)

bulk insert #tmptable
from 'C:\Transactions\example.xls'
with (FieldTerminator='\t', RowTerminator = '\n')
go

This is my excel file:

enter image description here

When executing bulk statement, getting the following error:

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

Do not know why it happens.

`#tmptable.[Date]` should be of `DATE` type and not `VARCHAR`. — Felix Pamittan, Jul 08 '15 at 02:37
Does not help. Have another similar error: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Date)" — gene, Jul 08 '15 at 02:40
I think your bulk insert also reads the headers. Try adding `FirstRow= 2` on your `with` statement and follow @wewesthemenace 's advice on using `DATE ` type. — James Joyce Alano, Jul 08 '15 at 02:48
I added FirstRow=2, and getting another error: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Date)." — gene, Jul 08 '15 at 02:51
I have to ask, did you also change the data type of `#tmptable.[Date]` to `DATE`? Just making sure. — James Joyce Alano, Jul 08 '15 at 02:54
Well, can you post the actual .xls data? I mean not the one without images and formatting. Or is this the actual the content of the .xls file? — James Joyce Alano, Jul 08 '15 at 03:00
Related: http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database — Felix Pamittan, Jul 08 '15 at 03:24

score 1 · Answer 1 · answered Jul 08 '15 at 03:15

1

Well, you are actually reading your headers, meaning the the data on the first few rows of your xls are images that's why you are getting a type mismatch error

enter image description here

get the row number of that first row where the data actually is.

then you use this:

create table #tmptable
(
    Date date,
    Receipt varchar(50),
    Description varchar(100),
    [Card Member] varchar(50),
    [Account #] varchar(17),    
    Amount varchar(20)
)

bulk insert #tmptable
from 'C:\Transactions\example.xls'
with (FieldTerminator='\t', RowTerminator = '\n', FirstRow = X)
go

where X is the row number where the data actually starts and not the headers

answered Jul 08 '15 at 03:15

James Joyce Alano

703
3
13

This is the only one file for now. Some files' first row might be different. These files are entered manually. – gene Jul 08 '15 at 03:21
Well, you need to manually enter those first rows, bulkinsert will not skip over 'non-data' files. It will be up to you to determine which is the data and which is NOT the data. But for now this should get you through. – James Joyce Alano Jul 08 '15 at 03:23
But how to determine when data starts. I tried to set `FirsRow = 8` since that's where data starts and I got "(0 row(s) affected)" – gene Jul 08 '15 at 03:26
for excel 2010 and ssms 2012 its not working for me.. please give suggestions – kasim Jul 08 '15 at 06:51
If you really have lots of files like these, you better have another mecanism that reproduce all the files without headers, with datas starting right at first row, so you can work with them easely with your code. Just in case you haven't considered yet – Antoine Pelletier Mar 06 '17 at 20:48

Bulk load data conversion error (truncation) for row 1, column 1 (Date) error

1 Answers1