Post reply

How to get rid of the dotted separation line from a text file output?

  • September 23, 2010 at 3:48 am

    #227170

    Hello folks

    I am recently trying to output a query result into a text file but seems to unable to get rid of the dotted separation line between the column names and the row data.

    Here is an example from the command line (assuming you have 'pubs' db on a local default instance):

    sqlcmd -E -d pubs -Q "select * from authors" -o authors.txt

    And the content of the authors.txt file is:

    au_id au_lname au_fname phone address city state zip contract

    ----------- ---------------------------------------- -------------------- ------------ ---------------------------------------- -------------------- ----- ----- --------

    172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025 1

    213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA 94618 1

    238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley CA 94705 1

    267-41-2394 O'Leary Michael 408 286-2428 22 Cleveland Av. #14 San Jose CA 95128 1

    274-80-9391 Straight Dean 415 834-2919 5420 College Av. Oakland CA 94609 1

    341-22-1782 Smith Meander 913 843-0462 10 Mississippi Dr. Lawrence KS 66044 0

    409-56-7008 Bennet Abraham 415 658-9932 6223 Bateman St. Berkeley CA 94705 1

    427-17-2319 Dull Ann 415 836-7128 3410 Blonde St. Palo Alto CA 94301 1

    472-27-2349 Gringlesby Burt 707 938-6445 PO Box 792 Covelo CA 95428 1

    486-29-1786 Locksley Charlene 415 585-4620 18 Broadway Av. San Francisco CA 94130 1

    527-72-3246 Greene Morningstar 615 297-2723 22 Graybar House Rd. Nashville TN 37215 0

    648-92-1872 Blotchet-Halls Reginald 503 745-6402 55 Hillsdale Bl. Corvallis OR 97330 1

    672-71-3249 Yokomoto Akiko 415 935-4228 3 Silver Ct. Walnut Creek CA 94595 1

    712-45-1867 del Castillo Innes 615 996-8275 2286 Cram Pl. #86 Ann Arbor MI 48105 1

    722-51-5454 DeFrance Michel 219 547-9982 3 Balding Pl. Gary IN 46403 1

    724-08-9931 Stringer Dirk 415 843-2991 5420 Telegraph Av. Oakland CA 94609 0

    724-80-9391 MacFeather Stearns 415 354-7128 44 Upland Hts. Oakland CA 94612 1

    756-30-7391 Karsen Livia 415 534-9219 5720 McAuley St. Oakland CA 94609 1

    807-91-6654 Panteley Sylvia 301 946-8853 1956 Arlington Pl. Rockville MD 20853 1

    846-92-7186 Hunter Sheryl 415 836-7128 3410 Blonde St. Palo Alto CA 94301 1

    893-72-1158 McBadden Heather 707 448-4982 301 Putnam Vacaville CA 95688 0

    899-46-2035 Ringer Anne 801 826-0752 67 Seventh Av. Salt Lake City UT 84152 1

    998-72-3567 Ringer Albert 801 826-0752 67 Seventh Av. Salt Lake City UT 84152 1

    (23 rows affected)

    My question is where there is a way to supress that annoying dotted line between column name and the rows?

    Thanks very much in advance!

    Bazinga!

  • September 23, 2010 at 7:27 am

    #1226310

    Use bcp.exe to get data to the disk, the output of SQLCMD is not really meant to be processed automaticaly.

  • September 23, 2010 at 8:43 am

    #1226381

    Here is the thing.

    I actually don't want to get pure data out of a query, the query out will present a set of dos batch commands that can be put into a .bat file and be executed. And this query is not simple enough to be included on the bcp command line.

    So that's why I am actually using something like this

    sqlcmd -E -i generate_batch_commands.sql -o the_much_needed_bach_commands.bat

    But somehow, in the "the_much_needed_bach_commands.bat" there are the annoying dotted separation lines.

    Bazinga!

  • September 23, 2010 at 9:06 am

    #1226400

    Trying to find something but I am pretty sure the only way to remove the dotted line is to not output the header.

    sqlcmd -E -d pubs -h-1 -Q "select * from authors" -o authors.txt

    -h-1 = -h is the header parameter and -1 is the setting to disable header print output.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • September 23, 2010 at 9:16 am

    #1226407

    Shawn Melton (9/23/2010)

    Trying to find something but I am pretty sure the only way to remove the dotted line is to not output the header.

    sqlcmd -E -d pubs -h-1 -Q "select * from authors" -o authors.txt

    -h-1 = -h is the header parameter and -1 is the setting to disable header print output.

    Hello Shawn

    Thanks so much for your great help!

    😀

    Bazinga!

Viewing 5 posts - 1 through 4 (of 4 total)

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