question

katie 2 avatar image
katie 2 asked

Bulk Insert and format file not working

I hope someone can help with my dilemna. It's probably super simple that i'm missing but I need to get to the bottom of it so I can do future bulk loads. Problem: When using bulk insert with a format file (created by bcp). format file sample: 9.0 29 1 SQLINT 0 4 "," 1 PERSONID "" 2 SQLINT 0 4 "," 2 SecondID "" 3 SQLNCHAR 2 10 "," 3 Field1 4 SQLNCHAR 2 10 "," 4 Field2 ... 29 SQLNCHAR 2 200 "\r\n" 29 Field29 **Example data:** 1111111,4,NO,NO,NO,YES,NO,1111111,111111,111111,Hourly - Active,#222222222222222222,,,,,,,,,,,,,Lots of text here,#222222222222222222,08/31/2011,, There error says the column row one is being truncated due to conversion failure. When I use the SSMS import wizard it sees the 1st two columns as float. If I'm using a format file to force sql to use a certain data type why is still trying to convert these columns to something else? I hope there is enough here to explain the problem. Anyway help would be greatly appreciated as I do not want to keep importing these files into our production database. I would like to use the bulk insert into a temp table, do my updating as necessary, then remove the temp table. Thanks so much! K **Additional Information (16SEP2011)** Create table script: create table #TempTable ( PERSONID int, Secondid int, Field1 NVARCHAR(5), Field2 NVARCHAR(5), Field3 NVARCHAR(5), Field4 NVARCHAR(5), Field5 NVARCHAR(5), Field6 NVARCHAR(10), Field7 NVARCHAR(10), Field8 NVARCHAR(10), Field9 NVARCHAR(50), Field10 NVARCHAR(100) , Field11 NVARCHAR(50), Field12 NVARCHAR(100) , Field13 NVARCHAR(50), Field14 NVARCHAR(100), Field15 NVARCHAR(50), Field16 NVARCHAR(100), Field17 NVARCHAR(50), Field18 NVARCHAR(5), Field19 NVARCHAR(5), Field20 NVARCHAR(5), Field21 NVARCHAR(50), Field22 NVARCHAR(50), Field23 nvarchar(100), Field24 nvarchar(100), Field25 nvarchar(100), Field26 nvarchar(100) , Field27 nvarchar(100) ) --grab the data into the temporary table BULK INSERT #TempTable FROM 'D:sqlupdatesSampleData.csv' WITH (FORMATFILE='D:sqlupdatesSampleData-n.fmt') --Specify filename to use here select * from #TempTable Here is the bcp syntax used to create the format file: bcp AdventureWorks2008R2.HumanResources.Department format nul -T -n -f Department-n.fmt -i used native (-n) because the character version wasn't working plus i have to do an inner join on the personid which is an int, so I was trying to save myself from doing a cast after the fact. Format file: 9.0 29 1 SQLINT 0 4 "," 1 PERSONID "" 2 SQLINT 0 4 "," 2 SecondId "" 3 SQLNCHAR 2 10 "," 3 Field1 SQL_Latin1_General_CP1_CI_AS 4 SQLNCHAR 2 10 "," 4 Field2 SQL_Latin1_General_CP1_CI_AS 5 SQLNCHAR 2 10 "," 5 Field3 SQL_Latin1_General_CP1_CI_AS 6 SQLNCHAR 2 10 "," 6 Field4 SQL_Latin1_General_CP1_CI_AS 7 SQLNCHAR 2 10 "," 7 Field5 SQL_Latin1_General_CP1_CI_AS 8 SQLNCHAR 2 20 "," 8 Field6 SQL_Latin1_General_CP1_CI_AS 9 SQLNCHAR 2 20 "," 9 Field7 SQL_Latin1_General_CP1_CI_AS 10 SQLNCHAR 2 20 "," 10 Field8 SQL_Latin1_General_CP1_CI_AS 11 SQLNCHAR 2 100 "," 11 Field9 SQL_Latin1_General_CP1_CI_AS 12 SQLNCHAR 2 200 "," 12 Field10 SQL_Latin1_General_CP1_CI_AS 13 SQLNCHAR 2 100 "," 13 Field11 SQL_Latin1_General_CP1_CI_AS 14 SQLNCHAR 2 200 "," 14 Field12 SQL_Latin1_General_CP1_CI_AS 15 SQLNCHAR 2 100 "," 15 Field13 SQL_Latin1_General_CP1_CI_AS 16 SQLNCHAR 2 200 "," 16 Field14 SQL_Latin1_General_CP1_CI_AS 17 SQLNCHAR 2 100 "," 17 Field15 SQL_Latin1_General_CP1_CI_AS 18 SQLNCHAR 2 200 "," 18 Field16 SQL_Latin1_General_CP1_CI_AS 19 SQLNCHAR 2 100 "," 19 Field17 SQL_Latin1_General_CP1_CI_AS 20 SQLNCHAR 2 10 "," 20 Field18 SQL_Latin1_General_CP1_CI_AS 21 SQLNCHAR 2 10 "," 21 Field19 SQL_Latin1_General_CP1_CI_AS 22 SQLNCHAR 2 10 "," 22 Field20 SQL_Latin1_General_CP1_CI_AS 23 SQLNCHAR 2 100 "," 23 Field21 SQL_Latin1_General_CP1_CI_AS 24 SQLNCHAR 2 100 "," 24 Field22 SQL_Latin1_General_CP1_CI_AS 25 SQLNCHAR 2 200 "," 25 Field23 SQL_Latin1_General_CP1_CI_AS 26 SQLNCHAR 2 200 "," 26 Field24 SQL_Latin1_General_CP1_CI_AS 27 SQLNCHAR 2 200 "," 27 Field25 SQL_Latin1_General_CP1_CI_AS 28 SQLNCHAR 2 200 "," 28 Field26 SQL_Latin1_General_CP1_CI_AS 29 SQLNCHAR 2 200 "rn" 29 Field27 SQL_Latin1_General_CP1_CI_AS Sample data: 1111111,4,NO,NO,NO,YES,NO,0011001,011110,1234,Hourly - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, 2222222,47,NO,NO,YES,NO,NO,0022002,022220,5678,Salaried - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, 3333333,15,NO,NO,YES,NO,NO,0033003,033330,9101,Salaried - Active,#9101111111111111111111,,,,,,,,,,,,,There is a long string of text here. xxxxxx,#9101111111111111111111,08/31/2011,, error message: Msg 4863, Level 16, State 4, Line 1 Bulk load data conversion error (truncation) for row 1, column 1 (PERSONID). 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)". Ok - I hope this gives enough info to resolve this. Thanks!! K
sql-server-2005bcpbulk-insertformatting
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you post the create script for the table as well as the entire format file and complete error message? Is the one sample data row affected by the error?
0 Likes 0 ·
I will try to put together a sample set today. I was trying not to put company info on the web. I'll post back the error. thanks.
0 Likes 0 ·
I couldn't figure out how to change my original post, so I just posted the needed info in the "Answer". I still need help. :)
0 Likes 0 ·

1 Answer

· Write an Answer
JD avatar image
JD answered
Hi, I' ve create a scenario using all data you give and it'does'nt work so, I have change your BULK INSERT sentence to: > BULK INSERT TempTable FROM > 'D:\tmp\ test.txt' WITH > (fieldterminator=',') ommiting the format file and it works fine..!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.