SQL script to import data from .txt table using BCP

Question

SQL script to import data from .txt table using BCP

kingst7

Old Hand

Points: 349
More actions
September 18, 2008 at 9:35 pm

#193131

I need to write a SQL script to import data from .txt tab delimited file to a table in SQL Server, it should also account for error handling, could anyone provide me the script.
I appreciate your help.
Thanks:)

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

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Heh... absolutely... we need a copy of the file, a copy of the record layout, and copy of the CREATE statement for the target table, and any gotcha's that you may know of.

Or, you could lookup Bulk Insert in Books Online (very simple method) or you could teach yourself how to use SSIS. 😉

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

kingst7 Old Hand Points: 349 More actions · Answer 2

Hey Jeff, I have attached the script for table creation and also the .txt file which is to be imported into table (SQL SERVER 2000) using BCP command only and the sproc should also account for errors.

I appreciate if you can help me out with the code 😉

Thanks

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

With error checking... broad subject... lemme start by asking what you want to do with the duplicate primay keys included in your sample file?

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

kingst7 Old Hand Points: 349 More actions · Answer 4

Hey Jeff, all I want is to transfer data from the .txt file to a table and when I talk about error handling, its only to check whether all the records were succesfully transferred to the table or not, thats it so don't take into consideration about the no of primary keys, all I need is the data transfer.

Thanks for your reply;)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Ok... no error checking except for total number of rows. That also means, no primary key in this instance. You have to remove the primary key from the table you provided the script for. So, your table would look like this, then...

DROP TABLE AVMR_NUMS

GO

CREATE TABLE AVMR_NUMS

(

lvmr_uid char (11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

lvm_type char (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvm_name1 varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvm_name2 varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvmeer_address1 varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvmeer_address2 varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvme_name varchar (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvmes_cd char (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

lvm_cd varchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

phone varchar (10)

)

GO

If the TextFile.txt file is located in C:\Temp, then the code to import it into the table would be this...

BULK INSERT dbo.AVMR_NUMS

FROM 'C:\Temp\TextFile.txt'

WITH (

DATAFILETYPE = 'CHAR',

FIELDTERMINATOR = '\t',

ROWTERMINATOR = '',

MAXERRORS = 0,

TABLOCK

)

You do realize that you actually do have some errors in the text file that need detection/correction, right?

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

kingst7 Old Hand Points: 349 More actions · Answer 6

Hi Jeff, I am sorry to say that this code would not serve my purpose, as I have to accomplish this by writing a stored procedure and it should make use of BCP utility (Bulk Copy Program) in the stored procedure, and not bulk insert. If you can help me with the stored proc that would be great.

Thanks for your reply,

Appreciate your help,

Byee, Jack

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

Exact same principles apply... but, now the requirements sound like homework. Recommend you take a look at Books Online and do your own homework... 😉

Good luck.

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

kingst7 Old Hand Points: 349 More actions · Answer 8

It was not my homework dude I was working for someone else, by the way I am impressed by your valuable advice, hope you get the same kind of reply some day.

Anyways he cracked it on its own.

Take rest at home;)

Byee, Jack

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

At least tell your buddy that there's errors in the data that he might not be aware of... dude... 😉

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

kingst7 Old Hand Points: 349 More actions · Answer 10

Hey Jeff, thanks for your help, I appreciate it.

Regards,

Jack

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 11

my file is like that.

PID SPECIALTY PNAME PLIC UPIN

010199 MED ABC KENNETH MD03424E E40690

I got this error.

Server: Msg 4866, Level 17, State 66, Line 1

Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].

The statement has been terminated.

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

Frances L (6/17/2009)
my file is like that.
PID SPECIALTY PNAME PLIC UPIN
010199 MED ABC KENNETH MD03424E E40690
I got this error.
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

The problem with that file is that the header doesn't have the same number of delimiters as the first data row. You can't just tell it to skip that row with the FIRSTROW parameter either. That's one of the bad parts about BCP and BULK INSERT. Even the rows you skip must have the same number of delimiters as the rows you don't.

--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: 1004365 More actions · Answer 13

Ummmm.... there is a way around this in T-SQL but let me ask a couple of questions....

(1) How many delimiters are there supposed to be?

(2) What are the delimiters? Spaces or Tabs?

(3) If spaces, then one of the columns of data is supposed to have spaces in it. For clear understanding on my part, please replace the spaces that are supposed to be delimiters with a comma so I can see what the column data is.

(4) Also, along with (3), will the column that has spaces in it always have one and only one space?

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

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 14

It is fix width file and suppose to have six fields to import. the first column would be field name.

see attached test file.

PID SPECIALTY PNAME PLIC UPIN

010199 MED ABC KENNETH MD03424E E40690

010546 MED MC WILLIAM MD0268L B32995

010561 MED LAM E GARY MD0255 B41920

010587 MED PRA SAMUL P OS0090L G81415

Thx.