Text Data Manipulation in SPL

 

Question

Here’s what is inside of my notepad file:

01 2014/10/17 Orly Yanson 7:30:00 1

02 2014/10/17 Juan DelaCruz 7:20:59 1

03 2014/10/17 Pedro Penduko 7:30:00 1

01 2014/10/17 Orly Yanson 11:30:35 2

02 2014/10/17 Juan DelaCruz 11:29:55 2

03 2014/10/17 Pedro Penduko 7:36:50 2

01 2014/10/17 Orly Yanson 1:30:28 3      

02 2014/10/17 Juan DelaCruz 1:30:00 3

03 2014/10/17 Pedro Penduko 1:30:50 3

01 2014/10/17 Orly Yanson 7:30:02 4

02 2014/10/17 Juan DelaCruz 7:30:08 4

03 2014/10/17 Pedro Penduko 7:50:29 4

 

Is it possible to get the txt file by using SQL in Java?

 

Answer

Java lacks the class library to process the structured data, but SPL (Structured Process Language) can do this for the advanced language. Below is the SPL script for handling your question:

A

1

$select * from D:/note.txt

2

=A1.groups(_1,_2; sum(_6))

3

=A2.select(_1==1   &&date(     _2,"yyyy/MM/dd")>=date("2014/10/01","yyyy/MM/dd"))

4

=A1.sort(_1,_2)

A1: Read the text file.

A2: Group records by the 1st column and the 2nd column and calculate sum over the 6th column.

A3: Find records where value of the 1st column is 1 and where value of the 2nd column is later than 2014/10/01.

A4: Sort the records by the 1st column and the 2nd column.

esProc SPL is designed to handle structured data and an SPL script can be integrate into a Java application through esPro JDBC. For more information, see How to Call an SPL Script in Java.