mysql sqlserver firstrow=2_sql server - Bulk load data conversion error (truncation) for row 1, colu...

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.

sql-server

excel-2010

bulkinsert

temp-tables

|

this question asked Jul 8 '15 at 2:36

gene 365 7 23

#tmptable.[Date] should be of

DATE type and not

VARCHAR. –

Felix Pamittan Jul 8 '15 at 2: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 8 '15 at 2:40 1   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 8 '15 at 2: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 8 '15 at 2:51      I have to ask, did you also change the data type of

#tmptable.[Date] to

DATE? Just making sure. –

James Joyce Alano Jul 8 '15 at 2:54

|

show more comments

1 Answers

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 errorout here. I have an excel sheet that has a bunch of columns, one of them being a date column. When I use an Excel Source and link it to a Slowly Changing Dimension transformation everything goes great until I click the last button to config

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

|

this answer answered Jul 8 '15 at 3:15

James Joyce Alano 663 1 13      This is the only one file for now. Some files' first row might be different. These files are entered manually. –

gene Jul 8 '15 at 3: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 8 '15 at 3: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 8 '15 at 3:26      for excel 2010 and ssms 2012 its not working for me.. please give suggestions –

kasim Jul 8 '15 at 6: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 6 at 20:48

|

o nowhere. I created a table in SQL Express 2008 R2 using the following script: CREATE TABLE Features(ID int not null identity(1,1 ),StopID varchar(10), Code int,Name varchar(100),Summary varchar(200),Lat real,Lon real,street varch