Bulk insert fails when we use a format file
-
Hi all,
I am having a bit of bother with Bulk Insert. Here are the details of what I am doing
1. From command line I run "bcp {DBName}.dbo.tblUsers out c:\data.csv -S"{Server\Instance}" -U"{username}" -P"{password}" -w -t"," -r"/n"' "
2. From SQL Query Analyser I run "
BULK INSERT inrsSQL.dbo.tblUsersTest
FROM 'c:\data.CSV'
WITH
(
FORMATFILE = 'C:\bcp.fmt'
)
"
This doesn't work. It produces the error message "
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
"
When I run
BULK INSERT inrsSQL.dbo.tblUsersTest
FROM 'c:\data.CSV'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
I get the good old "212 rows affected" message.
I don't understand why the format file method isn't working. The CSV and FMT files are produced from the bcp command so they should be compatible for the reverse operation with Bulk Insert.
For completeness of info here is the FMT file contents:"
8.0
5
1 SQLINT 1 4 "," 1 ID ""
2 SQLNCHAR 0 0 "," 2 Forename SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "," 3 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "," 4 Email SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 0 "\r\n" 5 Password SQL_Latin1_General_CP1_CI_AS
"
In the table, column 1 is an integer "
ID is an identity integer with increment of 1 and the rest are all nVarChar(150)
Your help here would be greatly appreciated.
Thanks
Robert
-
Not sure about the error but because your file is character you cannot use SQLINT for field definitions although BCP will output this in a fmt file by default.
Try this
8.0
5
1 SQLCHAR 0 12 "," 1 ID ""
2 SQLCHAR 0 150 "," 2 Forename SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 150 "," 3 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 150 "," 4 Email SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 150 "\r\n" 5 Password SQL_Latin1_General_CP1_CI_AS
p.s. added after posting
The input ID value will be ignored as the column in the dest table is identity
Far away is close at hand in the images of elsewhere.
Anon. -
Nice one bro. That worked a treat!
I understand about the 150 values but why is the length of the int not 4?
Ta muchly
Robert
PS (also added after posting
)Actually, I looked again at your post and then noticed you changed the SQLINT to SQLCHAR. When I changed the format file I neglected to do so. It seems that changing the length of the fields was sufficient.
Cheers
Robert
-
Not sure how that got there I think BCP did it when I experimented with creating output fmt files
After experimenting further I found that the lengths do not seem to matter, I managed to get it to work with all zero lengths
Your error though was due to the prefix length of 1 on the first column (ID), by setting this to zero was all that was needed for your fmt file to work
Because ID in your table is IDENTITY then it does not matter what the definition of the column is as it will be ignored. However if that column was not identity and was required (or any input int column) then you would have to use SQLCHAR otherwise you would get an incoorect value.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply