import .BCP files into sql server

Question

import .BCP files into sql server

SSC Veteran

Points: 252

October 6, 2017 at 5:37 am

I have downloaded some ZIP files from a government website for study that are of type SQL Server Replication Snapshot Bulk-copy Data File. I am assuming they are .bcp files.

I have also downloaded corresponding .sql files to be created in sql server. There is an additional separate zip file that contains a large .bcp file with no create table .sql file so I am not sure what it is for.

They are saved in c:\sadybcp\extract1...extract2.... I have created a database called test with tables extract1, extract2, extract3 etc all tables being different. I want to import them to my sql server.

I searched unsuccessfully to get the syntax online , most of them are for .txt files. So I have tried various combinations but have failed.

For example I have tried

bcp Test.dbo.extract1 in :\sadybcp\extract.bcp –T

bcp "Test.dbo.extract1"in :\sadybcp\extract.bcp -T

but I get different errors like unknown argumentextract1 on command line
or Unable to open BCP host data-file.

Please can anyone help me with the correct command ? I need to copy 15 files

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Assuming you have unzipped the files and they are bcp files from a replication snapshot, using the native format usually works. Try something like:
bcp Test.dbo.extract1 in C:\sadybcp\extract.bcp -S ServerName -T -E -n -b 1000

Sue

mail_sady SSC Veteran Points: 252 More actions · Answer 2

Thanks Sue for you reply. Yes I have unzipped them and saved them in a folder sadybcp . I tried it your method but got an error

So I tried another file that had only int and varchar columns but I got another error

Am I doing something wrong here ?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

mail_sady - Friday, October 6, 2017 9:16 AM
Thanks Sue for you reply. Yes I have unzipped them and saved them in a folder sadybcp . I tried it your method but got an error
So I tried another file that had only int and varchar columns but I got another error
Am I doing something wrong here ?

It looks like it starts but it's something in the BCP file. I would guess that you are looking at the tables to determine they were int and varchar columns? You could try creating a format file using the tables and then use the format file in the bcp command.
Something like this would create a format file in the sadybcp folder for Extract1 with the name extract1.fmt
bcp Test.dbo.extract1 format nul -T -n -f C:\sadybcp\extract1.fmt

Then you reference that format file using -f and point to the format file:
bcp Test.dbo.extract1 in C:\sadybcp\extract1.bcp -S Servername -T -f C:\sadybcp\extract1.fmt

Sue

mail_sady SSC Veteran Points: 252 More actions · Answer 4

Hi Sue,
The first statement worked and the format file was created. But when I executed the 2nd statement I got the following error

Checked online and opened the format file in notepad ++ ----view tab--show symbols--show all characters . I got the following:

My table in the database looks like this.

***********************************************************************************************************************************************************************************************
I then tried another table , and again the format file was created but I got the following error while executing the 2nd statement.

This is what I got on opening the format file in notepad++

My table structure is

********************************************************************************************************************************************************************************************************
How can I proceed further ?

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Try importing with bcp Test.dbo.extract1 in C:\sadybcp\extract1.bcp -S Servername -T -E -n -f C:\sadybcp\extract1.fmt
On the first error, you only have one character column for string truncation so you probably need the column to be wider.
For the second error, that one can happen for a few reasons. I've usually seen it when I have a column out of order between the format file and the import file.
Being that the format files were created from the tables, it could be that those don't match the import files so you may want to check whatever documentation came with the download.

Sue

mail_sady SSC Veteran Points: 252 More actions · Answer 6

Sue,
I have not managed to sort this and unfortunately there is no documentation I can refer to. Maybe I will look at other sites to begin with that would have more information.
Thank you for all your help. I have managed to learn something out of this with your inputs.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Can you post a link as to where you got the files so we might do some experiments to help you, 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)

mail_sady SSC Veteran Points: 252 More actions · Answer 8

Hi Jeff,

Thanks for offer . The website is http://data.charitycommission.gov.uk/default.aspx . Over the weekend I had a look and got some information on the data definition at http://data.charitycommission.gov.uk/data-definition.aspx though I am not sure how I can use it.

-mail_sady

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Ah, my apologies. I lost track of this one.

Did you suss this or do you still need help?

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

Ok... just in case you haven't figured it all out...
1. There is no need for BCP here.
2. There is no need for a BCP format file here.
3. The second link provides all of the documentation needed.

Let's look at Item #3 above, first. In that link (http://data.charitycommission.gov.uk/data-definition.aspx). At the top of the page for that link, it clearly states...

This extract is of all registered and removed charities, the full requirement is specified elsewhere. There are 15 output files, each corresponding to a table in the extract. The files are BCP files (extension ‘.bcp’) which, due to embedded characters in the data, have the following delimiters:
@**@ for columns and
*@@* for rows.

In other words, there IS documentation on the files themselves. The files are nothing special. The ".BCP" extension may have thrown you off. The data files are nothing more than flat text files that are similar to ".CSV" files except that we need to use @**@ for the column delimiters instead of commas and *@@* instead of \ r \ n .

That brings us to point 2. Not only don't we need any BCP files but the ones you had were incorrect because you didn't identify the correct delimiters.

That brings us all the way to point 1. IF (and only if) the data files are stored on the drives that SQL Server has access to, we can get away with using BULK INSERT. If the files are on another machine and without using a bit of a complicated trick I use to bypass the problem of not having direct access to that other machine, then you'll probably need to use BCP with a UNC.

For now, I'll keep it simple. I created the "dbo.extract_ar_submit" table from the table-scripts download on the first link and stored the data in my local C:Temp folder on my laptop. Then, from the same database that I created the table in, I ran the following code.

BULK INSERT dbo.extract_ar_submit FROM 'C:\Temp\extract_ar_submit.bcp' WITH ( BATCHSIZE = 2000000000 ,CODEPAGE = 'RAW' ,DATAFILETYPE = 'char' ,FIELDTERMINATOR = '@**@' ,ROWTERMINATOR = '*@@*' ,TABLOCK ) ;

11 seconds later, I got the following message, which indicates success. A followup SELECT shows that the table was correctly loaded from the file.

(1615104 row(s) affected)

If you do need to use BCP instead of Bulk Insert, post back and we'll dunk that problem, as well.

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

mail_sady SSC Veteran Points: 252 More actions · Answer 11

Hi Jeff,

Gosh thanks a ton ! I had given up on this. I will try this out and let you know . And no , it was about getting the data and not about the method so bulk insert is fine.

mail_sady

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

mail_sady - Monday, November 6, 2017 1:07 AM
Hi Jeff,
Gosh thanks a ton ! I had given up on this. I will try this out and let you know . And no , it was about getting the data and not about the method so bulk insert is fine.
mail_sady

The only reason that I mentioned a difference is because BULK INSERT will use your credentials. If you call BCP via xp_CmdShell, it will use the server's credentials, which may carry further than yours do.

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

mail_sady SSC Veteran Points: 252 More actions · Answer 13

Hi Jeff,

Thanks for this, it was seamless except one table dbo.extract_overseas_expendthat had no corresponding .bcp file to extract from.

You mentioned using BCP with UNC if the files are on different servers. I will be grateful if you can take me through that process .

regards

mail_sady

DerrickXu Newbie Points: 1 More actions · Answer 14

Hi jeff, I exactly come across the same situation as the poster. I want to import the .bcp file into sql server. I borrowed your codes post here, but it reports an error that " CODEPAGE' is not supported on the 'Linux' platform." Hope you can have a look when having time.

Attachments:

You must be logged in to view attached files.