Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: creating text files using bcp

    I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.

    Help is appreciated.

    I created a stored procedure with the below
    declare @filename varchar(50),
    @bcpcommand varchar(2000)

    set @filename = 'c:\report\media.txt'
    print @filename
    set @bcpcommand = 'bcp "select * from table" queryout "'+ @filename -U -P'
    exec master..xp_cmdshell @bcpCommand

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could format your select as needed.

  3. #3
    Join Date
    Jan 2004
    Posts
    10
    Can u elaborate more ...how do i format my sql to seperate the fields.

    like i want one field on position 1-10 and the seond from position 11-40 and so on....

    Thanks

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by hp1000
    Can u elaborate more ...how do i format my sql to seperate the fields.

    like i want one field on position 1-10 and the seond from position 11-40 and so on....

    Thanks
    Just create one long field or as many as you need like this:

    select field1+replicate(' ',25-datalength(field1)+....
    from table
    Last edited by snail; 01-23-04 at 17:27.

  5. #5
    Join Date
    Jan 2004
    Posts
    10
    I am using

    set @bcpcommand = 'bcp "select medium_name + replicate(' ',10-datalength(medium_name)) from database..report_tbl" queryout "'+ @filename + '" -U sa -P -c'

    I get Line 6: Incorrect syntax near '

    How can i escape the single quotes

    Please help

    Thsi method should work for what i want to do .

    Thanks

  6. #6
    Join Date
    Jan 2004
    Posts
    10
    Originally posted by snail
    Just create one long field or as many as you need like this:

    select field1+replicate(' ',25-datalength(field1)+....
    from table
    I am using the above but it does not work with NULL values . How do i make it to work with null values. Help is very much appreciated.

    Thanks

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you attempt to format your fields within BCP...QUERYOUT you may very quickly reach the limitation on the length of the command line (I don't remember what it is, but you can check.)

    What you can do instead, is create a stored procedure where you'd format whatever and whichever way you want, and in your BCP specify a call to that procedure along with QUERYOUT.

  8. #8
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    Here's how I do it - much easier to read and manage changes.

    Much like a structure or type, I create a temporary table in a stored procedure (TABLE vars won't work, as BCP won;t be able to see them), using CHAR data types, one field for each output field. I have a user-defined function for zero-padding, and one for right-justifying (space padding). I populate that table with all my data, then do a BCP statement consisting of SELECT field1 + field2 + field3 etc.

    This gives my the spacing I want, and that one create table at the beginning of the procedure allows me to easily control the number spaces each field gets.

    I can post a proc if it would be easier to understand.

    -b
    -bpd

  9. #9
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My table def looks like this (for an SAP feed):

    CREATE TABLE ##sap_table(sales_organization CHAR(4) NOT NULL,
    distribution_channel CHAR(2) NOT NULL,
    division CHAR(2) NOT NULL,
    filler_1 CHAR(2) NOT NULL,
    sold_to CHAR(8) NOT NULL,
    delivering_plant CHAR(4) NOT NULL,
    profit_center CHAR(10) NOT NULL,
    material_code CHAR(10) NOT NULL,
    pricing_date CHAR(8) NOT NULL,
    charge_type CHAR(4) NOT NULL,
    order_reason CHAR(3) NOT NULL,
    project_number CHAR(24) NOT NULL,
    contract_number CHAR(10) NOT NULL,
    purchase_order_number CHAR(12) NOT NULL,
    internal_sales_reference CHAR(12) NOT NULL,
    usage_type CHAR(3) NOT NULL,
    usage_detail CHAR(60) NOT NULL,
    requestor_name CHAR(40) NOT NULL,
    user_number CHAR(10) NOT NULL,
    [user_name] CHAR(40) NOT NULL,
    comments CHAR(100) NULL,
    quantity CHAR(15) NOT NULL,
    unit_of_measure CHAR(3) NOT NULL,
    currency CHAR(5) NOT NULL,
    extended_amount CHAR(15) NOT NULL,
    customer_cost_object CHAR(40) NOT NULL,
    cco_flag CHAR(3) NOT NULL,
    xy_flag CHAR(1) NOT NULL,
    filler_2 CHAR(6) NOT NULL )
    -bpd

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you want fixed width data, why not use a format file?
    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
    Jan 2004
    Posts
    10
    Thanks for all ur feedback . I am using coalesce and datalength functions and it did the trick.

    I create a view with the fields i want in the format i want and then do the select on the view with my bcp and it writes to the text file just fine.

    Thanks a lot,

Posting Permissions

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