Bulk Insert Error
in [Programming SQL]
|
Prev: oracle RowType Issue
Next: Problem Using a UDF in a Constraint to Enforce Non-overlappingDate Periods
From: Brian Conner via SQLMonster.com on 20 Jan 2010 15:49 I am getting the following error when trying to perform a Bulk Insert: 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 2 (GROUP_ID) Here is my SQL Code: BULK INSERT DeepDiveGroup8.dbo.atb_tbl FROM 'C:\Test\GRP 8 NEW ATB DETAIL DEC 2009.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', KEEPNULLS ) Here is a sample of the Text File: "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE HORIZONS"|"SECURE HORIZONS"|"1280"|"09/18/2009"|"12/10/2009"|"SJC NON HOSPITAL"|""|"SJC-QUEST DIAGNOSTICS NICHOLS"|"SJCQIL"|"PRESBYTERIAN HOSP. OF DENTON"|"OHGI1083"|"24756"|"CASEY MD,SHARON K"|"G92253"|"1093795908"|" "|"754. 52"|"SECURE HORIZONS - 30975"|"PO BOX 30975"|" "|"SALT LAKE CITY, UT"|"84130"|"PEREZ,SABAS"|"999-00-0000"|"02/25/1900"|"0086836"|"10912"|"REITZ MD,RICHARD E"|"12/11/2009"|"IBCSJC-2009120901"|"754. 52"|"0"|"HCFAT2"|"AP"|"NON HOSPITAL"|" "|"288.00"|"88237"|"SJC-QUEST DIAGNOSTICS NICHOLS I" "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE HORIZONS"|"SECURE HORIZONS"|"1280"|"09/18/2009"|"12/10/2009"|"SJC NON HOSPITAL"|""|"SJC-QUEST DIAGNOSTICS NICHOLS"|"SJCQIL"|"PRESBYTERIAN HOSP. OF DENTON"|"OHGI1083"|"24756"|"CASEY MD,SHARON K"|"G92253"|"1093795908"|" "|"754. 52"|"SECURE HORIZONS - 30975"|"PO BOX 30975"|" "|"SALT LAKE CITY, UT"|"84130"|"PEREZ,SABAS"|"999-00-0000"|"02/25/1900"|"0086836"|"10912"|"REITZ MD,RICHARD E"|"12/11/2009"|"IBCSJC-2009120901"|"754. 52"|"0"|"HCFAT2"|"AP"|"NON HOSPITAL"|" "|"288.00"|"88264"|"SJC-QUEST DIAGNOSTICS NICHOLS I" "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE HORIZONS"|"SECURE HORIZONS"|"1280"|"09/18/2009"|"12/10/2009"|"SJC NON HOSPITAL"|""|"SJC-QUEST DIAGNOSTICS NICHOLS"|"SJCQIL"|"PRESBYTERIAN HOSP. OF DENTON"|"OHGI1083"|"24756"|"CASEY MD,SHARON K"|"G92253"|"1093795908"|" "|"754. 52"|"SECURE HORIZONS - 30975"|"PO BOX 30975"|" "|"SALT LAKE CITY, UT"|"84130"|"PEREZ,SABAS"|"999-00-0000"|"02/25/1900"|"0086836"|"10912"|"REITZ MD,RICHARD E"|"12/11/2009"|"IBCSJC-2009120901"|"754. 52"|"0"|"HCFAT2"|"AP"|"NON HOSPITAL"|" "|"288.00"|"88291"|"SJC-QUEST DIAGNOSTICS NICHOLS I" "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE HORIZONS"|"SECURE HORIZONS"|"1280"|"09/18/2009"|"12/10/2009"|"SJC NON HOSPITAL"|""|"SJC-QUEST DIAGNOSTICS NICHOLS"|"SJCQIL"|"PRESBYTERIAN HOSP. OF DENTON"|"OHGI1083"|"24756"|"CASEY MD,SHARON K"|"G92253"|"1093795908"|" "|"754. 52"|"SECURE HORIZONS - 30975"|"PO BOX 30975"|" "|"SALT LAKE CITY, UT"|"84130"|"PEREZ,SABAS"|"999-00-0000"|"02/25/1900"|"0086836"|"10912"|"REITZ MD,RICHARD E"|"12/11/2009"|"IBCSJC-2009120901"|"754. 52"|"0"|"HCFAT2"|"AP"|"NON HOSPITAL"|" "|"288.00"|" "|"SJC-QUEST DIAGNOSTICS NICHOLS I" Can anyone help me figure out what's wrong? I am a T-SQL Beginner..... -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
From: Erland Sommarskog on 20 Jan 2010 18:16 Brian Conner via SQLMonster.com (u47161(a)uwe) writes: > I am getting the following error when trying to perform a Bulk Insert: > > 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 2 (GROUP_ID) > > Here is my SQL Code: > > BULK INSERT DeepDiveGroup8.dbo.atb_tbl > FROM 'C:\Test\GRP 8 NEW ATB DETAIL DEC 2009.txt' > WITH > ( > FIELDTERMINATOR = '|', > ROWTERMINATOR = '\n', > KEEPNULLS > ) > > > Here is a sample of the Text File: > > "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE So that is not going to work out. I'm a afraid that you need to write a format file. You will have to excuse me, because the hour is late, so this is a real a quick outline. The format file should look like this: 9.0 47 1 SQLCHAR 0 0 "\"" 0 "" "" 2 SQLCHAR 0 0 "\"|\"" 1 col1 Latin1_General_CS_AS 3 SQLCHAR 0 0 "\"|\"" 2 col2 Latin1_General_CS_AS .... 46 SQLCHAR 0 0 "\"|\"" 45 col45 Latin1_General_CS_AS 47 SQLCHAR 0 0 "\"\n" 46 col46 Latin1_General_CS_AS In the example I assume that the table and the file has 46 columns/fields. The first line specifies an empty element which is the first quote. This field is not imported, whence the 0. For the rest of the file, I specify "|" as the delimiter, but for the last field. Give this a try. Hopefully someone else can bump in if you get problems. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Brian Conner via SQLMonster.com on 21 Jan 2010 08:42 Thank you Erland for your response.... I changed the table, source file and format file to only 3 fields for this test Source File: "SJCQ"|"8"|"12285045" "SJCQ"|"8"|"12285045" "SJCQ"|"8"|"12285045" "SJCQ"|"8"|"12285045" I created the Format File: 9.0 3 1 SQLCHAR 0 0 "\"" 0 "" "" 2 SQLCHAR 0 80 "\"|\"" 1 Division Latin1_General_CS_AS 3 SQLCHAR 0 80 "\"|\"" 2 Group_ID Latin1_General_CS_AS 4 SQLCHAR 0 80 "\"\n" 3 Invoice_Number Latin1_General_CS_AS My SQL Code is: BULK INSERT DeepDiveGroup8.dbo.atb_tbl FROM 'C:\Test\GRP 8 NEW ATB DETAIL DEC 2009.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n', formatfile = 'c:\Test\Test_Format_File.txt', KEEPNULLS ) When I Execute I get the below 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 3, column 3 (GROUP_ID). I can't figure out what is wrong with Column 3 I have it as INT datatype in the Table Erland Sommarskog wrote: >> I am getting the following error when trying to perform a Bulk Insert: >> >[quoted text clipped - 16 lines] >> >> "SJCQ"|"8"|"12285045"|"DLDLAW271209"|"001951301"|"1280"|"SECURE > >So that is not going to work out. I'm a afraid that you need to write >a format file. You will have to excuse me, because the hour is late, >so this is a real a quick outline. > >The format file should look like this: > >9.0 >47 >1 SQLCHAR 0 0 "\"" 0 "" "" >2 SQLCHAR 0 0 "\"|\"" 1 col1 Latin1_General_CS_AS >3 SQLCHAR 0 0 "\"|\"" 2 col2 Latin1_General_CS_AS >... >46 SQLCHAR 0 0 "\"|\"" 45 col45 Latin1_General_CS_AS >47 SQLCHAR 0 0 "\"\n" 46 col46 Latin1_General_CS_AS > >In the example I assume that the table and the file has 46 columns/fields. >The first line specifies an empty element which is the first quote. >This field is not imported, whence the 0. For the rest of the file, >I specify "|" as the delimiter, but for the last field. > >Give this a try. Hopefully someone else can bump in if you get problems. > -- Brian Conner Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
From: Erland Sommarskog on 21 Jan 2010 18:13
Brian Conner via SQLMonster.com (u47161(a)uwe) writes: > Thank you Erland for your response.... > > I changed the table, source file and format file to only 3 fields for this > test > > Source File: > > "SJCQ"|"8"|"12285045" > "SJCQ"|"8"|"12285045" > "SJCQ"|"8"|"12285045" > "SJCQ"|"8"|"12285045" > > > > > I created the Format File: > > 9.0 > 3 > 1 SQLCHAR 0 0 "\"" 0 "" "" > 2 SQLCHAR 0 80 "\"|\"" 1 Division Latin1_General_CS_AS > 3 SQLCHAR 0 80 "\"|\"" 2 Group_ID Latin1_General_CS_AS > 4 SQLCHAR 0 80 "\"\n" 3 Invoice_Number Latin1_General_CS_AS Unfortunately, I read this about the same time as I did yesterday, so I don't have time to test. But change 80 to 0, and more importantly, \n should be \r\n. That was an error on my part last night, sorry! -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |