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:
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
get the row number of that first row where the data actually is.
then you use this:
where X is the row number where the data actually starts and not the headers