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

    This_Id|

    instead of

    This_Id.............|
    ?

    where '.' indicates a white space.
    mtmingus, Dec 10, 2003
    #2

  2. FrankKalis Moderator

    FrankKalis, Dec 10, 2003
    #2

  3. satya Moderator

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

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 10, 2003
    #2

  4. Jon M Member

    Jon M, Dec 10, 2003
    #2

  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.
    mtmingus, Dec 10, 2003
    #2

  6. FrankKalis Moderator

    FrankKalis, Dec 10, 2003
    #2

  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?

    Frank
    http://www.insidesql.de
    http://www.familienzirkus.de
    mtmingus, Dec 10, 2003
    #2

  8. Jon M Member

    Try this sql:

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

    Jon M
    Jon M, Dec 10, 2003
    #2

  9. FrankKalis Moderator

    FrankKalis, Dec 10, 2003
    #2

  10. Luis Martin Moderator

    How about this:

    RTRIM
    Returns a character string after truncating all trailing blanks.

    Syntax
    RTRIM ( character_expression )

    Arguments
    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
    varchar

    Remarks
    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.


    Examples
    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) +
    RTRIM(@string_to_trim)
    GO

    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
    Luis Martin, Dec 10, 2003
    #2

  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.

    Chris
    ChrisFretwell, Dec 10, 2003
    #2

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

    Cheers
    Twan
    Twan, Dec 10, 2003
    #2

Share This Page