sql bcp tool. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

sql bcp tool.

Author

Topic

paulanthony
Starting Member

2 Posts

Posted - 2008-04-23 : 08:31:22

Hi guys, Im trying to use Bulk copy to transform a csv file into a table

BULK INSERT [BULK_INSERT_TEST]
FROM 'c:\stock.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n\N'

)

--*STOCK LOOKS LIKE THIS
stockcode,barcode,qty
10002,"10002087",0

TABLE Looks like this

id, stock,barcode, qty

can anyone give any guidance on performing this - at present this doesn't seem to work. Worth mentioning id is autoincrementing. I would also need to strip quotes around the second field. Note. Stock file has a header - do I need to strip this to use BCP?

Any help appreciated.

2,10002,"10002087",0

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-23 : 08:47:10

To get rid of the header use firstrow=2.
To get rid of the quotes is a bit more complcated and needs a format file. It's a lot simpler to import into a staging table then parse the string.
You can avoid the identity by creating a view without it and importing into that or by using the format file.

For the format file and allowing he identity to increment see
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

paulanthony
Starting Member

2 Posts

Posted - 2008-04-23 : 09:10:55

Nigel, many thanks for that having removed the identity column I still get bad data in the table

I've tried a simple..

BULK INSERT [BULK_INSERT_TEST]
FROM 'c:\stock.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2,
ROWTERMINATOR = '\n\N'
)

and I get data into my table.. However its as if the rowterminator is wrong..the whole file goes in on qty.

i.e. only one row (should be two)...qty holds the below string...

"10002087",02,10002,"10002087",0

[
1,10002, "10002087",0,1002
2,10002, "10002087",0

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-23 : 22:50:07

Then you have to find out what the row terminator is in data file.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-07 : 12:12:57

try char(13) or char(10).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources