Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SSIS and Import/Export (2008)  Incorrect host-column number found in BCP format-f

Author  Topic  dcs Starting Member 6 Posts Posted - 2011-09-02 : 11:26:14 Hi all, Apologies for putting a big table and question. I have a file which has both commas and " being used as delimiters. So I created a format file to input the data into my table. I created a table dbo.Testing for this and tried using bcp format file to input the data into it but after several attempts, and applying many permutations combinations it doesnt seem to work. Every time it gives the error SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file CREATE TABLE [dbo].[Testing]( [Company] [varchar](2) NULL, [Item] [varchar](15) NULL, [Item Description] [varchar](80) NULL, [IssueUOM] [varchar](2) NULL, [PackSize] [numeric](11, 4) NULL, [ShippingFormat] [varchar](10) NULL, [CartonHeight] [numeric](9, 4) NULL, [CartonWidth] [numeric](9, 4) NULL, [CartonDepth] [numeric](9, 4) NULL, [CartonVolume] [numeric](9, 4) NULL, [CartonWeight] [numeric](9, 4) NULL, [ShipperHeight] [numeric](9, 4) NULL, [ShipperWidth] [numeric](9, 4) NULL, [ShipperDepth] [numeric](9, 4) NULL, [ShipperVolume] [numeric](9, 4) NULL, [ShipperWeight] [numeric](9, 4) NULL, [CartonsPerShipper] [numeric](9, 0) NULL, [ShipperQuantity] [numeric](9, 0) NULL, [PalletTi] [numeric](9, 0) NULL, [PalletHi] [numeric](9, 0) NULL, [PalletWeight] [numeric](9, 0) NULL, [PalletQuantity] [numeric](9, 0) NULL, [PrimaryStockroom] [varchar](2) NULL, [Supplier] [varchar](8) NULL, [CountryCode] [varchar](3) NULL, [GTFamily] [varchar](15) NULL, [MinOrderQty] [numeric](11, 3) NULL, [FOBPurchasePrice] [numeric](15, 5) NULL, [FobCurrency] [varchar](3) NULL, [StandardCost] [numeric](15, 5) NULL, [StandardCostCur] [varchar](3) NULL, [JSP] [numeric](15, 5) NULL, [JSPCurrency] [varchar](3) NULL, [TariffCode] [varchar](8) NULL, [TariffCodeDesc] [varchar](30) NULL, [UPNBarcode] [varchar](17) NULL, [EANBarcode] [varchar](28) NULL ) In the format file I have used the following field delimiters as per the field having or not having double quotes, Field Starts With Field Ends With Next Field Starts Field Terminator Example 1. “ “ “ “\”,\”” “AB”,”CD” 2. “ “ After Comma(Without “) “\”,” “AB”,123 3. After Comma (Without “) Comma With “ “\,”” 123,”AB” 4. After Comma Comma After Comma “,” 123,123 The format file is as follows:- 10.0 37 1 SQLCHAR 0 2 "\",\"" 1 Company Latin1_General_CI_AS 2 SQLCHAR 0 15 "\",\"" 2 Item Latin1_General_CI_AS 3 SQLCHAR 0 80 "\",\"" 3 ItemDescription Latin1_General_CI_AS 4 SQLCHAR 0 2 "\"," 4 IssueUOM Latin1_General_CI_AS 5 SQLCHAR 0 41 "," 5 PackSize "" 6 SQLCHAR 0 10 "\"," 6 ShippingFormat Latin1_General_CI_AS 7 SQLCHAR 0 41 "," 7 CartonHeight "" 8 SQLCHAR 0 41 "," 8 CartonWidth "" 9 SQLCHAR 0 41 "," 9 CartonDepth "" 10 SQLCHAR 0 41 "," 10 CartonVolume "" 11 SQLCHAR 0 41 "," 11 CartonWeight "" 12 SQLCHAR 0 41 "," 12 ShipperHeight "" 13 SQLCHAR 0 41 "," 13 ShipperWidth "" 14 SQLCHAR 0 41 "," 14 ShipperDepth "" 15 SQLCHAR 0 41 "," 15 ShipperVolume "" 16 SQLCHAR 0 41 "," 16 ShipperWeight "" 17 SQLCHAR 0 41 "," 17 CartonsPerShipper "" 18 SQLCHAR 0 41 "," 18 ShipperQuantity "" 19 SQLCHAR 0 41 "," 19 PalletTi "" 20 SQLCHAR 0 41 "," 20 PalletHi "" 21 SQLCHAR 0 41 "," 21 PalletWeight "" 22 SQLCHAR 0 41 "\,"" 22 PalletQuantity "" 23 SQLCHAR 0 2 "\",\"" 23 PrimaryStockroom Latin1_General_CI_AS 24 SQLCHAR 0 8 "\",\"" 24 Supplier Latin1_General_CI_AS 25 SQLCHAR 0 3 "\",\"" 25 CountryCode Latin1_General_CI_AS 26 SQLCHAR 0 15 "\"," 26 GTFamily Latin1_General_CI_AS 27 SQLCHAR 0 41 "," 27 MinOrderQty "" 28 SQLCHAR 0 41 "\,"" 28 FOBPurchasePrice "" 29 SQLCHAR 0 3 "\"," 29 FobCurrency Latin1_General_CI_AS 30 SQLCHAR 0 41 "\,"" 30 StandardCost "" 31 SQLCHAR 0 3 "\"," 31 StandardCostCur Latin1_General_CI_AS 32 SQLCHAR 0 41 "\,"" 32 JSP "" 33 SQLCHAR 0 3 "\",\"" 33 JSPCurrency Latin1_General_CI_AS 34 SQLCHAR 0 8 "\",\"" 34 TariffCode Latin1_General_CI_AS 35 SQLCHAR 0 30 "\",\"" 35 TariffCodeDesc Latin1_General_CI_AS 36 SQLCHAR 0 17 "\",\"" 36 UPNBarcode Latin1_General_CI_AS 37 SQLCHAR 0 28 "\"\r\n" 37 EANBarcode Latin1_General_CI_AS Sample of Data to be inserted "AB", "530002 ","2 x Coffee Mugs, Lid and Spoon ", "MD" , 12.0000 ," ",12.8346,12.2047 ,9.6457 ,1510.9260 ,4.1447 ,.0000 ,.0000,.0000 ,.0000 ,.0000 ,0 ,0 ,0 ,0,.0000,0 ,"NJ","PMRTYS50","HK ","JCL ",12000.000 ,.00000 ,"HKD",.00000 ,"USD",.00000 ,"USD","00123008"," ","987654321123 ","(24)11223344556677(99)00 " "CD","530004 ","TT Crystal Bowls x 4 ", "MK" , 12.0000 ," ",12.9921,12.2047 ,11.8110,1872.8075 ,5.7320 ,.0000 ,.0000 ,.0000 ,.0000 ,.0000,0 ,0 ,0 ,0,.0000,0 ,"NJ","DHTSK50","CN ","JCL ",6000.000 ,.00000 ,"HKD",.00000 ,"USD",.00000 ,"USD","12341123"," ","123456789987 ","(23)12345678911223 (88)00 " H:\>bcp Trial.dbo.Testing in E:\Trialfiles\Sample.txt -f Test3-c.fmt -S uklesq01 –T SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Incorrect host-column number found in BCP format-file Please help. Thanks in advance. DC