Forum Discussion
Solved
Help parse Accpac journal entries with multiple rows
I have to get hundreds of csv files, each containing thousands of journal entries, in Accpac format, into a table.
In Accpac format, each journal has one Header row (G) and then multiple entries below (each of which consist of a Detail row (D) and an Analytical row (A)).
Each csv file contains many journals below each other.
Here is a sample showing two journals (and only the first 5 of many columns).:
| Column1 | Column2 | Column3 | Column4 | Column5 |
| G | GENAJ | PR2-3878 | null | null |
| D | 1 | 1 | 3424.57 | USD |
| A | 1 | null | ||
| D | 2 | -1 | 3287.72 | USD |
| A | 1 | null | ||
| G | GENAJ | RR2-4006 | null | null |
| D | 1 | -1 | 91.52 | USD |
| A | 1 | null | ||
| D | 2 | 1 | 91.52 | USD |
| A | 1 | null | ||
| D | 3 | 0 | 0 | USD |
| A | 1 | null | ||
| D | 4 | -1 | 10809.62 | USD |
| A | 1 | null | ||
| D | 5 | 1 | 10697.28 | USD |
| A | 1 | null | ||
| D | 6 | 1 | 112.34 | USD |
| A | 1 | null |
I would like the final result to:
1. Have the Analytical line next to its corresponding Detail line
2. Repeat the Header info next to its corresponding Detail and Analytical lines.
Like this:
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | Column12 | Column13 | Column14 | Column15 |
| G | GENAJ | PR2-3878 | null | null | D | 1 | 1 | 3,424.57 | USD | A | 1 | null | ||
| G | GENAJ | PR2-3879 | null | null | D | 2 | -1 | 3,287.72 | USD | A | 1 | null | ||
| G | GENAJ | RR2-4006 | null | null | D | 1 | -1 | 91.52 | USD | A | 1 | null | ||
| G | GENAJ | RR2-4007 | null | null | D | 2 | 1 | 91.52 | USD | A | 1 | null | ||
| G | GENAJ | RR2-4008 | null | null | D | 3 | 0 | - | USD | A | 1 | null | ||
| G | GENAJ | RR2-4009 | null | null | D | 4 | -1 | 10,809.62 | USD | A | 1 | null | ||
| G | GENAJ | RR2-4010 | null | null | D | 5 | 1 | 10,697.28 | USD | A | 1 | null | ||
| G | GENAJ | RR2-4011 | null | null | D | 6 | 1 | 112.34 | USD | A | 1 | null |
I know how to get the csv files in and the data above is dummy data.
I appreciate your help.
