many reports in one file

randy · Post by **randy** » Mon Mar 12, 2012 7:10 am

Hi,

I have a file that contains 10 reports. I only need to load 3 of the reports. The data in one report will start in a different column the the other reports. I kind of need alittle help just getting started. I assume I need a stage variable for the report header. When It equals a report I need somehow start collecting the next X rows until the stage var changes. I am not really sure what the code for that stage variable would look like.

Thanks for any suggestions.
Randy

chulett · Post by **chulett** » Mon Mar 12, 2012 7:17 am

Can you post a (small) representative example of your input file? Also, clarify exactly what "load a report" means here.

randy · Post by **randy** » Mon Mar 12, 2012 10:05 am

In the sample file below, I have 4 reports:
SUMMARY CLOSING BALANCE
SALES TRANSACTIONS SUMMARY REPORT
RECONCILIATION REPORT
OTHER SALES SUMMARY REPORT

And I need to "load" reports 2 and 4 into separate Oracle tables.

Thanks
Randy

1 DATE RUN: 03/05/12
TIME : 14:29:36 SUMMARY CLOSING BALANCE PAGE 1
FEBRUARY 2012 PROD DATA
-
0 NET SALES FOR VALUE
CASH 777,012,529.52
DEBIT 82,578,134.52
CREDIT 98,919,522.96

1 DATE RUN: 03/05/12
TIME : 14:29:31 SALES TRANSACTIONS SUMMARY REPORT PAGE 2
FEBRUARY 2012 PROD DATA
- SUMMARY FOR SALES:
_______________________
- Department GROSS BONUS NET
0 70 192,314.23 7,874.23 184,440.00
76 243,087.93 0.00 243,087.93
78 0.00 0.00 0.00
57 0.00 0.00 0.00

TIME : 14:29:31 RECONCILIATION REPORT PAGE 4
FEBRUARY 2012 PROD DATA
- EU65 AUDIT REGISTER
BALANCE BALANCE DIFFERENCE
- VALUE SOLD 3,152,669.10 3,126,945.40 5,723.70
VALUE ADD 8,511,366.33 8,669,907.20 58,540.87-
0 DEBIT 8,197,663.90 9,038,757.40 41,093.50-
0 CREDIT 5,527,220.55 8,196,208.05 68,987.50-

0 OTHER SALES SUMMARY REPORT
0 NYC NJ TOTAL
0 CASH 16,810.85 1,090.00 1,900.85
DEBIT 10,978.35 1,265.00 1,243.35
CREDIT 14,206.70 1,455.00 1,661.70

chulett · Post by **chulett** » Mon Mar 12, 2012 12:33 pm

Still looking to understand what "load" means here, so simply repeating the word doesn't help. What I specifically mean is load how - what do your target tables look like, do you have to pivot rows to columns, that kind of thing. Or do you simply take each line as a string and stick that in a table, record for record?

randy · Post by **randy** » Mon Mar 12, 2012 1:20 pm

the oracle tables will match each of the reports. so if report 1 has 3 columns of data, the oracle table for that report will have 3 columns to match. If it makes it easier, I would be willing to work with oracle hold tables where I load one line from the report into a holding table, and then use PL/SQL to parse it.

My real issue is pulling in only the data for the 3 reports and ignoring the rest.

thanks
Randy

ray.wurlod · Post by **ray.wurlod** » Mon Mar 12, 2012 1:38 pm

How do you identify the various reports? The beginning of the report? The end of the report?

chulett · Post by **chulett** » Mon Mar 12, 2012 6:26 pm

I was thinking you could use the string "DATE RUN" to indentify the start of each report but that's only true for two of them. A line that starts with "TIME :" looked like another candidate but it's only good for three of the four. Perhaps look for the actual report name you need to source? Also not quite sure how you will know when you are done unless each report is a fixed number of lines, which it looks like they might be.

After reading them as one long string, looks to me like you'll need to parse each data line into component fields and then perform a vertical pivot on the results.

Kryt0n · Post by **Kryt0n** » Mon Mar 12, 2012 6:49 pm

You need something that identifies change of report... something that you can rely on. At that change, check to see if it meets one of your report names, if so, mark a stage variable to identify start of stream and send to the correct output stream (table dependent). The stage variable stays marked until the next change of report.

If SUMMARY CLOSING BALANCE and SALES TRANSACTIONS SUMMARY
REPORT were your relevant reports then make the stage variable = 1 when
SUMMARY CLOSING BALANCE and 2 when SALES TRANSACTIONS SUMMARY REPORT Else 0

Then a condition on each output stream to match report to target table.

As there seems to be superfluous data after the change of stream, have a subsequent stage to drop off non-data rows (if necessary), load rest to table.

How you determine a change of report is what you need to figure out.

randy · Post by **randy** » Tue Mar 13, 2012 1:26 pm

Kryton,

thanks, this was the kind of push I was looking for.

Randy