Multiple overlapping detail lines
Altair Employee1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
A: FJHF00XX1 L HRHRD Compan 18/10/11 84 10 9.45 11100.00 11100.00 11/111/11 11/11/11
B: 11565.00 11196.18 0.00 11101.56
C: 11151.30 1113.61 11157.69 0.00
11101.56 11101.56
E: 1119.63
A: FJHF00XX1 L HRHRD Compan 18/10/11 84 10 9.45 11100.00 11100.00 11/111/11 11/11/11
B: 11565.00 11196.18 0.00 11101.56
C: 11151.30 1113.61 11157.69 0.00
11101.56 11101.56
E: 1119.63
Each A1 to E22 is an individual record, I have been able to grab the data by specifying the A: line as the detail and B to E: as footers which means I have a very long row for each record/
How can I get the data into excel as it appears on the report? Multi line is not working as rows over lap with above and below on some records? Many thanks
Answers
-
What format is the data in to start with (Excel, CSV, Delimited Text, PDF, etc.)? There are a couple of things you can try but it depends on the format the source data you are using to model.
0 -
Altair Forum User said:
What format is the data in to start with (Excel, CSV, Delimited Text, PDF, etc.)? There are a couple of things you can try but it depends on the format the source data you are using to model.
ah sorry, it is a .txt file no special delims.
0 -
Also, can you tell us what version of Monarch you are using? This could impact the answer you might receive.
0 -
This response should be true of at least v13 or better of Monarch Classic. I am also making an assumption that each record contains 5 rows. I also removed the header and column info (A-E, 1-22), but it shouldn't matter for this discussion.
Select 5 lines from the line selection window and create a new detail template:
Once you set an appropriate trap line, you can select mulitple fields on different lines. You cannot "draw" a field to contain multiple lines, but you can you some advance template techniques to get past that problem. If your data is not consistently spaced where you can draw the fields you want to capture, another possibility would be to create 5 long fields (1 for each row) and then use formula fields to break them down to their individual records.
Keep in mind you are limited to 512 fields. I know this is not too specific but I hope it points you in the right direction. If you need more info or clarification let me know. Another good resource is the built in help file for monarch:
0 -
Altair Forum User said:
This response should be true of at least v13 or better of Monarch Classic. I am also making an assumption that each record contains 5 rows. I also removed the header and column info (A-E, 1-22), but it shouldn't matter for this discussion.
Select 5 lines from the line selection window and create a new detail template:
Once you set an appropriate trap line, you can select mulitple fields on different lines. You cannot "draw" a field to contain multiple lines, but you can you some advance template techniques to get past that problem. If your data is not consistently spaced where you can draw the fields you want to capture, another possibility would be to create 5 long fields (1 for each row) and then use formula fields to break them down to their individual records.
Keep in mind you are limited to 512 fields. I know this is not too specific but I hope it points you in the right direction. If you need more info or clarification let me know. Another good resource is the built in help file for monarch:
I cant seem to select multiple lines, is that because I am on version 11?
0 -
Altair Forum User said:
I cant seem to select multiple lines, is that because I am on version 11?
No, multi row templates have been around since the very early days.
What are the 11/111/11 entries in your dummy data representing?
Are all of the columns individual or do they go in repeating groups?
Are the real "detail" records (in Monarch terms) the A rows or the B,C,D,E rows as single rows?
In other words, if your report file has Column Titles as a header line above the data rows does it have one row of column headers or 4/5 separate rows each with different column names?
0 -
Altair Forum User said:
No, multi row templates have been around since the very early days.
What are the 11/111/11 entries in your dummy data representing?
Are all of the columns individual or do they go in repeating groups?
Are the real "detail" records (in Monarch terms) the A rows or the B,C,D,E rows as single rows?
In other words, if your report file has Column Titles as a header line above the data rows does it have one row of column headers or 4/5 separate rows each with different column names?
11/111/11 typo, it is a data dd/mm/yy
All rows are detail A to E: There is a key at the top of the repor
I was able to trap all fields in one template as suggested but I still ended up with one really long row a1 a2 ... e21 e22 (which is actually okay as each row is one complete record) but I would still like to get it as per the report like below
a1 .... a22
b1... b22
...
e1... e22
a1....a22
etc
0 -
Altair Forum User said:
11/111/11 typo, it is a data dd/mm/yy
All rows are detail A to E: There is a key at the top of the repor
I was able to trap all fields in one template as suggested but I still ended up with one really long row a1 a2 ... e21 e22 (which is actually okay as each row is one complete record) but I would still like to get it as per the report like below
a1 .... a22
b1... b22
...
e1... e22
a1....a22
etc
Billy, I think we probably would benefit from knowing the ultimate purpose for the extracted data.
Based on the information above each line needs to be treated as a detail line - single row.
However your A: row, or at least part of it, is completely different at the start and is more like a record header.
So are you just seeking to replicate the existing report to another report with exactly the same layout?
Or to dump it into Excel with the same layout?
Nothing more?
Clearly the first line will need different field columns to the rest of the report and, having guessed that the 11/111/11 field might be dates, are the fields in each column in each row (other than the row one anomaly) all of the same data type? If not, will extracting everything into Character based fields (i.e. TEXT) be suitable?
Is the .txt file just text in columns or is it a csv format?
As a guess I wonder if this is actually a single row record that has been wrapped into multiple rows to fit into a standard width "paper" report. It looks a bit strange, based on how it looks after posting - which might not be accurate - compared to most report sources.
EDIT: What I really meant there is could it be a former csv type file that have been re-presented as a fixed width format and then wrapped to a particular line width.
Grant
0
