What is the best approach to combine 2 different accounting system CSV file into 1 ?

[admin11]

Hi All

I like to seek advise from the expert here on above mentioned :-

Condition :-

Raw data CSV must be loading by folder.

Because :-

Company A using SAP have 5 subsidary company

Company B using SYSPRO have 5 subsidary .company

As after this problem statement settle , i can add all 10 CSV file into folder , PBI will be able to append all 10 CSV file with 2 different data structure into 1 standard formaT

Hope some one can give me advise which is the fast way to solve this problem ?

Enclosed my raw data file company A =GL_TA.CSV :-'

https://www.dropbox.com/s/q9ih56a3ha9mc5e/GL_TA.csv?dl=0

Date Model as below :-

GL Code

GL Description

Type

Month

Year

Posting Date

C/D

Reporting Code

Enclosed my raw data file company B = GL_PM.CSV :-

https://www.dropbox.com/s/s8mfyjm2ghh9b2v/GL_PM.csv?dl=0'

Data Model as below :-

GL Code

GL Description

Posting Date

Transaction Type

Document No.

C/D

Cumulative Balance (LC)

Debit(LC)

Credit(LC)

Customer/Vendor

Code Customer/Vendor Name

Hope some one can advise me the appraoch ?

Paul Yeo

.

:-

[PhilipTreacy · Accepted solution]

@admin11 

Hi Paul,

If the CSV's from different systems have different structures, then you'll need to do different transformations on them.  Which means you'll need to load the files in separate queries.

If you can distinguish the files from each system in some way, for example by their naming convention, then you can leave all the files in the same folder, create 2 From Folder connections and then filter the files acordingly.  Files from system 1 into query 1, files from system 2 into query 2.  Then do the transformations and append the data.

If you can't distinguish the CSV's from different systems then you'll need to store them in defferent folders.  You'll still need to use 2 different From Folder connections and do the transformations separately before appending.

regards

Phil

[admin11]

@PhilipTreacy 

Thank you so much for your sharing .

i Have try :-

i load all 4 CSV file from SYSPRO system to below folder  :-

https://isdnholdings.sharepoint.com/sites/FTGL/

And i load 4 CSV file from SAP into below folder :-

https://isdnholdings.sharepoint.com/sites/FS_GL/

And after load above 2 file , i append both table 1 one. it work fine now.

I will use this approach for merge the rest of table SO , INVC , AR etc . Thank you very much.

Paul.