SQL Bulk Insert - using " to wrap text fields

Question

SQL Bulk Insert - using " to wrap text fields

Viewing 15 posts - 16 through 30 (of 42 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kevin R Hurst Mr or Mrs. 500 Points: 522 More actions · Answer 2

If the first column is not in "", how would the Format File reflect that?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bhargavabs Mr or Mrs. 500 Points: 544 More actions · Answer 4

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..

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Are you exporting any other columns with the HTML?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

True enough... but my big question would be, why would anyone store HTML in a database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65143 More actions · Answer 8

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.

David Burrows SSC Guru Points: 65143 More actions · Answer 9

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.

bhargavabs Mr or Mrs. 500 Points: 544 More actions · Answer 10

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 ! 🙁

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 12

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?

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Look at the delimiter you specified between CREATED_BY and DATE_ORD.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nimmi.smith-624103 SSC Eights! Points: 876 More actions · Answer 15

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)".