Post reply

Several text files consolidate in one

  • December 6, 2005 at 2:13 am

    #88828

    I need some assistance if possible. I have to run some flat files with data coming from differnt tables and different columns (header and detail kind of thing, but this one has 8 sections. can not be created using any reporting tool (reporting services or crystal, etc) It has to be flat text files. The processing destination does not accept any other kind of files other than flat text.

    How can insert the results to the one text file?

    Let's say the first file reads:

    H 20051206    4     Development      00001245      00004859

    the next one reads:

    P 20051206091524    Memphis      TN     38118

    thir one is the detail and have more that one line as followo:

    T Jack Frost         345 19670528

    T Howard Huges    926 19351231

     

    As you can see the columsn are very disimilar and do not have the same length or data type. Any help is greatly appreciated. Thank you  By the way I use SQL Server 2000 sp 3(a) and the creation of the files is not a big of a deal is how can I insert all my files into one! Thank you for you help!!! 

  • December 6, 2005 at 2:33 am

    #607885

    I would take these files and doctor them to make them suitable for my needs in a language like VB (by inserting NULL or empty strings etc.) to make it compliant with the table and then import it into the tables.

    Using t-SQL for this is very difficult for these kind of jobs when it can be done easily in VB ( or any other scripting language like Perl etc..) as we are talking about files here and not databases or tables.

     

     

  • December 6, 2005 at 7:10 am

    #607922

    1.

    Create a table with single varchar column, concatenate (and cast where necessary) columns from each table into the varchar column. Extract the table into text file via DTS or OSQL (in DOS Prompt)

    2.

    Use OSQL (in DOS Prompt) to output and append each table to a single file, eg

    osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3,col4,col5 FROM [datebase].dbo.[table1]" | FINDSTR /c:" " > outputfile.txt

    osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3 FROM [datebase].dbo.[table2]" | FINDSTR /c:" " >> outputfile.txt

    osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3,col4 FROM [datebase].dbo.[table3]" | FINDSTR /c:" " >> outputfile.txt

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 6, 2005 at 7:50 am

    #607933

    Thank you guys. I will give it a try using OSQL. Cool, I think that sove my problem!! If not, I will come back a ask a little bit more. Thanks anyway

  • December 7, 2005 at 11:26 pm

    #608333

    There is also a way of storing data about the different formats ("versions") of the files in a table.

    All you have to do then is to ensure that your DTS can identify which file is in which format.  We tag a V1 or V2 etc. to the filename for e.g. FileDataV1.txt, TextDatav2.txt.

    You can then use Active-X and gv's to check this from the dts and then to use the specific import spec as set out in the mentioned table. 

    This DTS can then be looped to import multiple files with different layouts from a folder and move them to a "Processed" folder.

    <hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

    Roelof
    <a href='http://' class='authorlink' target='_blank'></a>

    --There are only 10 types of people in the world. Those who understand binary, and those who don't.

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

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