YACSVPUBCP - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

YACSVPUBCP

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 - 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

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.

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 - 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 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-)

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.