Reading a report and fetching the data to load to a DB

[Titto]

Hi,

Need some advise and input.
I have a report file, need to read the file and get the information from the report and load it to a database.
which has header, address, date information and data information as rows.

What is the best way to get the report data to table format. Any help is appreciated!
here is sample report .

Code: Select all

  AB123    CITY            REPORTNAME                        PAGE:        1
 TIME: 15:18:07             FOR 1                             DATE: 12/10/04
  11111111  ADDRESS1  ADDRESS2 ADDRESS3
                   PERIOD   AMT   
              ------------ ------ 
 ABC           1,662   	   29.7  
 BCD             137   	    2.4  
 DEF             361       42.2  
 EFG              62        1.1  
  
              ------------ ------ 
 TOTAL          5,591      100.0  
 MRKUP            290        5.1  
 
 
 TOTAL W/O MKP      5,928          
 TOTAL MRKP           290          
 TOTAL              6,219          
 
 AB123    CITY            REPORTNAME                        PAGE:       2
 TIME: 15:18:07           FOR  1                            DATE: 12/10/04
  11111111  ADDRESS1  ADDRESS2 ADDRESS3
                   PERIOD   AMT   
              ------------ ------ 
 ABC           1,662   	   29.7  
 BCD             137   	    2.4  
 DEF             361       42.2  
 EFG              62        1.1  
  
              ------------ ------ 
 TOTAL          5,591      100.0  
 MRKUP            290        5.1  
 
 
 TOTAL W/O MKP      5,928          
 TOTAL MRKP           290          
 TOTAL              6,219  
  
 AB123    CITY            REPORTNAME                        PAGE:        3
 TIME: 15:18:07           FOR 1                                  DATE: 12/10/04
  *******  ADDRESS1  ADDRESS2 ADDRESS3
                   PERIOD   AMT   
              ------------ ------ 
 ABC           1,662   	   29.7  
 BCD             137   	    2.4  
 DEF             361       42.2  
 EFG              62        1.1  
  
              ------------ ------ 
 TOTAL          5,591      100.0  
 MRKUP            290        5.1  
 
 
 TOTAL W/O MKP      5,928          
 TOTAL MRKP           290          
 TOTAL              6,219     

I need the data to be in following format for each customer (1111111), break the read when it hits ********* as customer.

Code: Select all

=================================================================
1111111       CITY        PED       DATE     ADD1  ABC    BCD   DEF    EFG    TOTALS
==================================================================
 

which is best way to get this done. If any one does this kind of report to DB or file Please share.

Thanks

[kcbland]

Use the Sequential stage, declare 1 column of metadata that will read the entire line. In a transformer constraint, throw away all lines that contain anything that identifies the line as blank, header, title, or other formatting information. Output the remaining data rows to a Sequential file. Now, read that file back using fully qualified column definitions that parse the columns as fixed width fields. Alternatively, skip the intermediate landing to a sequential file and just manually parse each column using positional notation into discrete columns.

[ray.wurlod]

FieldStore() and Field() functions are a great way to implement what Ken has suggested, but make sure you understand them well. Use stage variables, then you can limit your output to happen only when a row is complete.

[Titto]

Thanks, Let me try and do this.