| Author |
 Topic  |
|
|
SamC
White Water Yakist
USA
3459 Posts |
 Posted - 07/19/2004 : 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 |
Edited by - SamC on 07/19/2004 13:28:27
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/19/2004 : 13:49:42
|
Don't know much about BCP, but what abou a Format File?
Kristen |
 |
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 07/19/2004 : 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
United Kingdom
22191 Posts |
Posted - 07/19/2004 : 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 - OK
If 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/thingamajig
Kristen |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 07/19/2004 : 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
United Kingdom
22191 Posts |
Posted - 07/19/2004 : 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
USA
3459 Posts |
Posted - 07/19/2004 : 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 - 07/19/2004 : 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.
Sam
TAB 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 Rules
Brett
8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/19/2004 : 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 it
USE Northwind
GO
CREATE TABLE myTable99(Col1 varchar(8000))
CREATE TABLE myTable00(Col1 varchar(10),Col2 varchar(10),Col3 varchar(10))
GO
INSERT INTO myTable99(Col1)
SELECT '"Col1","Col2","Col3"' UNION ALL
SELECT '"Cola","Colb","Colc"' UNION ALL
SELECT '"Colx","Coly","Colz"' UNION ALL
SELECT '"Col7","Col8","Col9"'
GO
SELECT * FROM myTable99
master..xp_cmdshell 'bcp Northwind.dbo.myTable99 OUT c:\test.dat -S -Usa -P -c -t","'
GO
master..xp_cmdshell 'bcp Northwind.dbo.myTable00 IN c:\test.dat -S -Usa -P -c -t","'
GO
SELECT * FROM myTable00
DROP TABLE myTable99
DROP TABLE myTable00
GO
Brett
8-) |
Edited by - X002548 on 07/23/2004 12:08:00 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/20/2004 : 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
USA
3459 Posts |
Posted - 07/20/2004 : 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
United Kingdom
1961 Posts |
Posted - 07/20/2004 : 09:34:16
|
Who's Edward?
|
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 07/23/2004 : 11:07:03
|
| BTW - Brett, thanks for suggesting TAB delimited files. Tastes great, less filling. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/23/2004 : 12:07:18
|
quote:
Originally posted by Arnold Fribble
Who's Edward?
Boy am I feeling ill
Brett
8-) |
|
|
| |
Topic |
|
YACSVPUBCP
