Post reply

BCP Format File

  • March 6, 2009 at 9:28 pm

    #955394

    Sorry it took me so long to get back to this...

    No... no \t's here... I believe this is what you're looking for. Note that the forum ate the backslash-n that came after the \r...

    9.0

    13

    1 SQLCHAR 0 1 "\"" 0 LeadQuote Latin1_General_CI_AS

    2 SQLCHAR 0 100 "\",\"" 1 title Latin1_General_CI_AS

    3 SQLCHAR 0 100 "\",\"" 2 forename Latin1_General_CI_AS

    4 SQLCHAR 0 100 "\",\"" 3 surname Latin1_General_CI_AS

    5 SQLCHAR 0 500 "\",\"" 4 pafad1 Latin1_General_CI_AS

    6 SQLCHAR 0 500 "\",\"" 5 pafad2 Latin1_General_CI_AS

    7 SQLCHAR 0 500 "\",\"" 6 pafad3 Latin1_General_CI_AS

    8 SQLCHAR 0 500 "\",\"" 7 pafad4 Latin1_General_CI_AS

    9 SQLCHAR 0 500 "\",\"" 8 pafad5 Latin1_General_CI_AS

    10 SQLCHAR 0 500 "\",\"" 9 pafad6 Latin1_General_CI_AS

    11 SQLCHAR 0 100 "\",\"" 10 postcode Latin1_General_CI_AS

    12 SQLCHAR 0 12 "\",\"" 11 urn ""

    13 SQLCHAR 0 500 "\"\r" 12 emailaddress Latin1_General_CI_AS

    --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 9, 2009 at 4:25 am

    #955862

    This is very annoying!!

    Right I have created the format file in the way you suggested now Jeff (Thanks)

    But on trying to execute the following BCP Command:

    declare @bcpcommand nvarchar(1000)

    SET @bcpcommand = 'bcp "ASHLEY.DBO.Tablename" out c:\work\test.txt -f c:\work\bcpformatfile.fmt -T'

    EXEC master..xp_cmdshell @bcpCommand

    I get the error: Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server

    Any ideas?

  • March 9, 2009 at 6:17 am

    #955899

    Heh... dammit... I didn't test it and I forgot about that little nuance... My humble appolgogies...

    Here's a new BCP Format file with no "skips"...

    9.0

    12

    1 SQLCHAR 0 100 "\",\"" 1 title Latin1_General_CI_AS

    2 SQLCHAR 0 100 "\",\"" 2 forename Latin1_General_CI_AS

    3 SQLCHAR 0 100 "\",\"" 3 surname Latin1_General_CI_AS

    4 SQLCHAR 0 500 "\",\"" 4 pafad1 Latin1_General_CI_AS

    5 SQLCHAR 0 500 "\",\"" 5 pafad2 Latin1_General_CI_AS

    6 SQLCHAR 0 500 "\",\"" 6 pafad3 Latin1_General_CI_AS

    7 SQLCHAR 0 500 "\",\"" 7 pafad4 Latin1_General_CI_AS

    8 SQLCHAR 0 500 "\",\"" 8 pafad5 Latin1_General_CI_AS

    9 SQLCHAR 0 500 "\",\"" 9 pafad6 Latin1_General_CI_AS

    10 SQLCHAR 0 100 "\",\"" 10 postcode Latin1_General_CI_AS

    11 SQLCHAR 0 12 "\",\"" 11 urn ""

    12 SQLCHAR 0 500 "\"\r" 12 emailaddress Latin1_General_CI_AS

    When you do your query as part of the BCP command, start it with...

    SELECT '"'+Title,

    ... to include the leading quote.

    Again, this forum ate the backslash "N" after the backslash "R" and should be included.

    --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 9, 2009 at 6:19 am

    #955903

    If we can't get this bugger up and running in short order, we'll do the QUOTENAME thing for the export so we don't have to mess with the Format file... but I believe the format file will do it's trick correctly now.

    --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 9, 2009 at 6:55 am

    #955933

    OK seems to be nearly working now I am just having trouble inserting the leading quote

    I tried running the following:

    declare @bcpcommand nvarchar(1000)

    SET @bcpcommand = 'bcp "SELECT ""+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

    EXEC master..xp_cmdshell @bcpCommand

    But I now get the error;

    'Error = [Microsoft][SQL Native Client][SQL Server]Unclosed quotation mark after the character string '+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.Tablename'.'

  • March 9, 2009 at 10:08 am

    #956170

    Update - I have got around this for now by adding an additional column and populating the column with a double quote. I then adjusted the command to read:

    declare @bcpcommand nvarchar(1000)

    SET @bcpcommand = 'bcp "SELECT quote+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

    PRINT @BCPCOMMAND

    EXEC master..xp_cmdshell @bcpCommand

    Don't know whether this could have been done without altering the table but if anyone has any suggestions please go ahead and suggest...Thanks:D

  • March 9, 2009 at 4:29 pm

    #956462

    Yep... no need for the extra column...

    SET @bcpcommand = 'bcp "SELECT ''"''+title,forename,surname,pafad1,pafad2,pafad3,pafad4,pafad5,pafad6,postcode,urn,emailaddress from ASHLEY.DBO.tablename" queryout c:\work\ashtest.txt -f c:\work\bcpformatfile.fmt -T'

    --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, 2009 at 3:11 am

    #956625

    Hello Jeff

    I tried that but I get the BCP error (the one which advises the correct syntax to use) Can you test the command and hopefully provide one that works

    Thanks

  • March 10, 2009 at 4:06 am

    #956647

    Try char(34) to make the select command generate a double quote 'during the query'. This avoids the problem of the commandline seeing that double quote. I have also added quotes around the file names just in case you intend to use pathnames that have spaces in them.

    SET @bcpcommand = 'bcp "SELECT char(34)+title, forename, surname, pafad1, pafad2, pafad3, pafad4, pafad5, pafad6, postcode, urn, emailaddress from ASHLEY.DBO.tablename" queryout "c:\work\ashtest.txt" -f "c:\work\bcpformatfile.fmt" -T'

  • March 10, 2009 at 5:10 am

    #956680

    Thanks alot twillcomp - That has sorted the issue

    The process is now working a treat!

    Thanks everyone for all your help - You have been Great!!

Viewing 10 posts - 16 through 24 (of 24 total)

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