| Author |
Topic |
|
GhantaBro
Posting Yak Master
215 Posts |
 Posted - 2009-07-13 : 12:29:00
|
| Hey Guys,I am using bulk-insert command to stage flat files delmited with comma or pipe with quotes as text qualifier... the problem i sthe text qualifier.... is there anything that I can use in bulk-insert as the parameter that can ignore those quotes when staging the data? I am sure lot of people have been asking this... sorry. |
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-13 : 12:36:23
|
| Guys this will be automated process and we are not using SSIS or DTS for staging the file. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-13 : 13:37:11
|
| cant you use format file for specifying delimiters?http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-13 : 14:34:01
|
| Problem is we cannot create format file that easily as every file can have different number of fields in it. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-13 : 17:37:20
|
| First row has column names. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-13 : 17:39:28
|
| KeyCode, MemAcctNum, NATitle, NName205509,0505488528,,"Rip, Winkle"205501,0505488111,,"Tiger, Scott"Sample data.Quotes only for fields that has embedded comma. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-07-13 : 22:18:41
|
| Search for FnParseLists Function. It will solve your Problem. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-13 : 23:36:59
|
| Thanks Sodeep nice function, but I think using "," as the column delimiter in bulk insert and doing select into another table and using replace or substring to get rid off " from first and last fields would do the trick. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-14 : 13:05:23
|
| Hard to deal with Quotes only for those fields that has embedded comma... sample above. anyone with such experience pls. |
|
|
|
GhantaBro
Posting Yak Master
215 Posts |
Posted - 2009-07-14 : 16:08:21
|
| Has anyone use LogParser to import "true CSV"? |
|
|
|
|
Staging Flat File with Quotes as Text Qualifier