How can we transform data in an Excel file with a complex format into a structured data set and write it to the database?

A lot of coding work is needed if you try to get it done with a programming language. Of course you can use API to read an Excel worksheet and cells. You still need to write code to get data from the desired cells iteratively, store it in a data set object and then write and execute a SQL query to write data to the database. Another problem is that the program becomes invalid when you handle an Excel file with a different format next time. So you have to do the job all over again.

There would be no more hassle and problem if you choose esProc SPL to do the read-write thing. Only 3 lines of code are sufficient.

Look at an example. Below is Excel file item.xlsx. We want to read data from it to store in a data set.

Below is SPL script:

You can handle other post-reading operations, such as sorting, grouping and summarizing, easily with SPL too. Read SPL Grouping to find more examples.

It’s also convenient to handle various Excel data query scenarios with SPL:

1. Query Excel files of all formats, such as free format spreadsheet, crosstab, main table & subtable.

2. Export and append Excel data to another Excel file to output an Excel file with a sophisticated format.

3. Read and write an Excel file containing a huge volume of data.

4. Combine and split an Excel file.

More information can be found in Parsing and Exporting Excel Data in SPL.

esProc is the file processor that can conveniently handle data loading, database export and mixed computations over various types of files, including TXT, Excel, XML, JSON, CSV and INI. The desktop tool is ready to use, simple to configure and convenient to debug. It allows setting a breakpoint and step-by-step execution during which you can view the result of each step. Based on powerful yet simple syntax that agrees with human way of thinking, esProc is more convenient to use compared with high-level languages. See Data File Processor.

SPL is integration-friendly with a Java program. Read How to Call an SPL Script in Java to learn more.

To begin to work with esProc, see Getting Started with esProc.