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 tableBULK INSERT [BULK_INSERT_TEST] FROM 'c:\stock.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n\N' )--*STOCK LOOKS LIKE THISstockcode,barcode,qty10002,"10002087",0TABLE Looks like thisid, stock,barcode, qtycan 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 seehttp://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 tableI'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,10022,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. |
 |
|
|
|
|
|
|