Sql-server – Bulk load data conversion error (truncation) for row 1, column 1 (Date) error

bulkinsertexcel-2010sql servertemp-tables

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.

Best Answer

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

Related Topic