Split one data row into two

[treed]

Hi - My CSV file contains data that I need to split into two rows. Monarch is great for consolidating information into an orderly row, but I can't figure out how to create 2 rows from one.

At a high level, the file includes the following fields:
Account, Trans Date, Pay Ccy, Pay Amount, Rec Ccy, Rec Amount.
ABC, 12/31/09, USD, $100, JPY, Y10000
I need to create two rows:
ABC, 12/31/09, Pay, USD, $100
ABC, 12/31/09, Rec, JPY, Y10000

Any suggestions?
Thank you,
Tim

[Joe Berry]

Do you want the data to appear in the same file or in separate files? For example, do you want the data in a single file such as:
Rec 1, Part 1
Rec 1, Part 2
Rec 2, Part 1
Rec 2, Part 2

Or, do you want all part 1 records going to file A and all part 2 records going to file B? I believe both are possible, but the methods are different.

[treed]

Hi - Thanks for your reply. One file is better but not so important.

Ideally I'd like to do this in a single Monarch model since the way we've automated Monarch doesn't support project files. However if a project is required we could manually do it and deal with the automation aspect later.

Thanks,
Tim

[Grant Perkins]

Hi - My CSV file contains data that I need to split into two rows. Monarch is great for consolidating information into an orderly row, but I can't figure out how to create 2 rows from one.

At a high level, the file includes the following fields:
Account, Trans Date, Pay Ccy, Pay Amount, Rec Ccy, Rec Amount.
ABC, 12/31/09, USD, $100, JPY, Y10000
I need to create two rows:
ABC, 12/31/09, Pay, USD, $100
ABC, 12/31/09, Rec, JPY, Y10000

Any suggestions?
Thank you,
Tim

Tim,

I think I would be tempted to consider converting the csv file to a fixed width text file providing just the fields you need and giving control over the layout, especially field widths, and then exporting the result to a new file. Monarch could be used or, iirc, the Monarch Utility or possible another program.

Next step would be to read the new file in Monarch and use the Multi-Column processing feature to define 2 columns - one containing the Pay Ccy, Pay Amount fields and the other the Rec Ccy, Rec Amount fields.
Also make use of the COLUMN() function and the PAGE() and LINE() functions to create calculated fields.

Paint fields for the Account, Trans Date too, but not as part of the columns definition.

You should then get the separate records you want using the PAGE/LINE/COLUMN information to re-sort the extracted table into the required order.

There may be other ways but I can't think of an obvious approach in a single step from a csv file like yours without, perhaps, some manipulation using summaries - have not looked at that in detail yet. I would look at the above suggestion first if it was my project.

HTH.



Grant

[Data Kruncher]

After playing with this for a bit, I'd say that Grant's recommendation is probably the easiest way to split the records up, short of revising whatever system is creating the original csv file.

As to the summary approach that Grant also mentioned, I'd been focusing on that prior to reading his other recommendation without success. Monarch just doesn't split single record data up in that manner.

[treed]

Hi Grant/Joe/DK - Thanks for your help and suggestions. The CSV-to-fixed width conversion is an issue may be too manual a step for our support team to do.

I do have a point in the process where an incoming file can be duplicated and named uniquely, so I think I'm going to have to do that and have 2 xmod files creating 2 separate extracts.

I tend to work more with delimited data than fixed width so have never had a chance to learn about multicolumn processing. Pretty neat functionality.

Thanks,
Tim

[Grant Perkins]

Hi Grant/Joe/DK - Thanks for your help and suggestions. The CSV-to-fixed width conversion is an issue may be too manual a step for our support team to do.

I do have a point in the process where an incoming file can be duplicated and named uniquely, so I think I'm going to have to do that and have 2 xmod files creating 2 separate extracts.

I tend to work more with delimited data than fixed width so have never had a chance to learn about multicolumn processing. Pretty neat functionality.

Thanks,
Tim

Tim,

You can do the fixed width conversion using Monarch from the csv file input. Just read it into a table where you set the column widths (gives you control) and then export as a fixed width text file.

Then open another Monarch session that reads the newly created file and create the MCR based model from there.

Save both Monarch processes as Projects so that opening the project opens the input file and the associated model (you may need some smart stuff if the input file name is constantly changing).

Then create a batch file to run the entire 2 project process from a single click. To make life even easier Data Kruncher can help you with a Batch File Generator (http://excelwithmonarch.com/tips/monarch-batch-file-generator).

The cool thing about doing it this way is that you are in total control and so can set up stuff any way you want and change it, should the need or desire arise, without any reliance on others. Plus for very little work you can repeat the entire process with virtually no work at all - just don't tell anyone about that part!

All the best for 2010.


Grant

[Joe Berry]

Create the first model with the fields for the first record and a calculated field for RecNo(). Export the records as desired. Create a second model with the fields for the second record and a calculated field for RecNo(). Keep the field names the same so that this export can be appended to the same file as in part 1. Finally create a third model to import the appended file and sort by record number.

Hope this helps.

Joe

[OllyInMunich]

Hello Tim, hello everyone,

There's no need to use multiple models for this. Keep the original format but name the file as .txt or .prn. Open it as a report, not as a database, in Monarch.

Then define a multicolumn region of 2 columns, each 1 character wide, over on the right hand side in white space beyond the data.

Define a single line detail template that captures every line (say, use a nonblank trap in column 1), and grab one field that contains all the data, called "blob".

You should now have two rows of data in your table for every row of data in your report. Now it's straightforward to define a calculated field that lsplits(blob) on the comma, returning the right part based on the column() value.

HTH

Olly

[Joe Berry]

Olly,

What a unique solution. I just tried it. It is quick, easy and a one pass solution. It's a great day when you learn something new.

Thanks for sharing.

Joe