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.
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
12 People are following this question.
