question

cbc70037 avatar image
cbc70037 asked

Importing AdventureWorks CSV files to another DB

I'm in pursuit of a bigger sample dataset than the one I have so I'm looking at AdventureWorks. I have most of the tables handled, but some of them have records in a format that I have not seen before (I'm a non-DB developer). An example is the BusinessEntity table, with rows in the CSV as: 1+|0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50087+|2012-01-14 13:20:24.150&| This row contains the column values BusinessEntityID, rowguid, and ModifiedDate. I'm specifically curious about the plus signs and the ampersand. I'll have to write a bit of code to obviously remove these, but curious as to what they mean in the SQL Server world. Thanks.
csvimport
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
From the instawdb.sql file in the package download that loads the [AdventureWorks CS (OLTP)][1] data: BULK INSERT [Person].[BusinessEntity] FROM '$(SqlSamplesSourceDataPath)BusinessEntity.csv' WITH ( CHECK_CONSTRAINTS, CODEPAGE='ACP', DATAFILETYPE='widechar', FIELDTERMINATOR='+|', ROWTERMINATOR='&|\n', KEEPIDENTITY, TABLOCK ); This tells me that the '+|' (Plus, Pipe) is being used as the field terminator, hence it's appearance between the fields, and '&|\n' (Ampersand, pipe, newline) is used as the row terminator, so it is at the end of each row. It is also worth noting there are no field headers; if you need these they are also defined in the same script file as a CREATE TABLE statement for each table. Depending on how you import this data, you shouldn't need to remove the plus or ampersand characters if you specify them similarly in your import script/program. [1]: http://msftdbprodsamples.codeplex.com/releases/view/55330
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.