March 9, 2005 at 6:15 am
David,
As promised, here's the actual BCP format file and the call to BCP to use it (from a command window). This has been working correctly for almost a year. Of course, the BCP command is parameterized to protect the login and password information. Sure hope this helps...
8.0
46
1 SQLCHAR 0 1 "" 0 Leadingquote ""
2 SQLCHAR 0 150 "\",\"" 2 CompanyName ""
3 SQLCHAR 0 150 "\",\"" 3 Contact_First ""
4 SQLCHAR 0 150 "\",\"" 4 Contact_Last ""
5 SQLCHAR 0 150 "\",\"" 5 OrderID ""
6 SQLCHAR 0 150 "\",\"" 6 CovadCircuitNumber ""
7 SQLCHAR 0 150 "\",\"" 7 CustomerBillingCode ""
8 SQLCHAR 0 150 "\",\"" 8 Status ""
9 SQLCHAR 0 250 "\",\"" 9 ServiceBrand ""
10 SQLCHAR 0 150 "\",\"" 10 OrderingLogin ""
11 SQLCHAR 0 150 "\",\"" 11 CancelReason ""
12 SQLCHAR 0 150 "\",\"" 12 DisconnectReason ""
13 SQLCHAR 0 150 "\",\"" 13 OrderReceivedDate ""
14 SQLCHAR 0 150 "\",\"" 14 LineOrderedDate ""
15 SQLCHAR 0 150 "\",\"" 15 LineCommittedDate ""
16 SQLCHAR 0 150 "\",\"" 16 LineAcceptedDate ""
17 SQLCHAR 0 150 "\",\"" 17 AppointmentScheduledDate ""
18 SQLCHAR 0 150 "\",\"" 18 AppointmentCompletedDate ""
19 SQLCHAR 0 150 "\",\"" 19 OrderCanceledDate ""
20 SQLCHAR 0 150 "\",\"" 20 OrderClosedDate ""
21 SQLCHAR 0 150 "\",\"" 21 CentralOfficeCode ""
22 SQLCHAR 0 150 "\",\"" 22 Email ""
23 SQLCHAR 0 150 "\",\"" 23 ContactTelephone ""
24 SQLCHAR 0 150 "\",\"" 24 Street ""
25 SQLCHAR 0 150 "\",\"" 25 Unit ""
26 SQLCHAR 0 150 "\",\"" 26 City ""
27 SQLCHAR 0 150 "\",\"" 27 State ""
28 SQLCHAR 0 150 "\",\"" 28 Zip ""
29 SQLCHAR 0 150 "\",\"" 29 SiteTelephone ""
30 SQLCHAR 0 150 "\",\"" 30 CPEName ""
31 SQLCHAR 0 150 "\",\"" 31 CPEPassword ""
32 SQLCHAR 0 150 "\",\"" 32 NetworkDescription ""
33 SQLCHAR 0 150 "\",\"" 33 CPECovadProvided ""
34 SQLCHAR 0 150 "\",\"" 34 CPECovadConfigured ""
35 SQLCHAR 0 150 "\",\"" 35 InsideWiringAuthorized ""
36 SQLCHAR 0 150 "\",\"" 36 BackHaulCircuit ""
37 SQLCHAR 0 150 "\",\"" 37 AssignedPVC ""
38 SQLCHAR 0 150 "\",\"" 38 RequestedPVC ""
39 SQLCHAR 0 150 "\",\"" 39 WanMask ""
40 SQLCHAR 0 150 "\",\"" 40 WanCustomerRouterIP ""
41 SQLCHAR 0 150 "\",\"" 41 WanRouterIP ""
42 SQLCHAR 0 150 "\",\"" 42 WanRoutingProtocol ""
43 SQLCHAR 0 150 "\",\"" 43 LanMask ""
44 SQLCHAR 0 150 "\",\"" 44 LanRouterIP ""
45 SQLCHAR 0 150 "\",\"" 45 LanRoutingProtocol ""
46 SQLCHAR 0 150 "\"\r\n" 46 UsedHCP ""
BCP %SQLDB%.dbo.%SQLTable% IN %OUTPUTFILENAME% -f%BCPFormatFileName% -S%SQLServer% -U%SQLLogin% -P%SQLPW% -e%BCPErrorFileName% -m20 > %BCPLogFileName%
--Jeff Moden
March 7, 2006 at 1:33 pm
If the first column is not in "", how would the Format File reflect that?
March 7, 2006 at 4:09 pm
If the first column is unquoted data, then the first column BCP would actually import a column (instead of "0" as the target column #) and you would, of course, have to change the terminator to whatever terminator shows up immediately to the right of the field.
If the first column is not only unquoted, but is also a fixed field, the the "" (nothing) terminator would still be the one to use and the column that defines the size of the input would have to be set to the exact size of the field in the text file you are importing from.
--Jeff Moden
August 12, 2008 at 10:10 am
Hi Jeff,
The post is very useful. Am actually trying to perform something similar. Trying to export data to a csv file. I have a column which will hold html text which has commas within the text as well as " " s within text. Can you suggest me on how to handle this in bcp please. Here is sample text from the column:
If you observe this has a comma and double quotes. Currently am replacing commas with Ç and after extracting doing a find Ç and replace with ','
If I can handle this in a better way using format file, then can you please tell me if I can generate the format file from cmd.
I used following statement to generate format file with normal comma delimiter option.:
C:\>bcp saas_mdm.dbo.def_feature format nul -c -t -x -f ft.xml
-S sasdev -U sa -P Fon3H0me
Thanks in advance..
August 12, 2008 at 6:39 pm
Are you exporting any other columns with the HTML?
--Jeff Moden
August 12, 2008 at 8:07 pm
Though delimiter "," does its job in most cases it still can fail if someone will try to pass something like this:
Monitor 17","TCO'03" compliant.
For bullet proof solution you need to use Text ODBC driver.
_____________
Code for TallyGenerator
August 12, 2008 at 8:25 pm
True enough... but my big question would be, why would anyone store HTML in a database?
--Jeff Moden
August 13, 2008 at 1:59 am
Also, know your data. As long as the data is structured and delimited properly then many problems can be avoided. This sort of problem is normally caused by the wrong choice of delimiter (unavoidable in some cases).
That is why I now opt for TAB delimited, non quoted data. Although if it is quoted then I remove them post load. If I encounter data like Sergiy posted then I would preprocess the file before load anyway.
Far away is close at hand in the images of elsewhere.
Anon.
August 13, 2008 at 2:17 am
btw Jeff, I have tried your params and it worked :w00t:
Don't know why it did not before, but then it was a while ago, maybe I was too green then 😉
I did have to change the first line delimiter to "\""
to ignore the first quote correctly.
Far away is close at hand in the images of elsewhere.
Anon.
August 13, 2008 at 3:31 am
Yes, I wish to export some 10 columns, of which we have a column that contains HTML content.
I can take all the columns as enclosed in " " by concatenating " " in select statement. Only thing is how to generate a format file so that it takes this value ! 🙁
August 13, 2008 at 6:01 pm
I need to see a couple of lines of what you expect the output to be and the CREATE statement for the source table.
--Jeff Moden
August 25, 2008 at 3:34 pm
Jeff: Great post-even though this was 3 years ago. I am hoping you can help me with my bcp issue. My file looks like this
"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","ORD_TIME"
"MLMORD20140571","7499582HG","GVAUTO","",20080616,"02:21:08 PM"
"MLMORD20141711","7504735LS18","GVAUTO","",20080616,"11:17:13 PM"
It a double-quoted comma delimited text file. Some columns do not have the double-quotes, which means I have to use the bcp format file to load this data. Here is the format file I created:
9.0
7
1 SQLCHAR 0 1 "" 0 FirstQuote ""
2 SQLCHAR 0 255 "\",\"" 1 RECORD_ID ""
3 SQLCHAR 0 100 "\",\"" 2 SOURCE_ID ""
4 SQLCHAR 0 250 "\",\"" 3 MEDIA ""
5 SQLCHAR 0 30 "\",\"" 4 CREATED_BY ""
6 SQLCHAR 0 30 ",\" 5 DATE_ORD ""
7 SQLCHAR 0 25 "\"\r" 6 ORD_TIME ""
when I run bulk insert I am getting the error:
Cannot bulk load. Invalid destination table column number for source column 6 in the format file "g:\data\JOLMHDRFMT.TXT". I have verified that DATE_ORD does exist in the destination table. It must something to do with the way I created the format file for the date_ord field that does not have doubleqoutes around it.
Can you please help?
August 25, 2008 at 3:47 pm
Look at the delimiter you specified between CREATED_BY and DATE_ORD.
_____________
Code for TallyGenerator
August 25, 2008 at 10:54 pm
nimmi.smith (8/25/2008)
Jeff: Great post-even though this was 3 years ago. I am hoping you can help me with my bcp issue. My file looks like this
Yep... Sergiy is spot on about one of the problems... delimiter for Created_By shoud be "\"," ... the one for Date_Ord is missing a double quote... it should be ",\""
And thanks for the compliment.
--Jeff Moden
August 26, 2008 at 1:04 pm
Jeff: Thanks for the response.So now my format file looks like this:
9.0
7
1 SQLCHAR 0 1 "" 0 FirstQuote ""
2 SQLCHAR 0 255 "\",\"" 1 RECORD_ID ""
3 SQLCHAR 0 100 "\",\"" 2 SOURCE_ID ""
4 SQLCHAR 0 250 "\",\"" 3 MEDIA ""
5 SQLCHAR 0 30 "\"," 4 CREATED_BY ""
6 SQLCHAR 0 30 ",\"" 5 DATE_ORD ""
7 SQLCHAR 0 25 "\"\r" 6 ORD_TIME ""
The data file looks like this
"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","ORD_TIME"
"MLMORD20140571","7499582HG","GVAUTO","",20080616,"02:21:08 PM"
"MLMORD20141711","7504735LS18","GVAUTO","",20080616,"11:17:13 PM"
I am using the bulk insert to load the data
SET @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath + @Filename +
'" WITH (FORMATFILE = "g:\data\JOLMHDRFMT.TXT")'
exec (@query)
And lo and behold, I am getting this message, does anyone know what this means? Any help would be most appreciated, Thanks in advance:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
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)".
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply