Post reply

newbie question

  • October 17, 2006 at 6:55 am

    #76601

    Hi guys

                I am outputing my data in a txt file through a dts job. I want to make it more readable. how do i preprocess it, in order to make it more readable. I tried the options in the destination file task, but it still is not the way i want.

    Here is a quick snapshot...

    |2006-10-13 17:31:40|3|2667|E:\MSSQL\BACKUP\ABSSuite_Prodtest.BAK|1

    |2006-10-13 17:34:21|3|3435|D:\MSSQL\BACKUP\ABSSuiteCurrent.BAK|3

    |2006-10-13 17:36:28|3|2712|D:\MSSQL\BACKUP\ABSSuiteD.BAK|2

    |2006-10-13 17:38:53|3|2987|D:\MSSQL\BACKUP\ABSSuiteD2.BAK|2

    |2006-10-13 17:40:34|3|1951|D:\MSSQL\BACKUP\ABSSuitePDF.BAK|2

    This is how it looks like........

    and i want it to look more alligned...somewhat like it looks in the query analyser.

                                                        Thanks.............

  • October 17, 2006 at 10:40 am

    #666294

    Why not export the data to Excel?

  • October 17, 2006 at 11:29 am

    #666303

    thats wht i told my manager...but he insists he want a txt file.......

  • October 17, 2006 at 2:56 pm

    #666359

    Instead of using a delimited destination file, make it a fixed-length field file.

    Double-click on the destination file object, click the Properties button, and select the Fixed field radio button.

  • October 17, 2006 at 11:43 pm

    #666400

    Export it as csv, will be more readable and ur manager will see it like a txt file

  • October 18, 2006 at 2:28 am

    #666415

    Hi,

    Why not set the properties in Text File Properties to:

    File type ANSI

    Row delimeter CRLF

    Column delimeter to TAB

    Text qualifier none

     

  • October 18, 2006 at 2:48 pm

    #666651

    Create a view that formats each column the way you want, then export from the view in DTS.  Liberal use of STR, CAST, CONVERT, REPLACE, and STUFF should do the job.

  • October 19, 2006 at 7:03 am

    #666771

    Ok

       let me give it a shot and see if it works...

  • October 20, 2006 at 9:02 am

    #667091

    No luck guys.....I am still tryin.......this is the output format i want.

    column1                                 column2                       column3

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

    data.....                                  data......                      data.........

    data.....                                  data......                      data..........

    Is there any way i have a formatted preprocessed file....and just have the output file point to it......

  • October 20, 2006 at 3:01 pm

    #667224

    Pankaj,

    Tell ur manager that if you want that files to be used by other process then you need to have it delimited.

    If you really want it the way he wants. Try this.

    Declare @s_SQL varchar(8000)

    select @s_SQL = 'pub_id pub_name                                 city                 state country                        ' + Char(10)

    select @s_SQL = @s_SQL + '------ ---------------------------------------- -------------------- ----- ------------------------------ '

    select @s_SQL as Col1

    Union all

    select left((isnull(pub_id,'') + space(7)),7) + left((isnull(pub_name,'') + space(41)),41) + left((isnull(city,'') + space(21)),21)

      + left((isnull(state,'') + space(6)),6) + left((isnull(country,'') + space(30)),30)

    from publishers

    Thanks

    Sreejith

  • October 24, 2006 at 10:33 am

    #667641

    Thanks a lot Sreejit that helped a lot..I am gettin a readable output file now...

Viewing 11 posts - 1 through 10 (of 10 total)

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