Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2006
    Posts
    16

    Unanswered: exportting to a flat text file

    Hi, I need to export data from my sql server to a flat text file.

    using select I only have the options of delimmited or column aligned, in either case the output file is unicode encoded. I need an ASNI encoded file.

    I can create a non Unicode file if I use Import/Export but then the file does not have CR/LF or enf of record mark which I need.

    Is there a way to export a flat text file without unicodes and with CR/LF or enf of record mark ?

    I was looking into bcp, but I can't seem to find a good sample for this.

    thank you

    Alan

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    And pray tell ... what did you do to look into bcp? BOL, Google, search dbForums, SQLTEAM, SQLServerCentral, etc. Oh look, here's BOL ...

    Copying Data From a Query to a Data File

    The bcp utility allows you to copy the result set from a Transact-SQL statement to a data file. The Transact-SQL statement can be any valid statement that returns a results set, such as a distributed query or a SELECT statement joining several tables. For example, to copy the names of all the authors, ordered by surname, from the authors table in the pubs database to the Authors.txt data file, execute at the command prompt:
    bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -PpasswordBulk copying data from a query is useful if you want to ensure that the order of the data is preserved in the data file; bulk copying data from a table or view does not guarantee the order of the data written to the data file. Preserving the order of the data in the data file allows you to make use of the ORDER hint when bulk copying data from the data file back into a table. Using the ORDER hint can significantly improve bulk copy performance. For more information, see Optimizing Bulk Copy Performance.
    If the Transact-SQL statement returns multiple result sets, such as a SELECT statement that specifies the COMPUTE clause, or the execution of a stored procedure that contains multiple SELECT statements, only the first result set is copied; subsequent result sets are ignored.

    See Also


    bcp Utility
    Ordered Data Files
    1988-2000 Microsoft Corporation. All Rights Reserved.



    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Apr 2006
    Posts
    16
    this is my first time using bcp. So I'm a little in the dark. I've found many samples, but just not sure how to execute it. Do I copy such examples into a new query in SQL? or do I execute this from a regular dos comand promt? I have to use the following select when exporting the data.

    SELECT
    AddressID, IndividualId, AreaCode, Phone, PrimaryAddress, SecondaryAddress,
    CityName, State, ZipCode, Zip_4, Carrier_Route, PersonFirstName, PersonMiddleInitial,PersonLastName, PersonSurnameSuffix
    FROM tblConsumerListing INNER JOIN
    CUST_FILE ON CUST_FILE.PO = SUBSTRING(tblConsumerListing.UnitDesignatorNumber, 1,2) AND CUST_FILE.ZIP = tblConsumerListing.ZIPCODE
    WHERE UnitDesignator LIKE 'PO%'



    Thanks for your help.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    bcp is executed as an OS command. You can use the command line prompt, xp_cmdshell, or other methods.

    First step is to ensure your query works (since it looks like you want to use the QUERYOUT option. Once you have that working, try to put together a command line prompt and post here for review. Use the BOL link I gave you as a starting point. Decide whether or not you will be using a sql standard login to retrieve the data, or a Trusted Connection (Windows Domain login).

    Do not put actual login info in the command line you post here.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Apr 2006
    Posts
    16
    ok something like this? can I create a simple .bat file and run this?

    bcp "SELECT
    AddressID,
    IndividualId,
    AreaCode,
    Phone,
    PrimaryAddress,
    SecondaryAddress,
    CityName,
    State,
    ZipCode,
    Zip_4,
    Carrier_Route,
    PersonFirstName,
    PersonMiddleInitial,
    PersonLastName,
    PersonSurnameSuffix

    FROM tblConsumerListing INNER JOIN
    CUST_FILE ON CUST_FILE.PO = SUBSTRING(tblConsumerListing.UnitDesignatorNumber, 1,2) AND CUST_FILE.ZIP = tblConsumerListing.ZIPCODE
    WHERE UnitDesignator LIKE 'PO%'"

    queryout H:\Productn\SQL_Databases\Consumer_ENH_test\alan.t xt -c -r



    this is going to return a character data with a row terminator? I'm using windows authentication

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Assuming that the query works, you forgot the -S <Servername> argument, the -T argument (trusted connection), and I believe that the -r argument is superfluous but I will have to verify that the "\n" default terminator does indeed mean 0x0D0A (CR/LF).

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tomh53
    I will have to verify that the "\n" default terminator does indeed mean 0x0D0A (CR/LF).
    I believe it does. IIRC Linux uses CR only so we got caught out by /n being the DOS CRLF.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    After a little testing, you will also have a problem with the % in the LIKE statement. The OS will see that as an input parameter request and will throw an error.

    I would recommend you execute the query within SQL Server then use a straight bcp out statement to move the results table into the flat file.

    I can also confirm that the \n is indeed a hex 0D0A.

    Here is a sample that I usedd to extract data from the Northwind database ...

    Code:
    bcp "select o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate from Northwind.dbo.Orders o INNER JOIN Northwind.dbo.Customers c ON c.CustomerID = o.CustomerID and c.Country ='Mexico'" queryout E:\dataout.txt -c -T -S DGADEVSERVER\DGADEVSERVER
    hope this helps.

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Apr 2006
    Posts
    16
    thanks for all the info. It sounds like bcp is a very powerfull tool. I think creating a temp table for my results will not be time efficient, but I could use this info on other situations I have.

    Actually, I just tried using "ragged right" option in the import/export wizard and that appears to give me the option for cr/lf. SO that should solve my main problem :-)

    I'm getting side tracked here, but only the selrver has the import/export option. the users on the other machines with "sql server management studio express CTP" does not. Can I grant all users this option? or do I need to upgrade to the "sql server management studio" to have access to the import export wizard?

    thanks!

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Create a view and export that out as tab delimited

    DECLARE @cmd varchar(8000)
    SET @cmd = 'bcp schema.owner.view out c:\test.txt -S<Server> -T -c'
    master..xpcmdshell @cmd
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by tomh53
    After a little testing, you will also have a problem with the % in the LIKE statement. The OS will see that as an input parameter request and will throw an error.
    the workaround for this is just to double up the percent chars: %% instead of % when invoking BCP from a .bat or .cmd file.

    when I have a long query to run through bcp and can't create a view, I usually break it up for readablilty. for example:
    Code:
    @echo off
    set sql=  SELECT
    set sql=%sql%   AddressID,
    set sql=%sql%   IndividualId,
    set sql=%sql%   AreaCode,
    set sql=%sql%   Phone,
    set sql=%sql%   PrimaryAddress,
    set sql=%sql%   SecondaryAddress,
    set sql=%sql%   CityName,
    set sql=%sql%   State,
    set sql=%sql%   ZipCode,
    set sql=%sql%   Zip_4,
    set sql=%sql%   Carrier_Route,
    set sql=%sql%   PersonFirstName,
    set sql=%sql%   PersonMiddleInitial,
    set sql=%sql%   PersonLastName,
    set sql=%sql%   PersonSurnameSuffix
    set sql=%sql% FROM MyDatabase.dbo.tblConsumerListing t 
    set sql=%sql% INNER JOIN MyDatabase.dbo.CUST_FILE c 
    set sql=%sql%   ON c.CUST_FILE.PO =SUBSTRING(t.UnitDesignatorNumber, 1,2) 
    set sql=%sql%   AND c.CUST_FILE.ZIP = t.ZIPCODE
    set sql=%sql% WHERE UnitDesignator LIKE 'PO%%'
    
    bcp "%sql%" queryout mydata.txt -c -T -SMYSERVER -emyfile.err > mydata.log
    Last edited by jezemine; 03-27-08 at 00:58.

  12. #12
    Join Date
    Apr 2006
    Posts
    16
    thanks for the info! :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •