esProc Assists Java in Merging Text Files to Excel

Problem source:  .

In this case, you need to merge data from all text files in a given directory and export it to an Excel file. Each of these text files has tab-seperated data with one row and three columns.

JAVA approaches the problem by running a loop to retrieve all files in the directory and then writing data into the Excel file. The open source package (for example Apache POI) JAVA uses to handle Excel files provides a low-level API, which makes the coding really tedious.

esProc gets POI encapsulated and thus makes the operation quite easy:


A1:List all files in E:\\test. @p option indicates returning file names with full paths

A2:Retrieve all files under the directoy and combine their data together

A3:Export the merged data to an Excel file of xlsx format (the xlsx format can hold a million rows of data)


The script can be called by the JAVA program via JDBC interface provided by esProc. This is the code used for Java program to call the script through esProc JDBC (save the above code as test.dfx):

          // establish a connection via esProc jdbc


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

// call esProc script test.dfx (similar to the stored procedure)

com.esproc.jdbc.InternalCStatement st =(com.esproc.jdbc.InternalCStatement)con.prepareCall(“call test()”);

// execute esProc program


For scripts with very small amount of code, they can be written directly in the JAVA program that calls the esProc JDBC, without the need of writing it (test.dfx) separately:


This line of JAVA code calls directly a line of esProc code, which is equal to the above three-line script.


About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in esProc/R/Python/Perl, Program Language, Structured Data Process and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s