SQL Bulk Insert - using " to wrap text fields

Question

SQL Bulk Insert - using " to wrap text fields

Viewing 13 posts - 31 through 42 (of 42 total)

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

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

Instead of using EXEC... use PRINT(@Query) and post the result, please.

--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: 65138 More actions · Answer 2

Change the terminator on the last line to

"\"\r\n"

If your data contains the header row you will need

FIRSTROW=2

to ignore it

If you wish to keep the header then note that DATE_ORD is quote delimted in the header and not in the data.

Far away is close at hand in the images of elsewhere.
Anon.

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

Jeff:

results from the print statement:

BULK INSERT SALES.GBDS_JOLM_ORDHDR FROM "g:\DATA\BCPTEST.csv" WITH (FORMATFILE = "g:\data\JOLMHDRFMT.TXT")

David: where do you put FIRSTROW=2 to ignore the first row in the format file?

after 9.0 ? Would it be like this?

Thx

9.0

FIRSTROW =2

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

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

Thank you all, I figured out where FIRSTROW is supposed to in.

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

David/Jeff-anyone out there:

So, if the file looks like this

"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","AMT","ORD_TIME"

"MLMORD20140571","7499582HG","GVAUTO","NANCY",20080616,100.00,"02:21:08 PM"

"MLMORD20141711","7504735LS18","GVAUTO","BILL",20080616,100.00,"11:17:13 PM"

Now, I have two numeric fields DATE_ORD,AMT next to each other, how do I specify it in the format file? I am getting an error using this format file. Can someone please tell me what I am specifying wrong here? Thanks

9.0

8

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 30 ",\"" 6 AMT ""

8 SQLCHAR 0 25 "\"\r" 7 ORD_TIME ""

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

I am real close to finding my solution. Just need a little help:

Now my format file looks like this:

9.0

8

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 30 "," 6 AMT ""

8 SQLCHAR 0 25 "\"\r" 7 ORD_TIME ""

Here is my data file:

"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","AMT","ORD_TIME"

"MLMORD20140571","7499582HG","GVAUTO","NANCY",20080616,100.00,"02:21:08 PM"

"MLMORD20141711","7504735LS18","GVAUTO","BILL",20080616,100.00,"11:17:13 PM"

The results I am getting is picking up the first double quote from the ord_time field. Can someone please tell me what I am doing wrong? Thx

MLMORD201405717499582HGGVAUTONANCY20080616100"02:21:08 PM

MLMORD201417117504735LS18GVAUTOBILL20080616100"11:17:13 PM

Sergiy SSC Guru Points: 110206 More actions · Answer 7

7 SQLCHAR 0 30 ",\"" 6 AMT ""

_____________
Code for TallyGenerator

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

The data provider threw ina new kink, now my data looks like this:

"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","AMT","F_NAME","ORD_TIME"

"MLMORD20140571","7499582HG","GVAUTO","NANCY",20080616,100.00,"WALLY","02:21:08 PM"

"MLMORD20141711","7504735LS18","GVAUTO","BILL",20080616,100.00,"ROB""11:17:13 PM"

So, I created this format file:

9.0

8

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 30 "," 6 AMT ""

8 SQLCHAR 0 30 "\",\"" 7 F_NAME ""

9 SQLCHAR 0 25 "\"\r" 8 ORD_TIME ""

the results I am getting is weird:

RD_TIME" "MLMORD201405717499582HGGVAUTONANCY20080616100"WALLYNULL

2:21:08 PM" "MLMORD201417117504735LS18GVAUTOBILL20080616100"ROB""11:17:13 PM" NULL

I used this to run it:

BULK INSERT HDRTEST

FROM "G:\DATA\BCPTEST.csv "

WITH

( FIRSTROW =2 ,

FORMATFILE = 'G:\DATA\JOLMHDRFMT.FMT',

MAXERRORS = 0

)

So, the header should not have loaded. But I do see RD_TIME" "MLMORD20140571 in my record_id field.

I desperately need this to work. Can some one please help my befuddled mind??

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

Excuse my stupidity.

I see that in my last post the data file had a comma missing between a field.

Here is my file:

"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","AMT","F_NAME","ORD_TIME"

"MLMORD20140571","7499582HG","GVAUTO","NANCY",20080616,100.00,"WALLY","02:21:08 PM"

"MLMORD20141711","7504735LS18","GVAUTO","BILL",20080616,100.00,"ROB","11:17:13 PM"

Here is my format file

9.0

9

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 30 "," 6 AMT ""

8 SQLCHAR 0 30 "\",\"" 7 F_NAME ""

9 SQLCHAR 0 25 "\"\r" 8 ORD_TIME ""

I used this to load:

BULK INSERT HDRTEST

FROM "G:\DATA\BCPTEST.csv "

WITH

( FIRSTROW =1 ,

FORMATFILE = 'G:\DATA\JOLMHDRFMT.FMT',

MAXERRORS = 0

)

go

Now I am getting this 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 1, column 7 (AMT).

column7 is amt and it is numeric so the format "," is not correct? What should it be?

Thx

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Any reasonable explanation why are you trying to upload column headers?

WITH

( FIRSTROW = 2,

_____________
Code for TallyGenerator

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

nimmi.smith (8/27/2008)
Excuse my stupidity.
I see that in my last post the data file had a comma missing between a field.
Here is my file:
"RECORD_ID","SOURCE_ID","MEDIA","CREATED_BY","DATE_ORD","AMT","F_NAME","ORD_TIME"
"MLMORD20140571","7499582HG","GVAUTO","NANCY",20080616,100.00,"WALLY","02:21:08 PM"
"MLMORD20141711","7504735LS18","GVAUTO","BILL",20080616,100.00,"ROB","11:17:13 PM"
Here is my format file
9.0
9
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 30 "," 6 AMT ""
8 SQLCHAR 0 30 "\",\"" 7 F_NAME ""
9 SQLCHAR 0 25 "\"\r" 8 ORD_TIME ""
I used this to load:
BULK INSERT HDRTEST
FROM "G:\DATA\BCPTEST.csv "
WITH
( FIRSTROW =1 ,
FORMATFILE = 'G:\DATA\JOLMHDRFMT.FMT',
MAXERRORS = 0
)
go
Now I am getting this 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 1, column 7 (AMT).
column7 is amt and it is numeric so the format "," is not correct? What should it be?
Thx

The delimiters in the BCP file should be for the delimiter AFTER the column... slow down and look...

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

can someone please help me? What am I doing wrong?

My data file used to look like this

...."GVI PRINT AD DEF","","","",""

with the format file:

63 SQLCHAR 0 30 "\",\"" 62 PROJECT ""

64 SQLCHAR 0 50 "\",\"" 63 FILLER20 ""

65 SQLCHAR 0 50 "\",\"" 64 FILLER21 ""

66 SQLCHAR 0 50 "\",\"" 65 FILLER22 ""

67 SQLCHAR 0 50 "\"\r" 66 FILLER23 ""

I was able to load the file just fine.

Now the vendor added a new field at the end of the file-which is a numric field. Now the data looks like this:

...."GVI PRINT AD DEF","","","","",20229808

I modified my format file:

63 SQLCHAR 0 30 "\",\"" 62 PROJECT ""

64 SQLCHAR 0 50 "\",\"" 63 FILLER20 ""

65 SQLCHAR 0 50 "\",\"" 64 FILLER21 ""

66 SQLCHAR 0 50 "\",\"" 65 FILLER22 ""

67 SQLCHAR 0 50 "\"," 66 FILLER23 ""

68 SQLCHAR 0 50 ","\r" 67 ORDER_NO ""

I am now getting an error:

Msg 4832, Level 16, State 1, Line 1

Bulk load: An unexpected end of file was encountered in the data file.

I have tried

63 SQLCHAR 0 30 "\",\"" 62 PROJECT ""

64 SQLCHAR 0 50 "\",\"" 63 FILLER20 ""

65 SQLCHAR 0 50 "\",\"" 64 FILLER21 ""

66 SQLCHAR 0 50 "\",\"" 65 FILLER22 ""

67 SQLCHAR 0 50 "\"," 66 FILLER23 ""

68 SQLCHAR 0 50 "\r" 67 ORDER_NO ""

and am still getting the same error. HELP!!!

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

My bad the last statement does contain \r:

68 SQLCHAR 0 50 "\r" 67 ORDER_NO ""