vigushpri2030
New Member
- Joined
- Apr 26, 2016
- Messages
- 5
Hello all,
I am working on this particular file where data is generated in a .txt format. I have managed to convert the file to .xlsx and get rid of some unnecessary columns. Once I have structured the data, I need to populate it into a defined format.
Raw Data:
<colgroup><col><col></colgroup><tbody>
</tbody>
The medium and control code keeps changing while the below field names (such as OVERDUE AMOUNT; OVERDUE 0 DAY) are always unique. Corresponding values changes. There are about 500 such medium and control entries in the excel
I need to populate it in the below format:
Required Format:
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
I am working on this particular file where data is generated in a .txt format. I have managed to convert the file to .xlsx and get rid of some unnecessary columns. Once I have structured the data, I need to populate it into a defined format.
Raw Data:
| D05B - XYZ Company | XYZ Company FILE DATE 05/31/16 PAGE 1 |
| 865 - MEDIUM Q06 - TYPE | OVERDUE STATISTICS |
| 0C02 - CONTROL CODE | |
| PAYMENT OVERDUE INFORMATION | |
| A. OVERDUE AMOUNT | |
| 1. OVERDUE 0 DAY | 000,000,000.00 |
| 1.1 MINIMUM/PARTIALLY REPAYMENT ACCOUNT | 000,000,000.00 |
| 2. OVERDUE 1-30 DAYS | 000,000,000.00 |
| 3. OVERDUE 31-60 DAYS | 000,000,000.00 |
| 4. OVERDUE 61-90 DAYS | 000,000,000.00 |
| 5. OVERDUE 91-120 DAYS | 000,000,000.00 |
| 6. OVERDUE 121-180 DAYS | 000,000,000.00 |
| 7. OVERDUE ABOVE 180 DAYS | 000,000,000.00 |
| B. OVERDUE ACCOUNT NUMBERS | |
| B.1 CATEGORIZED BY OVERDUE DAYS | |
| 1. OVERDUE 0 DAY | 000,000,000 |
| 1.1 MINIMUM/PARTIALLY REPAYMENT ACCOUNT | 000,000,000 |
| 2. OVERDUE 1-30 DAYS | 000,000,000 |
| 3. OVERDUE 31-60 DAYS | 000,000,000 |
| 4. OVERDUE 61-90 DAYS | 000,000,000 |
| 5. OVERDUE 91-120 DAYS | 000,000,000 |
| 6. OVERDUE 121-180 DAYS | 000,000,000 |
| 7. OVERDUE ABOVE 180 DAYS | 000,000,000 |
| B.2 CATEGORIZED BY OVERDUE AMOUNT | |
| 1. OVERDUE 0 | 000,000,000 |
| 1.1. MINIMUM REPAYMENT AMOUNT | 000,000,000 |
| 2. OVERDUE AMOUNT(1-1000) | 000,000,000 |
| 3. OVERDUE AMOUNT (1001-5000) | 000,000,000 |
| 4. OVERDUE AMOUNT (USD 5000) | 000,000,000 |
<colgroup><col><col></colgroup><tbody>
</tbody>
The medium and control code keeps changing while the below field names (such as OVERDUE AMOUNT; OVERDUE 0 DAY) are always unique. Corresponding values changes. There are about 500 such medium and control entries in the excel
I need to populate it in the below format:
Required Format:
| Medium | Control Code | OVERDUE AMOUNT | OVERDUE ACCOUNT NUMBERS | ||||||||||
| 1. OVERDUE 0 DAY | 2. OVERDUE 1-30 DAYS | 3. OVERDUE 31-60 DAYS | 4. OVERDUE 61-90 DAYS | 5. OVERDUE 91-120 DAYS | 6. OVERDUE 121-180 DAYS | 7. OVERDUE ABOVE 180 DAYS | 1. OVERDUE 0 DAY | 2. OVERDUE 1-30 DAYS | 3. OVERDUE 31-60 DAYS | 4. OVERDUE 61-90 DAYS | 5. OVERDUE 91-120 DAYS | 6. OVERDUE 121-180 DAYS | 7. OVERDUE ABOVE 180 DAYS |
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
