BULK INSERT

Question

Post reply

BULK INSERT

David Kranes

SSCrazy

Points: 2523
More actions
October 31, 2006 at 1:31 pm

#173345

I am trying to bulk insert the following file into a table in my db:
retailer_id|sku_id|outlet_id|threshold|qty|sku_price|tax_rate|sell_thru|inv_status_1|inv_status_2|inv_status_3|inv_status_4|inv_status_5|rec_update_date|rec_create_date|rec_update_id
102|000001216175|09000|2|2|3800||999.99999||||||||
102|401000970643|09000|2|4|3800||999.99999||||||||
102|401001259259|09000|2|1|5400||999.99999||||||||
102|401001259716|09000|2|1|5400||999.99999||||||||
102|401001514136|09000|2|2|6200||999.99999||||||||
How would I go about doing this? Do I need to specify the row and line terminators and firstrow. What would the bulk insert command look like? Thank you,
David
Best Regards,
~David

Viewing 13 posts - 1 through 12 (of 12 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Have you tried Books online?

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Do you have a specific question, issue?

Edit:

http://www.sqlteam.com/item.asp?ItemID=3207

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 2

BOL example

BULK INSERT Northwind.dbo.[Order Details]   FROM 'f:\orders\lineitem.tbl'   WITH       (         FIELDTERMINATOR = '|',         ROWTERMINATOR = '|\n',

 FIRSTROW = 2 --skip the first decription row

Kindest Regards,

Vasc

David Kranes SSCrazy Points: 2523 More actions · Answer 3

If I use your example, would that work on my file example. What would the row terminator in the file need to look like? Right now there is now row terminator. Do have am example that would load this exact file.

102|000001216175|09000|2|2|3800||999.99999||||||||

102|401000970643|09000|2|4|3800||999.99999||||||||

102|401001259259|09000|2|1|5400||999.99999||||||||

102|401001259716|09000|2|1|5400||999.99999||||||||

102|401001514136|09000|2|2|6200||999.99999||||||||

If I use the following:

BULK INSERT esell.outlet_sku_xref

FROM 'C:\Chicos\EnterpriseSelling\osx.ref'

WITH

(

FIELDTERMINATOR =' |',

FIRSTROW=2

)

I get

(0 rows affected)

Your help is much appreciated.

David

Best Regards,

~David

Ray M SSC-Insane Points: 21093 More actions · Answer 4

For your example the # of columns in the table need to exactly match # of columns in the file.

If your file looks as you have posted, then there is most definately a row terminator.

It is a carriage return.

so you should include this line

ROWTERMINATOR = '|\n'

Give it a try.

Give a man a fish and he is fed for a day, Teach a man to fish and he is fed for a lifetime

David Kranes SSCrazy Points: 2523 More actions · Answer 5

This is what I get:

The table definition is:

CREATE TABLE [esell].[outlet_sku_xref] (

[retailer_id] [int] NOT NULL ,

[sku_id] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[outlet_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[threshold] [int] NULL ,

[qty] [int] NULL ,

[sku_price] [int] NULL ,

[tax_rate] [numeric](5, 3) NULL ,

[sell_thru] [numeric](8, 3) NULL ,

[inv_status_1] [int] NULL ,

[inv_status_2] [int] NULL ,

[inv_status_3] [int] NULL ,

[inv_status_4] [int] NULL ,

[inv_status_5] [int] NULL ,

[rec_update_date] [datetime] NULL ,

[rec_create_date] [datetime] NULL ,

[rec_update_id] [int] NULL

) ON [PRIMARY]

GO

Here is the data file:

102|000001216175|09000|2|2|3800||999.99999||||||||

102|401000970643|09000|2|4|3800||999.99999||||||||

102|401001259259|09000|2|1|5400||999.99999||||||||

102|401001259716|09000|2|1|5400||999.99999||||||||

102|401001514136|09000|2|2|6200||999.99999||||||||

I issue the following bulk insert command:

1> BULK INSERT [outlet_sku_xref]

2> FROM 'C:\Chicos\EnterpriseSelling\osx.ref'

3> WITH

4> (

5> FIELDTERMINATOR =' |',

6> ROWTERMINATOR = '|\n'

7> )

8>go

I get the following message:

Msg 4832, Level 16, State 1, Server CACVMESRMW2K01, Line 1

Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.

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

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

information about the error.

The statement has been terminated.

Any ideas???

David

Best Regards,

~David

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

David,

Change the statement to look like this...

BULK INSERT esell.outlet_sku_xref

FROM 'C:\Chicos\EnterpriseSelling\osx.ref'

WITH

(

FIELDTERMINATOR ='|',

ROWTERMINATOR ='\n', ---------\n is a newline character

FIRSTROW=2

)

if it still not working then replace \n with \r in ROWTERMINATOR

--Ramesh

David Kranes SSCrazy Points: 2523 More actions · Answer 7

Ramesh, it worked!!! Thank you very much!

David

Best Regards,

~David

surya-434952 SSC Eights! Points: 952 More actions · Answer 8

I would like to explain my scenario first. i have a .csv file 836 rows in which some columns may not have values when i wrote an bulk insert statement with filedtermintor ',' and rowtermintor'\n' it giving 468 rows only. after a keen observation i found that last column is getting the values of subsequent columns. I think its issue of rowtermination.

can any body help me how many rowterminators are availble

I tried rowterminator \r also its syntax error plz verify the field terminator or rowterminator.

how to know row terminator of .csv file.

thanks in advance

surya

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

Surya,

You need to look at the file with a hex editor (maybe like TextPad in the Binary mode) and you need to know a bit about the ASCII character set.

0A = New Line (Line Feed or /n)

0D = Carriage Return (\r)

09 = Tab (\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: 1004412 More actions · Answer 10

On a slightly different subject, it saddens me to see yet another MCP/MCTS that doesn't know this type of stuff nor how to find the answers in Books Online. What are they teaching in these cert courses, nowadays?

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

surya-434952 SSC Eights! Points: 952 More actions · Answer 11

HI modem,

I am very much thankful to you

surya

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

You're welcome... thank you for the feedback...

--Jeff Moden

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