Text file to Text file tranformation help needed

Question

Text file to Text file tranformation help needed

Mark Hawkins

Old Hand

Points: 343
More actions
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

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

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

Mark Hawkins Old Hand Points: 343 More actions · Answer 2

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