Post reply

Export non-uniform records to one Text file destination

  • December 15, 2006 at 6:55 pm

    #69168

    Hi all,

    I am trying to export data from 3 tables to one Text file.  I notice each time when I define the data transform task, it asks me the column mapping. 

    Also when I run them, it keep over written the previous file export.  Is it possible that I can attach data each time from each table to one single txt destination source?

    Or is my only choice is to export 3 table's content into 3 text files and then combine record content into one file with a VB script?

    If so, anyone has the script to combine content of files into one?

    We need to send this data result to a third party and the format they asked was one file, first line is one row header record which has 10 columns following by content of second table which has 80 columns (many records) and finally end with trailer record which has one record of 5 columns

    Thanks!

     

    DBA/OLAP Developer -
    San Francisco, CA

  • December 15, 2006 at 11:36 pm

    #678505

    If three tables has the same columns you can write a union query to copy the data to one text file..

    select * from table1

    union all

    select * from table2

    union all

    select * from table3

    OR

    You can copy each table to individual text file then merge three text files into one file.

     

    MohammedU
    Microsoft SQL Server MVP

  • December 18, 2006 at 12:50 pm

    #678781

    My tables do not have same numbers of columns.  So most likely I will need to do the text merge.

    Any idea where I can find the script to merge the files?  I need to be able to do this within my DTS package and programmatically.

    thanks!

    DBA/OLAP Developer -
    San Francisco, CA

  • December 18, 2006 at 3:38 pm

    #678821

    vbs scipt to append:

    set fso=createobject("scripting.filesystemobject")

    set nfl = fso.createtextfile("c:\\pathtoresultfile\resultfile.txt")

    set fl= fso.OpenTextFile("c:\pathtofile1\file1.txt")

    do while not fl.atendofstream

     nfl.writeline fl.readline

    loop

    set fl= fso.OpenTextFile("c:\pathtofile1\file2.txt")

    do while not fl.atendofstream

     nfl.writeline fl.readline

    loop

    set fl= fso.OpenTextFile("c:\pathtofile1\file3.txt")

    do while not fl.atendofstream

     nfl.writeline fl.readline

    loop

    nfl.close

    set nfl=nothing

     

     

    or you can use

    select '' as col1, '' as col2,'' as col3, ... '' as col100

    union all

    select col1,col2,col3,...col100 from table1

    union all

    select col1,col2,col3...col80,'' as col81,'' as col82,...'' as col100 from table2

    union all

    select col1,col2,col3...col5,'' as col6,'' as col7,...'' as col100 from table3

  • December 18, 2006 at 5:25 pm

    #678837

    That script works beautifully well!  Thanks!

    DBA/OLAP Developer -
    San Francisco, CA

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

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