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
  • etc.

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.