BCP to insert data from text file to table

Question

BCP to insert data from text file to table

PJ_SQL

SSCertifiable

Points: 6703
More actions
April 1, 2016 at 1:34 pm

#309637

I have this file as below
'A',
'B',
'C',
I am using this bulk insert below:
BULK INSERT test..A FROM 'c:\test.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR='","',
ROWTERMINATOR = '')
But I am still getting 'A', in the table:
In the table I want
A
B
C

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You need to use a format file to identify the quotes as part of your delimiter. BCP won't remove the quotes for you otherwise.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

Luis Cazares (4/1/2016)
You need to use a format file to identify the quotes as part of your delimiter. BCP won't remove the quotes for you otherwise.

Or run an UPDATE query after importing, if you don't mind the overhead of hitting the table twice rather than once.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

PJ_SQL (4/1/2016)
I have this file as below
'A',
'B',
'C',
I am using this bulk insert below:
BULK INSERT test..A FROM 'c:\test.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR='","',
ROWTERMINATOR = '')
But I am still getting 'A', in the table:
In the table I want
A
B
C

Part of the problem is that you have a linefeed as a row terminator in addition to the comma. Luiz is mostly correct though because the actual delimiter is quote comma ^c_r quote where ^c_r is representative of whatever row terminator that you have. That does nothing for the leading quote nor the final quote comma.

Or, just use BCP/Bulk Insert to import the data as is an post process the data in a single pass update to remove the extraneous characters.

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Phil Parkin (4/1/2016)
Luis Cazares (4/1/2016)
You need to use a format file to identify the quotes as part of your delimiter. BCP won't remove the quotes for you otherwise.
Or run an UPDATE query after importing, if you don't mind the overhead of hitting the table twice rather than once.

And there's the issue on having to increase the columns' lenght by two to prevent truncation errors. In a staging table, of course.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2