SQL Server Performance

dump a table into a text file

Discussion in 'General Developer Questions' started by mtmingus, Dec 10, 2003.

  1. mtmingus New Member

    Any method to dump a table (query) into a text file,
    with a specified deliminator such as '|', but NO
    white space for varchar fields:

    column name objectId is varchar(20), but I want it
    appears in the text file as Z(with '|'):


    instead of


    where '.' indicates a white space.
  2. FrankKalis Moderator

  3. satya Moderator

    Can take help of ISQL and refer to books online for more information.

    Satya SKJ
  4. Jon M Member

  5. mtmingus New Member

    Thanks but this doesn't resolve my problem.
    I know osql/isql can do the dump, but the
    question is,

    can the white space be trimmed automatically?

    BOL doesn't mention white space trim on the
    OSQL/ISQL items.
  6. FrankKalis Moderator

  7. mtmingus New Member

    no white space in query result, but when you use
    osql/isql to dump it, it takes whatever size
    defined in varchar. If the objectId is defined
    as varchar(100), and the actual value is 'A-1234',
    the dumpped text value will be
    'A-1234' followed by 94 white spaces.

    quote:Originally posted by FrankKalis

    Sorry, but when your column is a varchar there shouldn't be white spaces (blanks) at all, or?

  8. Jon M Member

    Try this sql:

    STUFF(yourstring,CHARINDEX (' ' , yourstring ), 100-len(yourstring),'') + '|'
    FROM whatever_table

    Jon M
  9. FrankKalis Moderator

  10. Luis Martin Moderator

    How about this:

    Returns a character string after truncating all trailing blanks.

    RTRIM ( character_expression )


    Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

    Return Types

    character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

    Note Compatibility levels can affect return values. For more information, see sp_dbcmptlevel.

    This example demonstrates how to use RTRIM to remove trailing spaces from a character variable.

    DECLARE @string_to_trim varchar(60)
    SET @string_to_trim = 'Four spaces are after the period in this sentence. '
    SELECT 'Here is the string without the leading spaces: ' + CHAR(13) +

    Here is the result set:

    (1 row(s) affected)
    Here is the string without the leading spaces: Four spaces are after the period in this sentence.
    (1 row(s) affected)

    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  11. ChrisFretwell New Member

    It took me a couple of reads through to see what you meant. Been there, done that. I used osql and my output was huge and full of wasted space. I wanted to put the results of a query to this format without creating a temp table. But then, because it was sql 2000, I discovered that I could use BCP with a query (queryout). And behold, my results were as I wanted (because I wanted the header, in my query I printed the 'headers' with the delimiter). Plus, without all the white space, my file was tiny!
    Since you're just putting out a table, it should work for you too.

  12. Twan New Member

    I seem to recall using the -O option for osql to get around some of this for an export I was doing. but I can't remember the exact details... it was to reduce the size of the resulting file though...


Share This Page