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:


col1 col2 col3
11    22    33


col1 col2 col3
44    55    66


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.