trying to export to .CSV with comma delimited AND double-quotes

Question

trying to export to .CSV with comma delimited AND double-quotes

hugh.hemington

SSC Eights!

Points: 861
More actions
May 20, 2010 at 1:49 am

#147075

What I have is a simple table (in SQL 2005) that provides input to a mapping program.
It has four columns. I'm trying to automate the process of exporting it to .CSV because I'm tired of importing the table into Access, then exporting it to .CSV.
Here's what I need:
"Addr","City","State","Zip"
"123 Main St.","Glendale","CA","92203"
...
...
In other words, comma delimited, double-quoted data WITH a header row.
I found a post that used a created view, but since I'm trying to do this from inside a STORED PROCEDURE, I can't create a view. I haven't figured out or found how to do this with a format file. If I knew how to drive the output I want backwards through bcp and create a format file, it might produce something that would make the output correct the other direction.
If this is possible using BCP, is there a GOOD primer on it? (I've read all the bad ones already)
If not, does anyone have an idea how I can produce that file format?
Thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

hugh.hemington SSC Eights! Points: 861 More actions · Answer 1

I figured out that I could "can" the views in advance, outside the stored procedure, but I'd still like to use BCP a lot better without so much hassle if possible.

So any good references on it, with lots of examples would be great!

cmcc Old Hand Points: 368 More actions · Answer 2

Did you get any replies to this topic?

I, too, prefer to contain all the logic in a stored procedure rather than fuss with an SSIS package.

Colleen

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

This will do it.

bcp "SELECT QUOTENAME('Address',CHAR(34))+','+QUOTENAME('City',CHAR(34))+','+QUOTENAME('State',CHAR(34))+','+QUOTENAME('ZIP',CHAR(34)) d UNION ALL SELECT QUOTENAME(AddressLine1,CHAR(34))+',' + QUOTENAME(City,CHAR(34))+',' + QUOTENAME(StateProvinceID,CHAR(34))+',' + QUOTENAME(PostalCode,CHAR(34)) FROM AdventureWorks.Person.Address" queryout "C:\Temp\Contacts.txt" -c -T -SPutYourServer\InstanceNameHere

Change the "PutYourServer\InstanceNameHere" to the actual name of you Server and Instance name. This particular code runs against the AdventureWorks database. You'll need to change the FROM clause for that and the column names to match the table you're drawing from.

This code also writes to "C:\Temp\Contacts.txt" on the server. You can change that to whatever UNC you want.

Last but not least, this uses a Trusted Conection with Windows Authentication. If you only have SQL Server Authentication, then I don't recommend using this because you'll need to include the user name and password in clear text. Instead, you'd need to use %1 and %2 as batch substitution variables in a batch file and provide them at run time.

If your system is properly locked down for it, you could to this from a stored procedure using xp_CmdShell in a stored procedure or just from a job on the server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

hugh.hemington (5/20/2010)
I figured out that I could "can" the views in advance, outside the stored procedure, but I'd still like to use BCP a lot better without so much hassle if possible.
So any good references on it, with lots of examples would be great!

Just one example is all that's needed... if it works. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cmcc Old Hand Points: 368 More actions · Answer 5

Perfect! I can put this in an SP and just schedule that.

SSIS is giving me conniptions ; I'd like to stay in my comfortable T-SQL world where I am master of the universe as long as I can.

Off to try it out...

Colleen

cmcc Old Hand Points: 368 More actions · Answer 6

🙁

Didn't work.

Getting this

...is too long. Maximum length is 128.

I use a view for the data, which is short enough.

It's getting the column names output that is causing me problems.

I'd use SSIS but I don't have time - I've been fiddling with this problem in SSIS for six weeks now, off and on, and keep tripping over one SSIS issue after another.

At least the BCP error makes sense to me.

In SSIS I have to change the file names - they have to be the date the package is run or the date from a parameter I have to pass in. This is a piece of cake from a parameter in a stored procedure but is taking a while to work through in SSIS.

i.e. I haven't gotten it to work yet.

ARGH!!!!

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

cmcc (11/1/2012)
🙁
Didn't work.
Getting this
...is too long. Maximum length is 128.
I use a view for the data, which is short enough.
It's getting the column names output that is causing me problems.
I'd use SSIS but I don't have time - I've been fiddling with this problem in SSIS for six weeks now, off and on, and keep tripping over one SSIS issue after another.
At least the BCP error makes sense to me.
In SSIS I have to change the file names - they have to be the date the package is run or the date from a parameter I have to pass in. This is a piece of cake from a parameter in a stored procedure but is taking a while to work through in SSIS.
i.e. I haven't gotten it to work yet.
ARGH!!!!

Haven't seen you posting these questions in the SSIS forum 🙂

You could also, perhaps (if the columns are static), create a text file somewhere which contains only the column headings.

Then, after your BCP has run, use a simple DOS Copy to append one file to the other.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

cmcc (11/1/2012)
🙁
Didn't work.
Getting this
...is too long. Maximum length is 128.
I use a view for the data, which is short enough.
It's getting the column names output that is causing me problems.
I'd use SSIS but I don't have time - I've been fiddling with this problem in SSIS for six weeks now, off and on, and keep tripping over one SSIS issue after another.
At least the BCP error makes sense to me.
In SSIS I have to change the file names - they have to be the date the package is run or the date from a parameter I have to pass in. This is a piece of cake from a parameter in a stored procedure but is taking a while to work through in SSIS.
i.e. I haven't gotten it to work yet.
ARGH!!!!

Post your BCP command for this because, as you can see, my command is larger than 128. What may be wrong is a very large column name that QUOTENAME is tripping over. There's a fairly easy fix for that but it would help to see the actually BCP command you're using.

Also... do any of the columns in the BCP command have a datatype larger than VARCHAR(128) or NVARCHAR(64)???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Out of curiosity, have you tried the import/export wizard?

It's a simple tool, just be sure to read all the options available.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cmcc Old Hand Points: 368 More actions · Answer 10

Thanks for the reply.

Yes, I tried the export wizard, which is what I use for casual one-off exports.

This is more complicated - three different files exported every day at a specific time (overnight), with a very specific naming convention, then posted to the client's FTP site.

For (at present) three different projects for this client.

Which is expected to expand to 32 projects. A year.

So I need a parameterized, robust solution where I can pass in a project code and have all the files created. With an outer container that will run through the project codes and apply the same logic to each.

So I need the SSIS variables to assemble the file names with the correct date and project code.

The T-SQL part is done - stored procedure, views - I just haven't had to dive this deeply into SSIS before. With any success.

I learned coding waaaaayyy back in VB days (1980s?) before moving to RDBMSs.

T-SQL has been very forgiving about datatype conversions; SSIS is not.

I'm only slowly learning how to think in this paradigm. I'm trying NOT to bring DTS tricks (file scripting object) to SSIS and learn how to do it the new (or not so new) way - you can see my struggle in the posts. There's no money for classes or conferences and we are discouraged from buying books ("just google it!") - it's slow.

OK, there has to be a question here somewhere.

I have to pick up from yesterday after I get the am tasks done. Be back...

And I am *so* grateful for this online community...

Colleen

Phil Parkin SSC Guru Points: 246965 More actions · Answer 11

What I would recommend is that you get your package working with a simple design and then just add the rest of the features and refinements one by one, testing all along the way.

Do not try to implement everything in one go - trying to track down what is going wrong will be too difficult.

Very best of luck.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin