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 Excel2007; 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 Excel2003 and Excel2007, 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 Excel2007, others are in the format of Excel2003. 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 Excel2003 and Eexcel2007, 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 better performance, we recommend xlsx function 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. Pandas should 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

    import xlrd

    from itertools import groupby

    from operator import itemgetter

    import os

    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.

A Handy Method of Accessing Excel Files in Java

Processing data from Excel files is common in Java projects. Here is an example to illustrate how to accomplish this. Read information of sales orders from an Excel file orders.xls and select orders that are placed on and after January 1, 2010 and whose SELLERID field is 18. Then write the selected data to order_result.xls. 

The content of Excel file orders.xls is as follows:

ORDERID	CLIENT	SELLERID	AMOUNT	ORDERDATE
1	UJRNP	17	392	2008/11/2 15:28
2	SJCH	6	4802	2008/11/9 15:28
3	UJRNP	16	13500	2008/11/5 15:28
tha4	PWQ	9	26100	2008/11/8 15:28
5	PWQ	11	4410	2008/11/12 15:28
6	HANAR	18	6174	2008/11/7 15:28
7	EGU	2	17800	2008/11/6 15:28
8	VILJX	7	2156	2008/11/9 15:28
9	JAYB	14	17400	2008/11/12 15:28
10	JAXE	19	19200	2008/11/12 15:28
11	SJCH	7	13700	2008/11/10 15:28
12	QUICK	11	21200	2008/11/13 15:28
13	HL	12	21400	2008/11/21 15:28
14	JAYB	1	7644	2008/11/16 15:28
15	MIP	16	3234	2008/11/19 15:28
16	AYWYN	4	6566	2008/11/21 15:28

Steps of coding in Java are like this:

1.Import data from the Excel file by rows and store them in sourceList, the List object.

2.Traverse sourceList, the List object, and store the eligible data in resultList, the List object.

3.Traverse resultList and store the data in the output Excel file by rows.

The code contains some statements handling different data types. See the following for detail:

   public static void myExcel() throws Exception {

      //import data from the excel file

      HSSFWorkbook workbook=

newHSSFWorkbook(new FileInputStream(new File("d:/file/orders.xls")));

      Sheet sheet=workbook.getSheetAt(0);//  import the data from the first sheet

      List<Map<String,Object>>sourceList= new ArrayList<Map<String,Object>>();

      List<Map<String,Object>>resultList= new ArrayList<Map<String,Object>>();

      SimpleDateFormat format = new SimpleDateFormat("yyyy-M-ddHH:mm:ss");

      for (inti = 1; i<sheet.getPhysicalNumberOfRows(); i++) {//process excel data by rows

         Row row=sheet.getRow(i);

         Map<String,Object> order=new HashMap<String,Object>();

         Cell cell0 = row.getCell(0);

         cell0.setCellType(Cell.CELL_TYPE_STRING);

              //integer data should be converted into the type of txt, otherwise, they would become the floating point

        Cell cell1 = row.getCell(1);

        cell1.setCellType(Cell.CELL_TYPE_STRING);

        Cell cell2 = row.getCell(2);

        cell2.setCellType(Cell.CELL_TYPE_STRING);

        order.put("ORDERID",cell0.toString());

        order.put("CLIENT",cell1.toString());

        order.put("SELLERID",cell2.toString());

        order.put("AMOUNT",row.getCell(3).toString());

        //process data of date type

        order.put("ORDERDATE",

HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue()));

        sourceList.add(order);

     }

     for (inti = 0, len = sourceList.size(); i<len; i++) {//filter according to the specified condition

        Map<String,Object> order =(Map<String,Object>) sourceList.get(i);                 System.out.println("1order.get(\"SELLERID\")="+order.get("SELLERID"));

        if ( Integer.parseInt(order.get("SELLERID").toString())==18 &&

        ((Date)order.get("ORDERDATE")).after(format.parse("2009-12-31 23:59:59")) )

        {//evaluatethe data’s eligibility

            resultList.add(order); //add the eligible orders to resultList, the List object

        }

     }

     //write excel file

     HSSFWorkbook workbook1 = new HSSFWorkbook();//create an excel file object

     Sheet sheet1 = workbook1.createSheet();//create a sheet object

     Row row1;

     row1 = sheet1.createRow(0);//the first row: headline

     row1.createCell(0).setCellValue("ORDERID");

     row1.createCell(1).setCellValue("CLIENT");

     row1.createCell(2).setCellValue("SELLERID");

     row1.createCell(3).setCellValue("AMOUNT");

     row1.createCell(4).setCellValue("ORDERDATE");

     for (inti = 1, len = resultList.size(); i<len; i++) {//create data rows by loop

        row1 = sheet1.createRow(i);
      row1.createCell(0).setCellValue(resultList.get(i).get("ORDERID").toString());

                            row1.createCell(1).setCellValue(resultList.get(i).get("CLIENT").toString());

                            row1.createCell(2).setCellValue(resultList.get(i).get("SELLERID").toString());

                            row1.createCell(3).setCellValue(resultList.get(i).get("AMOUNT").toString());

                            row1.createCell(4).setCellValue(format.format((Date) resultList.get(i).get("ORDERDATE")));

                   }

FileOutputStreamfos = new FileOutputStream("d:/file/orders_result.xls"); 

workbook1.write(fos);// write a file

fos.close(); 

         }

The resulting Excel data after the code is executed are as follows:

ORDERID	CLIENT	SELLERID	AMOUNT	ORDERDATE
432	ERNSH	18	6272.0	2010-1-13 15:28:05
444	SJCH	18	4312.0	2010-1-25 15:28:05
452	HP	18	4312.0	2010-2-01 15:28:05
492	HP	18	27900.0	2010-3-07 15:28:05
512	BTMMU	18	18000.0	2010-3-27 15:28:05
524	PJIPE	18	15600.0	2010-4-09 15:28:05
637	HP	18	9114.0	2010-8-01 15:28:05
638	JOPO	18	294.0	2010-8-01 15:28:05

…

Most of the code written with myExcel function is responsible for accessing and processing Excel files and data types. Yet it is rather complicated. There is just a small amount ofcode used for filtering based on conditions,and it is not universal enough. As filtering conditions are fixed, if there is any change about them, the code should be modified accordingly. We have to write code for analyzing dynamic expressions if we want to set dynamic filtering conditions.By doing so, we can get dynamic filtering conditions similar as the SQL theoretically. But programming would be difficult and the adjustment would be comparatively complicated.

If esProc, a programming language specially designed for processing structured (semi-structured) data, is used to help to process Excel data, programming will become much easier. With esProc, we can produce comparatively simple code to process Excel files. Besides, it is also simple for Java to call esProc script.

Programmers may pass the condition that “orders that are placed on and after January 1, 2010 and whose SELLERID field is 18” on to esProc program as the parameter where. See the chart below: 

The value of where is ORDERDATE>=date(2010,1,1) && SELLERID==18. The code ofesProc program is as follows:

A1： Define a file object, import data to it and set the first row as the headline. esProc's IDE can show the imported data visually, as shown in the right part of the above chart. The code for accessing file xlsx is =file("D:/file/orders.xlsx").importxls@xt().

A2： Filter according to the condition. Here macro is used to analyze the expression dynamically. where is the input parameter. esProc will first compute the expression enclosed by ${…}, replace ${…} with the computing result acting as the macro string value, and then interpret and execute the result. The code finally executed is =A1.select(ORDERDATE>=date(2010,1,1) && SELLERID==18).

A3： Write the eligible result set to the Excel file.

The code needn't to be rewritten when the filtering condition is changed, we just need to modify the parameter where. For example, if the filtering condition becomes "orders that are placed on and after January 1, 2010 and whose SELLERID field is 18, or orders whose CLIENT is PWQ", the value of parameter where can be expressed like this:CLIENT=="PWQ"||ORDERDATE>=date(2010,1,1) && SELLERID==18. After the code is executed, the result set of A2 is as follows:

This piece of code can be called in Java application by using JDBC provided by esProc. The code called in Java for saving the above esProc program as test.dfx is as follows:

// create a connection between esProc and JDBC

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

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

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

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

//set parameters

st.setObject(1,"ORDERDATE>=date(2010,1,1) && SELLERID==18 || CLIENT==\"PWQ\"");//the parameter is the dynamic filtering condition

//execute esProc stored procedure

st.execute();

For the script with relatively simple code, it is not necessary to write specially the esProc script file (test.dfx). esProc code can be written directly to the Java application thatcalls esProc JDBC.

    String where="CLIENT==\"PWQ\"||ORDERDATE>=date(2010,1,1) && SELLERID==18 ";

    String resultpath=" D:/file/orders_result.xls ";

    String sourcepath=" D:/file/orders.xls ";

    ResultSet set =     st.executeQuery("=file(\""+resultpath+"\").exportxls@t(file(\""+sourcepath+"\").importxls@t().select("+where+"))");

This piece of Java code calls a line of code from esProc script directly: get data from the Excel file, filter them according to specified conditions and write the result set to the Excel file. 

Code Examples of Accessing HTTP Data in esProc

esProc can access data conveniently in http data source for processing. Now we'll look at some functions through an example.
In this example, a servlet provides outward query of employee information in json format. Servlet accesses employee table in the database and saves employee information as follows:

doGet function of servlet receives employee id strings of json format, queries corresponding employee information through the database and generates employee information list in json format and then returns it. Process of reading the database and generating employee information is omitted in the following code: protected void doGet(HttpServletRequestreq, HttpServletResponseresp) throws Servlet Exception, IOException {
// TODO Auto-generated method stub
String inputString=(String) req.getParameter("input");
//inputString  input value is："[{EID:8},{EID:32},{EID:44}]";
if (inputString==null) inputString="";
String outputString ="";

{...}//code for querying the database through inputString and generating outputSring is omitted here
  // the generated outputString
//"[{EID:8,NAME:"Megan",SURNAME:"Wilson",GENDER:"F",STATE:\...";
resp.getOutputStream().println(outputString);
resp.setContentType("text/json; charset=GBK");
}

The following code can be used for esProc to access this http servlet:

A1: Define the input parameter to be submitted to servlet, i.e. the employeeid list in json format.

A2: Define httpfile objects, URL is http://localhost:8080/demo/testServlet?input=[{EID:8},{EID:32},{EID:44}].

A3: Import A2, the result returned by http file objects.

A4: Parse by rows the json format information of each employee, and create a sequence.

A5: Compute on the table sequence in A4 and combine SURNAME and NAME into FULLNAME.

A6: Export results of A5 to a text file. 

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 filesin 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 most primitive method is manual data input, which is obviously time consuming, laborious, boring and error prone. Programmers often 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.