SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 YACSVPUBCP
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/19/2004 :  13:19:45  Show Profile  Reply with Quote
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
22431 Posts

Posted - 07/19/2004 :  13:49:42  Show Profile  Reply with Quote
Don't know much about BCP, but what abou a Format File?

Kristen
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/19/2004 :  14:06:35  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/19/2004 :  14:31:10  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/19/2004 :  14:35:14  Show Profile  Reply with Quote
If BCP can be setup to import a file meeting the requirements you just listed, I'd like to know how to do it.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/19/2004 :  14:42:32  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/19/2004 :  15:15:24  Show Profile  Reply with Quote
I've got an investment in BCP. It would be a crunch to move to DTS.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/19/2004 :  15:56:00  Show Profile  Reply with Quote
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-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/19/2004 :  16:12:10  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/20/2004 :  01:12:32  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/20/2004 :  09:06:42  Show Profile  Reply with Quote
My preprocessor is Windows Notepad, and it's already done. BCP included the quote marks in the imported data (unless I removed them).

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 07/20/2004 :  09:34:16  Show Profile  Reply with Quote
Who's Edward?
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 07/23/2004 :  11:07:03  Show Profile  Reply with Quote
BTW - Brett, thanks for suggesting TAB delimited files. Tastes great, less filling.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/23/2004 :  12:07:18  Show Profile  Reply with Quote
quote:
Originally posted by Arnold Fribble

Who's Edward?




Boy am I feeling ill



Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000