Importing Excel Data into Access with esProc

In daily work we have frequent use of text data or spreadsheets, we need to import these data into database for further statistical analytics. For this task, esProc is a very handy tool.

In the following example, we will import Excel data into an Access database, to demonstrate how to migrate text data into database with esProc.

In the directory "D:\files\BoxOffice" we stored some box office data for movies. They are Excel files with the extension of "xlsx". The first row of the Excel files contains field names, such as:

Now we need to store each files in Access database, for better data analytics.

Within esProc we need to establish a data connection for Access first, either through system ODBC data source or direct use of mdb or accdb files: 

Connect to Access data source, and then we need to find the list of files to be imported: 

Once we get the data file list, we can then run a loop to read the data from each Excel file and import them to Access: 

When processing each file, we must first read the datasheet, and use its file name (without file extension) as the name for tables in Access database. In order to make the data and table names more in line with the standards for database, spaces in table name will be replaced by "_", and possible blank data records will be deleted. Then, according to the structure and content of data tables, table creation statements will be generated in B5, and data update statements will be generated in C5. When generating the statements, field names and data in the first row must be read, and determine the data types: 

Line 17 executes table creation statements. At this point, we must consider the situation where a table with the same name could already exists in the database. If this is true, A1.rollback() must be executed to do a rollback.

Line 19 imports the data from datasheets to Access database.

When the loop is done, we need to close the data source to avoid the existence of too many connections:

From this example, we can learn how to meet complicated requirements in esProc with simple codes. In particular, the same approach works not only for Excel files and Access database, but also for txt/xml files and various databases.

There are also some other approaches to import text data into the database. For example, the mostprimitive method is manual data input, which is obviouslytime consuming, laborious, boring and error prone. Programmersoften solve these problems by coding. However, coding with common high-level languages ​​(Java, C#) or scripting languages ​​(Perl, Python) means a lot of workload, and is quite difficult to complete. Of course, Excel and Access are all Microsoft Office products. You can import Excel data directly from inside Access, with Excel as an external source. However, every time you can only import a single file, which makes it too troublesome when there are too many files to be imported. Also, this only works for Access database. In contrast, when you need to import batch text data into database, esProc is a nice tool.

A Handy Method of Accessing Data in Remote http Server in Java

In Java projects, sometimes accessing data in remote http server is required. The data can be of xml format or json format. The following will compare two accessing methods through an example.

Here is a servlet which provides employee information query in json format. servlet accesses the employee table in the database and stores the data of employees as follows:

EID   NAME       SURNAME        GENDER  STATE        BIRTHDAY        HIREDATE         DEPT         SALARY

1       Rebecca   Moore      F       California 1974-11-20       2005-03-11       R&D          7000

2       Ashley      Wilson      F       New York 1980-07-19       2008-03-16       Finance    11000

3       Rachel      Johnson   F       New Mexico     1970-12-17       2010-12-01       Sales         9000

4       Emily         Smith        F       Texas        1985-03-07       2006-08-15       HR    7000

5       Ashley      Smith        F       Texas        1975-05-13       2004-07-30       R&D          16000

6       Matthew Johnson   M     California 1984-07-07       2005-07-07       Sales         11000

7       Alexis        Smith        F       Illinois       1972-08-16       2002-08-16       Sales         9000

8       Megan     Wilson      F       California 1979-04-19       1984-04-19       Marketing        11000

9       Victoria    Davis        F       Texas        1983-12-07       2009-12-07       HR    3000

…

servelet'sdoGet function receives the employee id string of json format, queries corresponding employee information in the database, generates an employee information list of json format and return it. The following code omits the process of accessing the database and generating the employee information list:

protected void doGet(HttpServletRequestreq, HttpServletResponseresp) throws ServletException, IOException {

         String inputString=(String) req.getParameter("input");

         //the input value of inputString is："[{EID:8},{EID:32},{EID:44}]";

         if (inputString==null) inputString="";

         String outputString ="";

        

         {...}//here the code of generating outputString through inputString’s queryof the database is omitted

         //the code of generated outputString is

//"[{EID:8,NAME:"Megan",SURNAME:"Wilson",GENDER:"F",STATE:\...";

         resp.getOutputStream().println(outputString);

         resp.setContentType("text/json"); 

}

Java will access this http servlet, get the employee information in which EID is 1 and 2, and then sort the records of information by EID in descending order. Detailed steps are as follows:

1. Import an open source project httpclient to access servlet and get the result.

2. Import an open source project json-lib to parse the returned strings.

3. Use comparison method to sort by EID in descending order.

The sample code is a follows:

public static voidmyHTTP() throws Exception {

                   // the following defines http’s url

                   URL url =

new URL("http://localhost:6080/myweb/servlet/testServlet?input=[{EID:1},{EID:2}]");

                   URI uri = new URI(url.getProtocol(), url.getUserInfo(), url.getHost(), url.getPort(), url.getPath(), url.getQuery(), null);

                   //then send a request from http and receive the returned result

                   CloseableHttpClient client = HttpClients.createDefault();

                   HttpGet get = new HttpGet(uri);

                   CloseableHttpResponse response = client.execute(get);

                   String myJson=EntityUtils.toString(response.getEntity());

                   //then parse the imported data into json object

         JSONArrayjsonArr = JSONArray.fromObject(myJson );

             //then sort the json data (in descending order)

         JSONObjectjObject = null;

                   for(inti = 0;i<jsonArr.size();i++){

                            long l = Long.parseLong(jsonArr.getJSONObject(i).get("EID").toString());

                            for(int j = i+1; j<jsonArr.size();j++){

                                               longnl = Long.parseLong(jsonArr.getJSONObject(j).get("EID").toString());

                                               if(l<nl){

                                                        jObject = jsonArr.getJSONObject(j);

                                                        jsonArr.set(j, jsonArr.getJSONObject(i));

                                                        jsonArr.set(i, jObject);

                                               }

                            }

                   }

                   System.out.println(jsonArr.toString());

         }

The open source project json-lib needs to be imported. The jars necessary for its function are:

         json-lib-2.4-jdk15.jar

         ezmorph-1.0.6.jar

         commons-lang.jar

         commons-beanutils.jar

         commons-logging.jar

         commons-collections.jar

Import the open source project httpclient. The jars necessary for its function are:

            commons-codec-1.6.jar

            commons-logging-1.1.3.jar

            fluent-hc-4.3.5.jar

            httpclient-4.3.5.jar

            httpclient-cache-4.3.5.jar

            httpcore-4.3.2.jar

            httpmime-4.3.5.jar

As can be seen from this example, Java needs to import two open source projects to complete its job. By the way, the jars may have overlapping parts. In addition, myHTTP function's operation for accessing and sorting http data is not universal enough. When it is required to sort in ascending order or according to more than one field, the program has to be modified. To make myHTTP function more universal and as flexible as SQL in accessing and processing data, dynamic analysis of expressions should be achieved, which will produce rather complicated code.

The method in accessing and processing http data in Java can be replaced by the cooperative work of Java and esProc. The advantage of this cooperation is that only one project will be imported in order to realize dynamic accessing and sorting with simple code. esProc can getand compute data from a remote http server conveniently. To achieve the dynamic processing, the expression for sorting could be sent to esProc as a parameter. Please see the figure below:

The value of the parameter sortBy is EID:-1. The program for esProc to access http data contains only six lines of code as follows:

A1：Define the input parameters to be sent to servlet, that is, the employee id list of json format.

A2: Define anhttpfile object, the URL is http://localhost:6080/myweb/servlet/testServlet?input=[{EID:1},{EID:2}].

A3：Import the result returned by httpfile object in A2.

A4：Parse one by one each employee’s information of json format, and generates a sequence.

A5：Sort the data. esProc will first compute the parameter sortBy in macro ${sortBy}, and then execute the resulting statement A4.sort(EID:-1) which means sorting by EID in descending order.

A6：Return the result in A5 to the Java code that called this piece of esProc program.

If the fields and method for sorting are changed, the program needn’t to be modified. We just need to change the parameter sortBy. For example, sort by EID in ascending order and by name in descending order. In this case, what we need is to change the value of sortBy to EID:1,NAME:-1. The sorting statement we finally execute is A4.sort(EID:1,NAME:-1).

This piece of esProc program can be called conveniently in Java using jdbc provided by esProc. To save the above esProc program as file test.dfx, Jave need to call the following code:

          // create a connection between esProc and jdbc

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

//call esProc program (the stored procedure) in which test is the name of filedfx

com.esproc.jdbc.InternalCStatementst;

st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");

// set parameters

st.setObject(1,"EID:1,NAME:-1");//esProc’s input parameters, that is, the dynamic expression for sorting

// execute esProc stored procedure

ResultSet set=st.executeQuery();

while(set.next()) System.out.println("EID="+set.getInt("EID"));

As the esProc code in this example is relatively simple and can be called directly in Java, it is unnecessary to write the esProc script file (like the above-mentioned test.dfx). Thus the code will be written in this way:

st=(com. esproc.jdbc.InternalCStatement)con.createStatement();

ResultSet set=st.executeQuery("=httpfile(\"http://localhost:6080/myweb/servlet/testServlet?input=[{EID:1},{EID:2}]\").read().import@j().sort(EID:1,NAME:-1)");

The above Java code directly called a line of esProc statement, that is, read data from http serverand sort them by specified fields and return the result ResultSet to Java. 

Methods of Accessing Excel Files by R language

There are many ways for R language to access Excel files, but each has its weaknesses. For example, xlsx package has complicated code and supports only Excel 2007; RODBC has too many restrictions, is difficult to understand and unstable and goes wrong strangely. Though the method of saving the file in csv format is relatively common and stable, it operates inconveniently and lacks ability to process multiple files with program. Another method is to extract xml, but the complicated steps and code forbid us to use it.It’s also not ideal to transform the file with a clipboard because part of the operation need to be done manually and we’d rather save the file in csv format.

However, all these problems can be avoided if we access Excel files using gdata package and meanwhile, write to Excel with WriteXLS. Both of the two packages support Excel 2003 and Excel 2007, operate stably, have easy and intuitive code and require no manual work. The following example is used to illustrate the method of accessing Excel with the two function packages.

Target:

There are multiple Excel files of same structure in the directory ordersData. Among these files containing sales order over the years, some are in the format of Excel 2007, others are in the format of Excel 2003. Please load them, compute the total sales amount of each client and write the result to result.xlsx. The following is some of the data of 2011.xlsx:

Code:

library(gdata)                                     

library(WriteXLS)                       

setwd("E: /ordersData")                  

fileList<-dir()   

orders<-read.xls(fileList[1])                                         

for (file in fileList[2:length(fileList)]){                         

orders<-rbind(orders,read.xls(file))

}

result<-aggregate(orders[,4], orders[c(2)],sum)

WriteXLS("result","result.xlsx")                                 

Some of the data of result.xlsx are as follows:

Code interpretation:

1 .The two lines of code library (gdata) and library(WriteXLS)aim to import two third-party function packages, which have read.xls function and WriteXLS function to read and write Excel respectively.

2 .The line of code fileList<-dir()lists all the files in the directory. The following for statement read files by loop and merge data into the data frame orders. If there are other files in the directory, they should be removed using wildcard characters.

3 .This line of code result<-aggregate(orders[,4], orders[c(2)],sum)executes grouping and summarizing, in which orders[,4]represents summarizing column (i.e. Amount) and orders[c(2)] represents grouping column (i.e. Client).

4 .Both read.xls and WriteXLS support the data type data.frame though they come from different packages, therefore, they can coordinate rather well.Besides,read.xls function can automatically identify the format of both Excel 2003 and Eexcel 2007, and is quite convenient to use.

5.All the code is concise and easy to grasp for beginners.

Note for use:

1.Versions

gdata and WriteXLS are not R language's built-in library functions, they are the third-party packages needing download and installation. What’s more, both of them require the Perl environment, so it is particularly important to choose an appropriate version. Through our trials, we find that 2.15.0 version of R language gets along well with 2.13.3 version of gdata and 3.5.0 version of WriteXLS. But something may go wrong if they operate with the newest Perl version and an older 5.14.2 version is thus required. Otherwise the following error report will appear:

Error in xls2sep(xls, sheet, verbose = verbose, ..., method = method,  :

  Intermediate file 'C:\Users\Thim\AppData\Local\Temp\RtmpMHvLZS\file224060624738.csv' missing!

2. Performance

gdata and WriteXLS have no problem in accessing small files, but they perform badly in handling bigger files (maybe because of Perl). For example, it takes 8 to 10 minutes to read an Excel file of 8 columns and 200,000 rows. To achieve a betterperformance, we recommend xlsxfunction package. But, of course, Excel2003 will be of no use in this occasion. In fact,xlsx performs just slightly better than gdata does. Therefore, in order to truly improve performance, it is recommended that all Excel files be transferred into 2007 format and xml files in them be uncompressed and data be read through resolving these xml files.

Alternative methods:

For the problems of version conflicts and poor performance that R language has, we have alternative solutions like Python, esProc, Perl etc. As R language, they can also access Excel files and perform data computing. In the following, we’ll introduce briefly esProc and Python.

esProc integrates the function of accessing EXCEL into its installation package, so it is no need for it to download the extra third-party packages. It can access Excel2003, Excel2007, Excel 2010 and even the older versions. Its code is as follows:

esProc's performance is satisfactory. It takes only 20 to 30 seconds for it to read an Excel file of 8 columns and 200,000 rows.

Python has a rather excellent performance, except that it requires the third-party packages as R language does. Pandasshould have been able to complete the task of accessing xls file easily, but its installation under windows failed (after all, xls files are mainly produced under windows). Finally, we succeeded in performing this operation by using packages of both xlrd and xlwt3. Unfortunately, the two packages support only Excel2003 and produce much more complicated code:

import xlwt3
importxlrd
fromitertools import groupby
from operator import itemgetter
importos
dir="E:/ordersData/"
fileList =os.listdir(dir)
rowList = []
for f in fileList:
book=xlrd.open_workbook(dir+f) #open read-only workbook by loop
sheet=book.sheet_by_index(0)
nrows = sheet.nrows
ncols = sheet.ncols
for i in range(1,nrows):
row_data = sheet.row_values(i)
rowList.append(row_data) #all records are appended to rowList
rowList=sorted(rowList,key=lambda x:(x[1])) #sort the data before grouping
result=[]
for key, items in groupby(rowList, itemgetter(1)): # group using groupby function
    value1=0
forsubItem in items:value1+=subItem[3]
result.append([key,value1]) #merge the summarized result into 2D array in the end
wBook=xlwt3.Workbook() # create a new writable workbook
wSheet=wBook.add_sheet("sheet 1")
wSheet.write(0,0,"Client")
wSheet.write(0,1,"Sum")
for row in range(len(result)): #write data to the file by loop
wSheet.write(row+1,0,result[row][0])
wSheet.write(row+1,1,result[row][1])
wBook.save(dir+"result.xls") #save the file

It is a far more complicated method than R language.