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.
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
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.)
should do that for you.
That will give you a Character String.
To make the resulting field NUMERIC use the VAL() function.
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.
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.
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.
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.
[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]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]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]