Automate data manipulation in Excel and transfer data to MySQL

Case summary

Carlo, an IT consultant, was working on a challenging, yet quite common task for his client. He had to create a system, which would generate management reports based on Excel files received from some 10-20 product-testing laboratories.

Previously, these reports were created in Excel – mostly manually – and then distributed to management and laboratories in PDF or print.

This set up lacked the necessary level of automation and flexibility. So, Carlo decided to find a way to extract data from lab files, arrange it following predetermined paths, and export data to MySQL database. Then, using a web interface, generate reports just the way he needed, e.g. charts with year-by-year comparisons, and share a link to the final report with all parties involved.

With tasks like this, one challenge is to minimize manual operations and time spent on preparing data. Another challenge is to export prepared data accurately to MySQL.

One way to automate the process is to create a custom VBA macro. However, with the large amount of required data manipulations, writing a macro from scratch would take unreasonably long. Besides, if you have ever tried integrating a macro with an external database, you know how tiresome manual synchronization can be. Any minor change in the process (say, if a different source table name) leads to endless adjustments to the script.

XLTools add-in, on the other hand, provided Carlo flexible tools to automate the entire process without VBA: extract, prepare and export data to MySQL database.

XLTools Automation is a powerful and a versatile tool. The sequence of all necessary data manipulations were coded in a single Excel worksheet and manual efforts reduced to a single button click.

Let’s take a look at a few operations automated along this process.

Step 1. Automate data extraction from Excel files

XLS files received from testing laboratories are standardized. They always follow the same template. We need to extract data from these source files and get it ready for further calculations.

• It retrieves data from external Excel or CSV files. You can collect necessary data in one place and then continue working with the entire data volume. Basically, this is your copy & paste tool.
• When you extract a simple range, it is output as a named table. Only data formatted as table can serve as data source for further operations, such as SQL queries in Excel.

For example, we need to extract this table with numeric codes assigned to laboratories:

Specify data range to extract:

• Workbook named “source.xls”. Note, that source files should be located in the same folder as your XLTools automation file.
• Range E:F on the “LABCODE” sheet.
• Skip 4 rows from the top, since the actual values begin in row 5.

Specify where to place extracted data:

• Name the new table “LabCodes”.

• Assign table headers “lab” and “stat”.

• Preserve cells format.

• Place the result on a new hidden sheet named “LabCodes”.

• Place the result on a new hidden sheet named “LabCodes”.

  We do not need the sheet to be visible – extraction is just an intermediate step before further manipulations.

Execute this command and you get a table “LabCodes” starting in cell A1 on a new hidden sheet “LabCodes”:

Just the same way, we extracted other necessary tables from lab files. Then we are set to work with the entire data set.

Step 2. Join two worksheets on a common column with an automated SQL Query

At the project start, Carlo mapped out how the specific records should be exported from Excel to MySQL. Following the map, we helped automate data preparation before export.

• SQL queries are the first choice, when you need to join two or more tables on a common column.
• XLTools allows you to run SQL SELECT queries directly on Excel tables and prepare data quickly.
• SQL allows you to execute multiple complex tasks in one go – you can rearrange and format data as you need with just a couple of queries.

For example, we wanted to merge these two tables on a common field and rename column labels. Also, we needed to process positive/negative values: column “sign” indicates that values in column “d” are either negative or positive.

Specify which fields to SELECT:

• Take column “lab” from table 2 and name it as “ic_code”.
• Take column “Nb” from table 1 and name it as “rank”.
• Take column “%” from table 1 and name it as “percent”.
• Take column “N°” from table 1 and name it as “lab_code”.
• Take column “sign” from table 1 and name it as “dev”. At the same time add a condition (CASE WHEN): if a value in column “sign” equals “-”, then multiply the value in column “d” by -1.
• Take column “Sd” from table 1 and name it as “s_dev”.
• Take column “D2” from table 1 and name it as “dist”.
• Take column “Method” from table 1 and name it as “method”.
• Find and select “Table1” FROM the workbook “source.xls”.
• LEFT JOIN tables ON values in columns “Nb” and “stat”.

Specify how to output the result of the query:

• Preserve cells format.
• Apply the name “data” to the new table.
• Place the result on a new sheet named “data”.

When you run the command, it will take a second to generate this table:

Step 3. Automatically export data from Excel to MySQL database

Carlo’s project was an inspirational challenge (THANK YOU)! While data extraction and SQL queries are long included in XLTools Automation, export from Excel to MySQL database is something new we have added.

Specify tables to export to the database:

• Take tables “zscore-fix”, ”zscore-pt”, ”outliers”, ”repeatability”, and ”data”.

Enter access details to MySQL database:

• Enter login details to your database: server, database, user name and password. This will allow XLTools to connect to your MySQL database.

Specify where to place the export summary report:

• Apply the name “ExportResults” to the table
• Place the result on a new sheet named “ExportResults”

When executed, this command will save data to MySQL and generate the following export summary report:

The summary displays how many records (rows) from each Excel table were exported to MySQL. If any errors during export, they are provided in the last column. By the way, you will get error description in the language of your database. Our customer Carlo works in Rome and errors will appear in Italian.

In this case, export failed for two records in the table “zscore-fix”. Apparently, some cells in this Excel table were blank – and missing data cannot be exported to MySQL. Now it’s easy to trace back and fix the errors.

Link the entire process to a single button

XLTools Automation also allows us to assign user buttons to these commands. In the examples above, we executed each command separately. We can also link the entire sequence of commands to custom buttons on XLTools tab.

E.g. Carlo’s commands are linked to three buttons. “Prepare & Export” triggers the entire process (extract, prepare and export data to MySQL) with just one button click:

Do you have an Excel process you want to automate?

What if XLTools does not (yet) have the functionality for your specific task? We’ll see if we can develop it for you. Our users have inspired many new features. Thanks to Carlo’s project, Automation now supports Export from Excel to MySQL.