Automate data manipulation in Excel and transfer data to MySQL [Case Study]

Credits to: Carlo Melis, IT consultant for an international non-governmental food products certification organization, Rome, Italy.

Features in focus: Automation without VBA (extract table, SQL query, export to MySQL). Supported in XLTools version 5.0.0.762 and higher.

 

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.

Process before automation

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.

Initial method was taking about 20 days of awful work. And we had to deal with errors copying huge amounts of data from one cumulative report in Excel into separate reports for every laboratory involved. The formats were very different, so it was not possible to automate the process within Excel. Also, the client needed charts where they could compare results collected months ago: this was possible only by transferring data to MySQL database.

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.

Process after automation

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. 

My client is very happy with the results because now we can do all the work in just about 2 hours instead of 20 days. XLTools helped us simplify work on the present XLS structure. The process is open for future changes – scripts are easy to modify. Now we transfer data into the database in minutes and then manage it as needed.

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.

That’s exactly what XLTools.ExtractTable command does:

  • 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:

extract table before

To do so, we write this XLTools.ExtractTable automation command:

XLTools.ExtractTable

WorkbookFile:

source.xls

Range:

LABCODE!E:F

SkipTopRowsCount:

4

ApplyTableName:

LabCodes

Headers:

lab,stat

PreserveFormat:

TRUE

OutputTo:

NewHiddenSheet[LabCodes]

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”.
    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”:

Extract table after

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.

For joining worksheets, we recommend XLTools.SQLSelect command. We love and frequently use it ourselves:

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

merge tables before

To do so, we used this XLTools.SQLSelect command:

XLTools.SQLSelect

SQLQuery:

SELECT
      tbl2.[lab] as [ic_code],
      tbl1.[Nb] as [rank],
      tbl1.[%] as [percent],
      tbl1.[N°] as [lab_code], 
      CASE WHEN tbl1.[sign] = '-' THEN -1 * tbl1.[d]  ELSE tbl1.[d] END as [dev],
      tbl1.[Sd] as [s_dev],
      tbl1.[D2] as [dist],
      tbl1.[Method] as [method],
FROM [source.xls].[Table1] tbl1
LEFT JOIN [LabCodes] tbl2 ON tbl1.[Nb] = tbl2.[stat]

PreserveFormat:

TRUE

ApplyTableName:

data

OutputTo:

NewSheet[data]

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:

SQL Query merge result

Note that all columns have new labels. Values in column “dev” are either positive or negative. XLTools.SQLSelect is an excellent tool to automate processing Excel tables. In complex data preparation for Carlo’s project, we relied on SQL queries almost entirely.

 

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.

Once we have prepared and formatted all necessary data, we create XLTools.ExportToMySQL command:

XLTools.ExportToMySQL

Tables:

zscore-fix,zscore-pt,outliers,repeatability,data

Server:

Server Name or Server IP address

Database:

The name of your database

Uid:

User name to access your database

Pwd:

Password to access your database

ApplyTableName:

ExportResults

OutputTo:

NewSheet[ExportResults]

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:

export to my sql 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:

export custom buttons

 

Do you have an Excel process you want to automate?

Please contact us! We want to help.

We’ll discuss your task and check if XLTools is up for it. Support with setting up customized Excel automation is included in our license.

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.

We have chosen XLTools because the team reacted quickly to our request. They were ready to discuss the options, help us with a custom solution and always available when needed.

Carlo Melis, IT Consultant

Leave a Reply

Your email address will not be published. Required fields are marked *