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
str mycolumns="xcus,xorg,xshort"
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. When the format is not specified, the system will assume a format based on the file extension.

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

fdelimiter ","
rdelimiter "\r\n"

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

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