Author |
Topic |
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 13:19:45
|
Yet Another CSV Problem Using BCP...I've got a 10,000 row CSV file from a customer. Comma delimited. Pretty good form.Whatever package wrote this has quoted strings whenever the string has a blank. This is throwing off BCP for me.Is there a way to instruct BCP to honor quotes when they're encountered, maybe through a command line option or through bcp.fmt?Sam |
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 13:49:42
|
Don't know much about BCP, but what abou a Format File?Kristen |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 14:06:35
|
Seems format file allows specification of the delimiter. I've used commas and tildes before.There must be some way to instruct it to honor quotation delimiters, but I've never seen a switch that specified quote marks "when found".Anyway, I've stripped the quote marks out of the data. On to the next BCP problem. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 14:31:10
|
I hate CSV. Almost nothing does it properly:Comma delimited - that bit is OK!If it contains a COMMA put quotes around it - OKIf it also contains QUOTES then double up the QUOTES. Uh-Oh! That's going to be trickey if the app./BCP doesn't inherently deal with it.Have to say that when I'm pulling "random" data into a DB I use DTS with Source set to the "Text file" driver/thingamajigKristen |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 14:35:14
|
If BCP can be setup to import a file meeting the requirements you just listed, I'd like to know how to do it. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-19 : 14:42:32
|
Well I don't know the answer to that one, but I just tried a TEXT file with:1,"222","33,333","44,""444"in it using DTS and it was fine (even the double quote got sorted out)Any good as a solution?Kristen |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-19 : 15:15:24
|
I've got an investment in BCP. It would be a crunch to move to DTS. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-19 : 15:56:00
|
quote: Originally posted by SamC If BCP can be setup to import a file meeting the requirements you just listed, I'd like to know how to do it.
SamTAB is the best delimiter in my opinion...But I also like fixed width...but coming from EXCEL, fixed width is VERY unreliable...as a repeatable production process...BCP RulesBrett8-) |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-19 : 16:12:10
|
And yes the text identifiers were/are a pain...I thought I figured that out though...I mean...hell even excel does itUSE NorthwindGOCREATE TABLE myTable99(Col1 varchar(8000))CREATE TABLE myTable00(Col1 varchar(10),Col2 varchar(10),Col3 varchar(10))GOINSERT INTO myTable99(Col1)SELECT '"Col1","Col2","Col3"' UNION ALLSELECT '"Cola","Colb","Colc"' UNION ALLSELECT '"Colx","Coly","Colz"' UNION ALLSELECT '"Col7","Col8","Col9"'GOSELECT * FROM myTable99master..xp_cmdshell 'bcp Northwind.dbo.myTable99 OUT c:\test.dat -S -Usa -P -c -t","'GOmaster..xp_cmdshell 'bcp Northwind.dbo.myTable00 IN c:\test.dat -S -Usa -P -c -t","'GOSELECT * FROM myTable00DROP TABLE myTable99DROP TABLE myTable00GO Brett8-) |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 01:12:32
|
Won't deal with a doubled-up double-quote within a field though, will it?(But Sam may not have that problem, but not all his columns are quoted)Sam, maybe you have to run the file through a pre-procesor to get it consistently delimited?Its not a one-off job then?Kristen |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-20 : 09:06:42
|
My preprocessor is Windows Notepad, and it's already done. BCP included the quote marks in the imported data (unless I removed them). |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-20 : 09:34:16
|
Who's Edward? |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-07-23 : 11:07:03
|
BTW - Brett, thanks for suggesting TAB delimited files. Tastes great, less filling. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-23 : 12:07:18
|
quote: Originally posted by Arnold Fribble Who's Edward?
Boy am I feeling illBrett8-) |
|
|
|