Post reply

BCP

  • August 11, 2009 at 8:40 am

    #73886

    Hi,

    i need to export data from a view to a PSV file.

    I'm traing to do this t-sql:

    exec master..xp_cmdshell 'bcp "select * from [database]..test1" queryout "c:\Import.psv" -T -S "server\instancename"'

    I receive an error:

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for replace operation.

    I want to export this data to a unicode PSV so that people from Oracle can import it.

    Can someone help please?

    thank you

  • February 15, 2010 at 12:46 am

    #1118687

    Use this format...

    declare @sql varchar(8000)

    declare @path varchar(8000)

    set @path ='d:\import.psv'

    select @sql = 'bcp "set fmtonly off select * from db_name.DBO.table_name" queryout '+@path+' -c -t^ -T -S' + @@servername

    exec master..xp_cmdshell @sql

    this will give u the Psv format with tab delimiter..

  • May 2, 2012 at 9:41 am

    #1482505

    hi if u can send code for import using " bcp in "

    By Rajesh

  • May 2, 2012 at 9:46 am

    #1482513

    er.rajesh89 (5/2/2012)

    hi if u can send code for import using " bcp in "

    By Rajesh

    Booksonline and google are great resources, but if you need an example, here's both an out and in in:

    --using a super special 4 char row delimiter to be sure it doesn't exist in the data

    --flags explanation:

    -- -c = charcater data

    -- -t"[||]" = field terminator

    -- -r"[~~]" = row terminator

    -- -T' = Trusted connection

    --out

    EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT EMAILTEMPLATESID, EMAILBODY FROM Sandbox.dbo.EMAILTEMPLATES ORDER BY EMAILTEMPLATESID" queryout c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in

    EXECUTE master.dbo.xp_cmdshell 'bcp Sandbox.dbo.EMAILTEMPLATES in c:\Data\bcpExample.txt -c -t"[||]" -r"[~~]" -T'

    --in via bulk insert

    BULK INSERT EMAILTEMPLATES FROM 'c:\Data\bcpExample.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = '[||]',

    ROWTERMINATOR = '[~~]',

    FIRSTROW = 1

    )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 3, 2012 at 5:24 am

    #1482921

    Thanks Lowell :-D,

    this code is very used ....

  • March 1, 2013 at 8:14 am

    #1592400

    This is a related question, hoping someone on this thread can answer it.

    I've had a working bcp process for months that creates a CSV file on a network drive. Here's the command:

    'bcp "exec [dbname].[dbo].ExtractData" queryout ' + @destFolder + @fileName + ' -t \, -T -c'

    When @destFolder = "\\[server_name]\UploadFiles" it works

    If I change @destFolder to: "\\[server_name]\9 Auto Generated Upload Files\" with spaces in the folder name, it doesn't work.

    I can work around this, but does anyone know of a way to force bcp to accept spaces in the destination folder?

    Thanks in advance,

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • March 1, 2013 at 8:30 am

    #1592412

    Sigerson (3/1/2013)

    This is a related question, hoping someone on this thread can answer it.

    I've had a working bcp process for months that creates a CSV file on a network drive. Here's the command:

    'bcp "exec [dbname].[dbo].ExtractData" queryout ' + @destFolder + @fileName + ' -t \, -T -c'

    When @destFolder = "\\[server_name]\UploadFiles" it works

    If I change @destFolder to: "\\[server_name]\9 Auto Generated Upload Files\" with spaces in the folder name, it doesn't work.

    I can work around this, but does anyone know of a way to force bcp to accept spaces in the destination folder?

    Thanks in advance,

    it's the usual command line thing: files or paths that contain spaces must be wrapped with double quotes

    your code modified:note the dbl quotes that make up the path+file, and also for the t delimiter

    'bcp "exec [dbname].[dbo].ExtractData" queryout "' + @destFolder + @fileName + '" -t "\", -T -c'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 1, 2013 at 9:00 am

    #1592426

    @lowell,

    Thanks for the quick answer. Yes, a rookie mistake. (pause for hanging head in shame)

    Is there a word for having to learn so many fine points that one begins to forget the basics? :ermm:

    Thanks again,

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • March 1, 2013 at 9:05 am

    #1592429

    Sigerson (3/1/2013)

    @Lowell,

    Is there a word for having to learn so many fine points that one begins to forget the basics? :ermm:

    Oh yea,the only guys that remember it all, i think I hear it described as "no personal life" the most 😀

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 2, 2013 at 7:04 am

    #1592673

    Lowell (3/1/2013)

    Sigerson (3/1/2013)

    @Lowell,

    Is there a word for having to learn so many fine points that one begins to forget the basics? :ermm:

    Oh yea,the only guys that remember it all, i think I hear it described as "no personal life" the most 😀

    Heh... we can coin a new phrase, Lowell. "NPL" for "No Personal Life". I won't hazard the pronunciation of the acronym, though. 😛

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

Viewing 10 posts - 1 through 9 (of 9 total)

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