June 12, 2015

Export Dynamic Text Files to Database with esProc


There are multiple text files in a certain directory. Each of their names includes one of the letters a/b/c at the beginning and a date. The letter is fixed while the date is dynamic. You need to write contents of these files as well as the dates into a database table by a specified date. The text files are as follows:

a20100325.txt

col1 col2 col3
11    22    33

b20100325.txt

col1 col2 col3
44    55    66

c20100325.txt

col1 col2 col3
77    88    99

The expected database table, tb1, after data export is as follows:

col1 col2 col3 d_date
11    22    33    20100325
44    55    66    20100325
77    88    99    20100325


esProc script for doing this:

A1: A sequence composed of a, b, c.

A2: Import text files named after the three letters in A1 and the dates specified by parameter ddate by loop. Merge the three files using conj() and add a date column (d_date) with derive. Then enter the dates as values of the newly added column.

A3: Update the table tb1 with A2’s result. @i means inserting data only.