VBA in Excel vs Access for data conversion

Question

We are upgrading our enterprise software and part of that effort I have to perform sales order (data) conversion from legacy to new software. I have moderate experience with macros/VBA in excel and I performed some basic data conversion but I am not sure if I should choose Access or stick with excel? Below are some high-level requirements -

1. This is one time data conversion during the software switch.
2. Less than 100k lines per tab and probably 5 to 10 tabs
3. Convert the legacy data to new data with help of mapping logic; for example customer numbers from legacy system will be converted to new customer numbers with help of cross reference data and similar to this there are other cross referencing.

4. It involves lot of looping of each sales order with respect to the cross referencing data.

5. Data layout has to formatted in specific way so that it can be uploaded to new software.

Let me know if any other details are needed.

Thanks!

Answer 1

The features you gain from using a database make this a Access hand down winner. However, mysql/mariadb might be an even better choice long term.

You will eventually need to learn SQL, but know it will make life so much easier.

A database can be connected to via ODBC in many different programming languages. Therefore you can use one of a dozen (probably more) languages to connect to it, and manipulate the data before it is uploaded to the new software.

update customer set customer_id = lookuptable.customer_id where lookuptable.old_customer_id=customer.old_customer_id;

Answer 2

Personally I would use neither - I would use python + pandas - you will have much more control over the format of the output data and probably no looping just simple mappings. You will be amazed at how little code you will need & you can work interactively, (in iPython) or by batch processing.