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 |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2010-11-18 : 03:15:09
|
| Is it possible to import from a csv like this?1. Using a format file.2. Starting from Line 3 in the csv (there is crap in first two rows and headers are in row 3).3. Number of columns in table differ from those in the csv.4. Order of columns in table are different from those in csv (eg, col 1 of table is DOB, whilst DOB in csv is in col 9).If so, can anyone explain how, I've tried various ways and still can't seem to get it working!Or any suggestions for another method that will work around this scenario.Thanks |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-11-22 : 18:53:17
|
1. Yes ..see BCP utility for creating a format file, from command prompt it would be in this syntax (http://msdn.microsoft.com/en-us/library/ms162802.aspx)2. Yes ..set the first row to 4, but this requires proper row delimiter..the property is not designed to simply skip headers. (FROM BOL- The FIRSTROW attribute is not intended to skip column headers. Skipping headers is not supported by the BULK INSERT statement. When skipping rows, the SQL Server Database Engine looks only at the field terminators, and does not validate the data in the fields of skipped rows. )3/4 You can skip/re-order columns into the destination similar to the below for a four row table, where you want to skip the 2nd column and the destination is in a different order.10.041 SQLCHAR 0 3 "," 4 SQLTableCol4 SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 1 "," 0 FieldSkipped SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 10 "," 1 SQLTableCol1 SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 10 "\r\n" 3 SQLTableCOl3 SQL_Latin1_General_CP1_CI_ASBCP is really easy to use, but in your case, create the file and then edit to change your needs.Alternatively, you could simply import the file into a staging table, then use T-SQL to Insert Into/Select From to do your column reorganization. You can also use T-SQL to do the bulk insert using the format file if needed.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
|
|
Import using Format File