SPECIFYING TEXT-QUALIFICATION IN BULK INSERT

Question

Post reply

SPECIFYING TEXT-QUALIFICATION IN BULK INSERT

Aaron Orrell

Mr or Mrs. 500

Points: 510
More actions
November 6, 2007 at 9:50 am

#62189

Hello,
Does anyone know if you can specify text qualifiers in a Format File or explicitly in BULK INSERT. It is not listed in the syntax for BULK INSERT and also not specified in the Format File help.
I was thinking maybe it is specified in the terminator (in the fmt or 'fieldterminator') as "{"},{"}".
The documentation indicates that the escape sequence can only be used for t,r,n,\,0. But I was able to use \" in the fmt file. the problem I am running into is that text-qualification is optional. If the value is null, then it is nothing--not ,"",.
Any help would be appreciated.
Kindest regards,
Aaron

Viewing 3 posts - 1 through 2 (of 2 total)

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

ziminski SSC Veteran Points: 221 More actions · Answer 1

BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }

[ WITH

(

[ BATCHSIZE [ = batch_size ] ]

[ [ , ] CHECK_CONSTRAINTS ]

[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]

[ [ , ] DATAFILETYPE [ =

{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]

[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]

[ [ , ] FIRSTROW [ = first_row ] ]

[ [ , ] FIRE_TRIGGERS ]

[ [ , ] FORMATFILE = 'format_file_path' ]

[ [ , ] KEEPIDENTITY ]

[ [ , ] KEEPNULLS ]

[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]

[ [ , ] LASTROW [ = last_row ] ]

[ [ , ] MAXERRORS [ = max_errors ] ]

[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]

[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]

[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]

[ [ , ] TABLOCK ]

)

]

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

Yes... it's \"... if you want to depict "," (quotes included), the delimiter would be identified as "\",\"" in the format file. But, beware... the first quote in the row of input is NOT a delimiter... you have to treat that as a single character column that has a column number of 0 (zero) so it doesn't get imported. For example...

[font="Courier New"]8.0

10

1 SQLCHAR 0 1 "\"" 0 FirstTerm ""

2 SQLCHAR 0 LLL "\",\"" NNN FirstField ""

3 SQLCHAR 0 LLL "\",\"" NNN SecondField ""

4 SQLCHAR 0 LLL "\",\"" NNN ThirdField ""

5 SQLCHAR 0 LLL "\",\"" NNN FourthField ""

6 SQLCHAR 0 LLL "\",\"" NNN FifthField ""

7 SQLCHAR 0 LLL "\",\"" NNN SixthField ""

8 SQLCHAR 0 LLL "\",\"" NNN SeventhField ""

9 SQLCHAR 0 LLL "\",\"" NNN EighthField ""

10 SQLCHAR 0 LLL "\"\r" NNN NinethField ""[/font]

Note that you must also sub field lengths for LLL and you must sub column numbers for NNN. Because this is a delimited file, you'd be wise to make the values for LLL bigger than what the table will allow which will "auto-magically" give you an error if the width for a field of the import line is larger than what the table was designed for.

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