Query Records in a Database Table by Pairs of Excel Values and Store Them in Excel Files
Problem description & analysis
Below is part of the data in database table EMPLOYEE:
| 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 |
| … |
… |
… |
… |
… |
… |
… |
… |
… |
And here is Excel file args.xlsx:
| dept |
gender |
| R&D |
M |
| HR |
F |
We are trying to find records in EMPLOYEE according to the multiple pairs of dept.gender values and store records for each pair of values in an xlsx files. Below is the desired result:
R&D_M.xlsx：
| EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
SALARY |
| 10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
13000 |
| 22 |
Jacob |
Davis |
M |
Texas |
1985-05-07 |
2001-05-07 |
R&D |
16000 |
| 33 |
Matthew |
Martinez |
M |
Pennsylvania |
1980-07-19 |
2000-07-19 |
R&D |
11000 |
| 35 |
Justin |
Smith |
M |
Texas |
1978-08-20 |
2008-08-20 |
R&D |
7000 |
| 38 |
Matthew |
Johnson |
M |
New York |
1972-11-20 |
2002-11-20 |
R&D |
6000 |
| 187 |
Nicholas |
Smith |
M |
Pennsylvania |
1986-11-13 |
2005-12-01 |
R&D |
5000 |
| 189 |
Antony |
Williams |
M |
North Carolina |
1978-07-26 |
2007-11-01 |
R&D |
7000 |
| 190 |
Robert |
Johnson |
M |
Minnesota |
1976-01-12 |
2008-12-01 |
R&D |
10000 |
| 191 |
David |
Johnson |
M |
Florida |
1969-01-31 |
2007-01-01 |
R&D |
5000 |
| 192 |
Christian |
Martin |
M |
Ohio |
1974-12-09 |
2006-11-01 |
R&D |
8000 |
| 195 |
Michael |
Smith |
M |
South Carolina |
1975-05-10 |
2011-01-01 |
R&D |
6500 |
| 198 |
Christopher |
Robinson |
M |
Arkansas |
1978-10-03 |
2004-04-01 |
R&D |
8000 |
| 200 |
Zachary |
Williams |
M |
Michigan |
1983-06-17 |
2001-04-01 |
R&D |
7000 |
| 202 |
Nicholas |
Miller |
M |
Florida |
1987-04-27 |
2006-05-01 |
R&D |
6500 |
| 211 |
William |
Thomas |
M |
Pennsylvania |
1969-05-10 |
2004-11-01 |
R&D |
5000 |
| 212 |
Ryan |
Smith |
M |
Washington |
1981-09-14 |
2009-05-01 |
R&D |
8000 |
| 214 |
James |
Williams |
M |
Texas |
1976-04-11 |
2000-07-01 |
R&D |
5000 |
| 215 |
Ryan |
Williams |
M |
California |
1971-01-13 |
2009-10-01 |
R&D |
6500 |
HR_F.xlsx：
| EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
SALARY |
| 4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
7000 |
| 9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
3000 |
| 163 |
Ashley |
Smith |
F |
Kentucky |
1979-12-26 |
2010-12-01 |
HR |
5000 |
| 165 |
Hannah |
Smith |
F |
Texas |
1979-06-26 |
2010-07-01 |
HR |
6500 |
| 171 |
Megan |
Jones |
F |
California |
1978-07-24 |
2010-09-01 |
HR |
8000 |
| 175 |
Jasmine |
Smith |
F |
Pennsylvania |
1976-03-23 |
2005-07-01 |
HR |
7000 |
| 177 |
Megan |
Johnson |
F |
Missouri |
1978-03-11 |
2009-07-01 |
HR |
5000 |
| 179 |
Olivia |
Jones |
F |
North Carolina |
1972-08-17 |
2007-05-01 |
HR |
10000 |
| 180 |
Abigail |
Smith |
F |
New York |
1972-09-19 |
2007-05-01 |
HR |
5000 |
| 181 |
Alyssa |
Johnson |
F |
Illinois |
1982-04-30 |
2002-08-01 |
HR |
6500 |
Solution
Write the following script p1.dfx in esProc:
| A |
|
| 1 |
=file("args.xlsx").xlsimport@t() |
| 2 |
=connect("demo") |
| 3 |
=A1.(A2.query("SELECT * FROM EMPLOYEE WHERE DEPT=? AND GENDER=?",dept,gender)) |
| 4 |
>A2.close() |
| 5 |
>A1.(file(dept/"_"/gender/".xlsx").xlsexport@t(A3(#))) |
Explanation:
A1 Import data from args.xlsx.
A2 Connect to demo database.
A3 Loop through each row of A1 to perform SQL in database and return result as a table sequence. The two parameters correspond to the current dept and gender in A1.
A4 Close database connection.
A5 Loop through A1 to export A3’s corresponding data to an Excel file. The file name will be dept_gender.xlsx (dept and gender are the current ones in A1).
Read How to Call an SPL Script in Java to learn about the method of integrating the SPL script into Java.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
