Automating Loading of Data from Text Files

You can automate loading of data from text files, like Excel, csv, formatted text, etc. using Format object.

Hard-coded Property Values

Here is an example.

format catest
file "customer.xlsx"
table "cacus"
dataformat "xlsx"
skip 5
columns xcus,xorg,xshort
event before
if cacus.xcus .eq. ""
int bcount=bcount+1
error "Skipping Blank Record "+bcount
elseif cacus.xcus .eq. lastcus
int dcount=dcount+1
error "Skipping Duplicates "+cacus.xcus+" lastcus="+lastcus+" -- "+dcount+" record"
end if
set lastcus=cacus.xcus
end event
end format

Variables and Expressions as property values

Instead of hard-coded property values, you could use variables or expressions, like in the sample below.

format catest2
file "customer_"+#date+"."+myformat
table mytable
dataformat myformat
skip records_to_skip
columns mycolumns
event before
if cacus.xcus .eq. ""
int bcount=bcount+1
error "Skipping Blank Record "+bcount
elseif cacus.xcus .eq. lastcus
int dcount=dcount+1
error "Skipping Duplicates "+cacus.xcus+" lastcus="+lastcus+" -- "+dcount+" record"
end if
set lastcus=cacus.xcus
end event
end format

Automation

Once you have the format defined, you could now automate loading, using the format command from your method.

In the first case (hard-coded values), it would be

format "catest", "load"

In the second case, you need to set the property values and then call format.

str myformat="csv"
str mytable="cacus"
int records_to_skip=5
format "catest", "load"

SQL Statement instead of a Table

Also, a table can be replaced with a SQL statement along with an alias.

table "(select zid, xordernum, xcus, ~
(select xorg from opordcus c where o.zid=c.zid and o.xordernum=c.xordernum) as xorg ~
from opord o) tbl"

Supported Data Formats

Currently supported data formats are: fixed, text, csv, xls and xlsx.

For text format, you also need to specify fdelimiter (field delimiter) and rdelimiter (record delimiter) properties.

fdelimiter ","
rdelimiter "\r\n"
skip 0

For fixed format, you need to specify column name, start and size together with the rdelimiter (record delimiter) property.

dataformat FIXED
rdelimiter \r\n
skip 0
columns
xcus 1 20
zid 21 6
xorg 28 40
end columns