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

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

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

  • 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

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

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

  • 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

  • 🙁

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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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