9 Replies Latest reply: May 15, 2014 10:06 AM by Joe Berry RSS

    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

        • Split one data row into two
          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.

            • Split one data row into two
              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

            • Split one data row into two
              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[/quote]

               

              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

                • Split one data row into two
                  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.

                    • Split one data row into two
                      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

                        • Split one data row into two
                          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[/quote]

                           

                          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 [URL="http://********************/tips/monarch-batch-file-generator"]Batch File Generator[/URL].

                           

                          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

                            • Split one data row into two
                              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

                                • Split one data row into two
                                  Olly Bond

                                  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