DreamApps Wiki

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

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

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

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"

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"

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