I have to transform data coming from several sources into a standard csv template. Each source provides either Excel (xlsx), text (csv) or xml files. The task consists in rearrange the data into a standard layout that can in turn be inserted into a database table (in MS Access). The main actions required are as follows:
- remove fields
- add fields and populate them with calculation based on other fields
- remove rows
- add to an xml node data coming form another node
These files are not big. They have a maximum of 30,000 lines. At the moment I am transforming them with VBA in the Access database where I eventually import the data but I would like to move this task outside the database. I did a brief research online and found that Python Pandas or Petl could be good libraries to transform data. Can you advise the following:
- is Python Pandas or Petl a good solution to the above problem? If yes which one is better suited for this task?
- if I use Python will that mean one solution per source?
- what interface can I build to allow the users to launch the Python transformation jobs? Is it possible to create a sort of service that runs every time the original files are dropped into a specific folder?