October 1, 2010 at 8:33 am
Hi friends,
I am trying to insert a bulk data into my database. I follow this steps,
i attached my text file with this,
my table is look like this...
Column NameType
===============================
sr.no int (pk)
id int
name varchar(20)
profile varchar(max)
dob datetime
picture image
add varchar(20)
city varchar(5)
----------------------------------------------------
i am using this command for bulk insert into my table.
BULK INSERT TmpStList FROM 'c:\TxtFile1.txt' WITH (FIELDTERMINATOR = '","')
i am getting this error,
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 1 (sr.no).
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 (sr.no).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (sr.no).
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 1 (sr.no).
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 1 (sr.no).
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 1 (sr.no).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (sr.no).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (sr.no).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (sr.no).
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 1 (sr.no).
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 1 (sr.no).
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)".
pls help me out by this problem.
Thanks in Advance.
M.I
________________________________________
M.I.
[font="Times New Roman"]
October 1, 2010 at 1:58 pm
Try using a format file. This will help you get started
SELECT * FROM OPENROWSET (BULK 'C:\TxtFile1.txt', FORMATFILE = 'C:\TxtFile1.fmt') AS Z
TxtFile1.fmt
7.0
9
1 SQLCHAR 0 0 "," 1 col1
2 SQLCHAR 0 0 ",\"" 2 col2
3 SQLCHAR 0 0 "\",\"" 3 col3
4 SQLCHAR 0 0 "\",\"" 4 col4
5 SQLCHAR 0 0 "\",\"" 5 col5
6 SQLCHAR 0 0 "\",\"" 6 col6
7 SQLCHAR 0 0 "\",\"" 7 col7
8 SQLCHAR 0 0 "\",\"" 8 col8
9 SQLCHAR 0 0 "\"\r\n" 9 col9
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply