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.
| 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. |
|
|
|
|
|
|
|
|
sql bcp tool.