Differences

This shows you the differences between two versions of the page.

--- platform:format [2014/02/17 03:12]
asok created
+++ platform:format [2014/02/17 04:31] (current)
asok [Supported Data Formats]
@@ Line 1: / Line 1: @@
 =====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.
+<code>
+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
+</code>
+====Variables and Expressions as property values====
+Instead of hard-coded property values, you could use variables or expressions, like in the sample below.
+<code>
+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
+</code>
+====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
+<code>
+format "catest", "load"
+</code>
+In the second case, you need to set the property values and then call //format//.
+<code>
+str myformat="csv"
+str mytable="cacus"
+int records_to_skip=5
+str mycolumns="xcus,xorg,xshort"
+format "catest", "load"
+</code>
+====SQL Statement instead of a Table====
+Also, a table can be replaced with a SQL statement along with an alias.
+<code>
+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"
+</code>
+====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.
+<code>
+  fdelimiter ","
+  rdelimiter "\r\n"
+</code>
+For fixed format, you need to specify column **name**, **start** and **size**, together with the **rdelimiter** (record delimiter) property.
+<code>
+  dataformat FIXED
+  rdelimiter \r\n
+  columns
+    xcus      1   20
+    zid      21   6
+    xorg     28   40
+  end columns
+</code>

DreamApps Wiki

User Tools

Site Tools

Differences

Page Tools