BULK INSERT

[Guest_imported]

Is it possible to specify TEXT DELIMITER using BULK INSERT command?

 

[rajeevnandanmishra]

Hi there,
following is the syntax for bulk insert
------------------------------
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 ]
)
]
------------------------------
And you can specify the field terminator (basically the delimeter)

 

[Bernadette]

Hi There

It doesnt have a TEXT DELIMITER as such but it has a FIELD DELIMITER which specifies the field terminator to be used for char and widechar data files. The default is \t (tab character).


Example
========
BULK INSERT Northwind.dbo.[Order Details]

FROM 'f:\orders\lineitem.tbl'

WITH

(

FIELDTERMINATOR = '|',

ROWTERMINATOR = '|\n'

)



Hope This Helps Bernadette

 

[Guest_imported]

Hi Rajeevnandanmishra

I'd like to specify the special character delimiting text.
That's different from FIELD TERMINATOR and ROW TERMINATOR.
I've done it using DTS. I'm wandering how to complete it using BULK INSERT command.
Thanks

 

[rajeevnandanmishra]

Hi rog987,

What do you mean by the special character.
do mean like
!
@
#
$
%
^
mySpecialTextValue (--- or /// or /|\ or ..)


You can specify any of these in the fieldteminator option.

Please let me know what special character you have in your text file.

 

[Guest_imported]

Rajeevnandanmishra

Special character is @ (text delimiter)

; is the field terminator

thanks