To make data analysis, first you need to load the original data. The data used most frequently comes from the text files or the databases. In esProc, you can load data from the text files or the databases easily and quickly.
1. Text file data
esProc can loaddata from a text file as a table sequence. For example, the text file empolyee.txt contains employee information 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
…
import function is used in esProc to import data from files:
| A | |
| 1 | =file(“employee.txt “) |
| 2 | =A1.import@t() |
| 3 | =A1.import() |
In A2, the use of option @t in import function means that, during data importing, the first row of the text file will be regarded as the column names in the imported table sequence. The data in A2 is as follows:
Let’s look at what it will be like if option @t isn’t used. The imported table sequence in A3 is as follows:
2. Database data
esProc can access various kinds of databases through JDBC. Click menu item Datasource Connection in Tool to view the datasource manager:
You can connect to or disconnect from a certain datasource, as well as configure the database to be connected through the datasource manager. demo is esProc’s built-in datasource which is launched by executing esProc\bin\startDataBase.bat under the installation directory. Once it connects to the datasource, esProc gains access to the database and fetches data using SQL:
| A | |
| 1 | =demo.query(“select * from CITIES”) |
| 2 | $select * from CITIES |
query function can be used to get the result set after executing the SQL command and retrieve it as a table sequence, like the code in A1. When the database is connected, SQL statement can be written directly behind $, like what is shown in A2. The results in A1 and A2 are the same, as shown below:
Besides using the datasource manager, you can also use connect function to connect to a datasource. In this case, you should close the connection using close function after data is retrieved from the database:
| A | |
| 1 | =connect(“demo”) |
| 2 | =A1.query(“select * from CITIES”) |
| 3 | >A1.close() |
The same method is used in A2 to import the table sequence of cities information.