Importing data using bcp

Question

Importing data using bcp

EMtwo

Hall of Fame

Points: 3498
More actions
February 1, 2017 at 10:53 am

#318263

Does anyone know how I can import data from a csv file(pipe delimited) into a sql server database table using bcp client?
Any help will be appreciated,
Thanks.

Viewing 15 posts - 1 through 15 (of 17 total)

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

drew.allen SSC Guru Points: 76944 More actions · Answer 1

The documentation on BCP is very good and BCP contains too many options for us to give you an intelligent response with so little information. I suggest that you Google bcp and try it, and then come back here if you run into problems.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

EMtwo Hall of Fame Points: 3498 More actions · Answer 2

Thank you Drew. Your comment helps me narrow my question.

I think the most difficult part is asking the right question. Hopefully I do to get the answer I'm looking for.

What is the bcp command I need to generate the format file for importing a pipe delimited data?

Thanks in advance.

drew.allen SSC Guru Points: 76944 More actions · Answer 3

The documentation has an example on how to create a format file. You'll also need to use the -t option to change the delimiter to a pipe (from the default tab).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

EMtwo Hall of Fame Points: 3498 More actions · Answer 4

Got it. The documentation the Sqlservercentral.com. Thanks so much. I will look at it now and post any question I may have. Thanks again.

EMtwo Hall of Fame Points: 3498 More actions · Answer 5

If it's not too much to ask, do you mind dropping the link to the documentation here? I'm trying to navigate my way to where the documentation is, but I'm not sure. Thanks in advance.

EMtwo Hall of Fame Points: 3498 More actions · Answer 6

Not to worry; I think I found the information i'm looking for. Thank you!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

You might want to take a look at this article.
http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

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

EMtwo Hall of Fame Points: 3498 More actions · Answer 8

So, I was able to create my format file successfully. However, when I test the process of extracting data from a table using the format file I get the following error:

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

Any help with this will be highly appreciated....

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

smatu - Friday, February 3, 2017 12:43 PM
So, I was able to create my format file successfully. However, when I test the process of extracting data from a table using the format file I get the following error:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
Any help with this will be highly appreciated....

Does SQL Server has access to the file? Are you using a path according to the server and not the local machine?

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

EMtwo Hall of Fame Points: 3498 More actions · Answer 10

EMtwo

Hall of Fame

Points: 3498

February 3, 2017 at 12:55 pm

#1926693

I'm not sure I understand your question....

Luis Cazares SSC Guru Points: 183698 More actions · Answer 11

smatu - Friday, February 3, 2017 12:55 PM
I'm not sure I understand your question....

How are you calling bcp?

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

We've been guessing long enough. Please post your unadulterated BCP command with the obvious exception of obfuscating ServerName, Login, and Password. Also, for imports, BCP may be the wrong tool. It may well be better to use BULK INSERT but depends on where the file being imported actually is. And you likely DON'T need a BCP Format file for either of these because you can specify the column separator in the code.

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

phil.doensen Mr or Mrs. 500 Points: 501 More actions · Answer 13

Jeff Moden - Friday, February 3, 2017 3:56 PM
We've been guessing long enough. Please post your unadulterated BCP command with the obvious exception of obfuscating ServerName, Login, and Password. Also, for imports, BCP may be the wrong tool. It may well be better to use BULK INSERT but depends on where the file being imported actually is. And you likely DON'T need a BCP Format file for either of these because you can specify the column separator in the code.

I agree.
Whenever working with CSV files and file formats, I have always found OPENROWSET BULK much more reliable and much more flexible.
https://msdn.microsoft.com/en-us/library/ms190312.aspx

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

phil.doensen - Friday, February 3, 2017 9:30 PM
Jeff Moden - Friday, February 3, 2017 3:56 PM
We've been guessing long enough. Please post your unadulterated BCP command with the obvious exception of obfuscating ServerName, Login, and Password. Also, for imports, BCP may be the wrong tool. It may well be better to use BULK INSERT but depends on where the file being imported actually is. And you likely DON'T need a BCP Format file for either of these because you can specify the column separator in the code.
I agree.
Whenever working with CSV files and file formats, I have always found OPENROWSET BULK much more reliable and much more flexible.
https://msdn.microsoft.com/en-us/library/ms190312.aspx

I'll have to disagree in the reliability thing when it comes to OPENROWSET vs BULK INSERT vs BCP. All 3 are extremely reliable and flexible depending on what you're using them for and how you're using them.

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