Post reply

Text file to Text file tranformation help needed

  • September 7, 2004 at 12:25 am

    #360858

    I'm looking for some ideas on how to start this one.  I'm fairly new to DTS, but reasonably competent in T-SQL.  I want to use a DTS package because of it scheduling and logging, and because I have several other packages which run quite well.

    OK, here goes:

    I have a csv text file which contains multiple orders, with each line containing the header information and the detail information for the order. Looks something like this:

    Customer1, Line 1, Date, Order#, Part#, Quantity

    Customer1, Line 2, Date, Order#, Part#, Quantity

    Customer2, Line 1, Date, Order#, Part#, Quantity

    Customer2, Line 2, Date, Order#, Part#, Quantity

    What I need to output is another csv text file which breaks the order into header and detail lines.  So the above would become:

    HDR, Customer1, Date, Order#

    DTL, Line 1,Part#, Quantity

    DTL, Line 2,Part#, Quantity

    HDR, Customer2, Date, Order#

    DTL, Line 1,Part#, Quantity

    DTL, Line 2,Part#, Quantity

    The output would be better in one file, but could be seperated into 2 files if needed.

    My idea is to import the whole file into a table, perform a group by query to get the header details, and then a select query to get the details.  The problem is how to assemble them in the right order.  All the header records at the top and all the detail records at the bottom won't work.

    Any ideas?

    Thanks,

    Mark

  • September 8, 2004 at 4:32 am

    #522085

    No need to even involve DTS or SQL.

    Using VBScript, open two TextStream objects (one for input the other for output). Read a line from the input stream, write your first header and detail lines. Read another line and check if its for the same customer, if so right out another detail line otherwise write out the header and detail lines. Rinse and repeat till you get to the end of the file.

    --------------------
    Colt 45 - the original point and click interface

  • September 8, 2004 at 8:28 pm

    #522222

    Phil,

    Thanks for the reply.

    I'm not completely skilled with VBScript, but I think I could figure it out.

    I'd still like, if possible, to create a DTS package because of the logging and the scheduling features, and because I have other packages involed in the process which are also DTS.

    Could I use an activex component in DTS to accomplish this in the same way? If I use in and out text files, and a looping script in ActiveX would this sound reasonable.

    Thanks,

    Mark

Viewing 3 posts - 1 through 2 (of 2 total)

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