7 Replies Latest reply: May 15, 2014 10:03 AM by Steve Caiels RSS

    Need help with CSV file

    MonarchUser _

      I have this CSV file where the data is displayed differently from other CSV files. Here is the report:

       

       

      MBS POOL NUMBER               ,050986,250005,250094,253389

      POOL DEFICIENCY AMOUNT         :,98.27,65.65,1.67,320.64

      LESS:  REJECTED LIQUIDATIONS  :,0.00,0.00,0.00,0.00

              REJECTED CURTAILMENTS   :,0.00,0.00,0.00,0.00

              REJECTED LAR DUE TO        :,0.00,0.00,0.00,0.00

               PMT/NOTE RATE CHANGE   :,0.00,0.00,0.00,0.00

              OTHER REJECTIONS            :,0.00,0.00,0.00,0.00

              INVALID LOANS                  :,0.00,0.00,0.00,0.00

              REVERSED LIQUIDATIONS     :,0.00,0.00,0.00,0.00

              REVERSED CURTAILMENTS    :,0.00,0.00,0.00,0.00

              SOFT REJECTS                   :,0.00,0.00,0.00,0.00

      DIFFERENCE                              :,98.27,65.65,1.67,320.64

       

      I want to display all the fields listed to the left of the report in columns as shown below:

       

      POOLLND     POOLDEFAMT     REJLIQ     REJCURT     REJLAR     REJOTHER     INVLOANS     REVLIQ     REVCURT     SOFTREJ     DIFF

      050986     98.27                  0.00     0.00       0.00       0.00                      0.00                  0.00     0.00      0.00      98.27

      250005     109.16          0.00     0.00     0.00     0.00          0.00                  0.00       0.00        0.00      65.65

      250094     103.52          0.00     0.00     0.00     0.00          0.00          0.00       0.00        0.00      1.67

      253389    320.64                0.00       0.00       0.00       0.00                    0.00                    0.00       0.00        0.00      320.64

       

      I might need floating trap for this but not quiet sure how to start. Any help would be appreciated.

        • Need help with CSV file
          Nick Osdale-Popa

          For trapping,

          Select your first line, set floating trap, enter your commas as your trap line, then hit the button for auto-define fields - this will tell Monarch length of each field.

           

          For display, you are going to use your first field as Key item and display it using the Across option. The tricky part is that a 'down' display is required.

            • Need help with CSV file
              MonarchUser _

              Thanks for replying to my post.

               

              I am thinking I will use 4 models to capture all the 4 (050986,250005,250094,253389)

              columns. I don't use Monarch often so it's going to take me  sometime to figure this out.

                • Need help with CSV file
                  Grant Perkins

                  MU,

                   

                  Just to clarify something.

                   

                  Your sample report seems to include 2 records each having 12 lines.

                   

                  Youe output has 3 lines. Is it safe to assume that the third line realts to another 12 line record not included in the sample set?

                   

                  Do all records in the report have 12 lines?

                   

                  If both answers are YES and you only need the first value (or four) in each line I would suggest the following.

                   

                  Create a 12 line sample starting with

                   

                  MBS POOL NUMBER ,050986,250005,250094,253389

                   

                  and use

                   

                  MBS POOL NUMBER

                   

                  as the trap.

                   

                  For each field, named for the field title at the start of the line adapted as per your output example, simply 'paint' a field across the entire width of the report for each of the 12 lines. This will be a CHARACTER field type.

                   

                  Next, for each of the field s that will now appear in the table, create a CALCULATED field using the LSPLIT() function (see the Help for how to use it) that splits up the TEXT String using the comma character and select just the second of the split parts. (Actually if you only need the first value yo uneed only tell Monarch that the field has to be split into 3 parts but making it any value greater than 2 will be fine.)

                   

                  LSPLIT(ExtractedFieldName,3,",",2)

                  /code

                  should do that for you.

                   

                  That will give you a Character String.

                   

                  To make the resulting field NUMERIC use the VAL() function.

                   

                  So

                  VAL(LSPLIT(ExtractedFieldName,3,",",2)

                  /code

                   

                  and make the Calculated field type NUMERIC should give what you need.

                   

                  If extraneous spaces become a problem check out the TRIM() function variants.

                   

                  I could not identify which version of Monarch you are using but I think this should work for any releases in the past 10 years or so.

                   

                  If you need other fields as well we will need to extend this a little.

                   

                  If you have Monarch V7 or later it may be worth considering starting out by re-creating the input file (Using Monarch or Monarch Utility or in earlier versions some separate utility programs) to create a COLUMNAR rather the csv report. That would allow different approaches. From V7 Pro onwards the Multi Column Report feature looks like it would work well. Better than 4 models and 4 extractions I would think.

                   

                  HTH.

                   

                   

                  Grant

                    • Need help with CSV file
                      MonarchUser _

                      I updated the sample report to make it simple. I need 4 rows, one for each MBS pool number with all the 12 columns.

                       

                       

                      I tried using floating trap with auto define fields but then it picks everything on the report. I want my model to pick just the data for first MBS pool number (050987) then for other 3 MBS pool numbers and then display them in a row.

                        • Need help with CSV file
                          Grant Perkins

                          Right.

                           

                          I would suggest that you convert the csv file to a fixed width format to make life easier. You could do this with Monarch  (outlined below) or Access of Excel.

                           

                          You will benefit from a version of Monarch which includes Multi Column Region processing. V7 onwards. Pro version.

                           

                          First read the file as a CSV database. Set the field widths to be adequate for whatever may come up (if this is more than a one-off process) and then export to a new fixed width file.

                           

                          This will give you a report that is easier to to moel using MCR.

                           

                          Read the new report as a report, NOT a database.

                           

                          Create the columns. Make the MBS POOL NUMBER row a 'header' row and trap it as an append with the MCR set.

                           

                          Arrange the order of the fields in the summary as you require them to be.

                           

                          That should be about it.

                           

                          HTH.

                           

                           

                          Grant

                            • Need help with CSV file
                              MonarchUser _

                              Reading the CSV file as database and exporting it as a fixed length format file did work. I was able to capture all the fields I needed. Thanks a lot.

                               

                              Now the issue is that these CSV files are downloaded (about 30-40) every month. We want to bring all this data into an access database for further analysis using automated process. I have most of the vba code but I am not sure how to:

                               

                              1.     Open the multiple CSV files as monarch database through vba code

                              2.     Set the Lines to skip at start to 6.

                              3.     Replace nulls with default values.

                              4.     Select all the columns to be imported.

                              5.     Export the database as a fixed length text file.

                               

                              I appreciate all the help I have received in this forum.

                                • Need help with CSV file
                                  Steve Caiels

                                  [SIZE=3]Hi,[/SIZE][/FONT]

                                  [SIZE=3]For point 1 : Data Pump would allow you to process multiple csv files, either as a group or individually.  Or you could add them together in VB so that Monarch only has one csv to deal with.  You may need to filter out duplicate field title rows though. /SIZE[/FONT]

                                  [SIZE=3]Monarch V10 will easily allow you burst the export back out into multiple fixed width files if you want to.[/SIZE][/FONT]

                                  [SIZE=3]For points 2 to 4 : These are saved either in the model or project, so there is no need to set them via vb.  You can just save the project and call the entire project via vb using the SetProjectFile() method.[/SIZE][/FONT]

                                  [SIZE=3]For point 5 : A project export will hold the definition for exporting to a fixed width text file.  The RunAllExports() or RunExport(name) methods will do the trick.[/SIZE][/FONT]

                                  [SIZE=3]Have you seen the programmers reference guide on /SIZE[/FONT][URL="http://www.datawatch.com/_support/downloads_updates.php"][FONT=Calibri][SIZE=3][COLOR=#800080]http://www.datawatch.com/_support/downloads_updates.php[/COLOR][/SIZE][/FONT][/URL][FONT=Calibri][SIZE=3] ?[/SIZE][/FONT]

                                  [SIZE=3]Regards,

                                  Steve. /SIZE[/FONT]