Post reply

Removing double quotes from colum names with cvs export

  • March 7, 2008 at 4:04 am

    #187355

    I have problem... We do weekly exports and emailing from our database to one of customers. The problem is that our customer needs text file to look like this:

    id,vastausa,vastausb,pvm,tunnus,vastausc,vastausd

    1,"gfdgfg","gfdgfg",,"","",""

    2,"gfdgfg","gfdgfg",2002-01-10 11:03:39.463000000,"","",""

    3,"gfdgfg","gfdgfg",2002-01-10 11:03:41.243000000,"","",""

    4,"gfdgfdgfd","gfdgfdgfd",2002-01-10 11:42:57.743000000,"timtinen1","",""

    Only thing we can get from sql text export is this:

    "id","vastausa","vastausb","pvm","tunnus","vastausc","vastausd"

    1,"gfdgfg","gfdgfg",,"","",""

    2,"gfdgfg","gfdgfg",2002-01-10 11:03:39.463000000,"","",""

    3,"gfdgfg","gfdgfg",2002-01-10 11:03:41.243000000,"","",""

    4,"gfdgfdgfd","gfdgfdgfd",2002-01-10 11:42:57.743000000,"timtinen1","",""

    As you see my problem is those double quetes on colum names at the first row of the file... Cutomer needs double quotes on data part of file, but not on colum field.

    How do I export test file so that colum names are without double quotes, but datapart has double quotes as needed?

    We use MSsqlserver 2000. By solving this we can make this task fully automated...

  • March 7, 2008 at 5:00 am

    #786560

    Not quite sure, but some programs call needed export format as Merge file. How do I export data as merge file from SQLserver?

  • March 10, 2008 at 1:37 am

    #787222

    Anyone has any helpo for me?

    Do I have to do some sort of activex script to do this task or is there any way to do this easily...?

  • March 10, 2008 at 4:41 am

    #787261

    What are you using to do the export with? DTS?

    --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)
    Intro to Tally Tables and Functions

  • March 10, 2008 at 4:49 am

    #787265

    We are using normal "text file destination" to export data to text file.

  • March 10, 2008 at 4:51 am

    #787269

    ...and with DTS paggage.. ..vith simple SMTP mail task with it...

  • March 10, 2008 at 5:36 am

    #787284

    Sorry... I know squat about DTS... not sure if activex will help here or not.

    --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)
    Intro to Tally Tables and Functions

  • March 10, 2008 at 6:01 am

    #787295

    hi ...

    I have understand u r problem...

    while exporting table data to text file ,

    we get the Text Qualifier drop down list with options like double quote,single quote ,none in the wizard control.

    if u select the 'none' option, then u get what u want exactly.

    Thank you,

    Ajay Varma

  • March 10, 2008 at 6:09 am

    #787300

    Ajay Varma (3/10/2008)

    hi ...

    I have understand u r problem...

    while exporting table data to text file ,

    we get the Text Qualifier drop down list with options like double quote,single quote ,none in the wizard control.

    if u select the 'none' option, then u get what u want exactly.

    Thank you,

    Ajay Varma

    No... that will remove the double quotes from everything... OP wants to remove the double quotes only from the header of the file.

    --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)
    Intro to Tally Tables and Functions

  • March 10, 2008 at 6:14 am

    #787303

    Yeah... I only wan't to remove double quetes from header.

    I tryed to look, search with google if there is some sort of DTS export task plugin or some, but was not abble to find any.... It would be the easyest for me. Not so familiar with activex scripts. 🙁

  • March 10, 2008 at 6:44 am

    #787327

    hi again,

    k it will remove double quotes from everything...

    but i don't know for only columns... sorry

    can anyone hav the solution for this....

    Thank you,

    ajay varma.

  • March 10, 2008 at 6:50 am

    #787332

    Could be done in plain ol' T-SQL... but would require either a trip through xp_CmdShell or would need to be called externally in a batch file.

    --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)
    Intro to Tally Tables and Functions

  • March 10, 2008 at 10:55 am

    #787496

    Ok. I found litle vbscript (activex) script to the task. First I do simple DTS export then execute my activex and then mail the file to cutomer.

    This is the activex (vbscript):

    Function Main()

    Const ForReading = 1

    Const ForWriting = 2

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objFile = objFSO.OpenTextFile("e:\test.txt", ForReading)

    strContents = objFile.ReadAll

    objFile.Close

    strFirstLine = "This is the new first line in the text file."

    strNewContents = strFirstLine & vbCrLf & strContents

    Set objFile = objFSO.OpenTextFile("e:\test.txt", ForWriting)

    objFile.WriteLine strNewContents

    objFile.Close

    Main = DTSTaskExecResult_Success

    end function

    http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun05/hey0630.mspx

    I just manually do the first line of file... 🙂

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

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