XLSUpload

How often have you looked at a spreadsheet, sighed, and said "Ugh, now I have to get this into a database."  Often this involves upload tools, and mapping columns, exporting to a CSV file... lots of tedious error-prone steps.  Now a quick and simple OIE workflow route can solve this problem! The combination of the new xmlToXMLAction action and the sqlInsertAction allows the creation a potent and generic workflow route - the ability to upload an XLS file directly into a database table!

xlsToXmlAction
The OIE xlsToXmlAction allows the transformation of any columnar XLS data-stream into a StandardXML data-stream.  Utilizing this action provides a very linear way for savvy users to import data into a given database. This action transforms the first sheet in the XLS workbook into StandardXML making the following assumptions:

• The first row corresponds to field names
• The structure of the sheet is columnar with each row corresponding to StandardXML row having fields label as found in the first row.

Data type detection of the values is automatic.  Remember that with XLS there are actually only three data types: float, string, and date-time. All numeric values in XLS are floating-point; Excel has no concept of integer values.  If a cell is none of these types the value of the cell will be taken as a NULL (this will include empty cells).
One caveat with this transformation is that the column headings [which become field names] must be valid XML element names.  In an effort to work around common column heading conventions any spaces, slashes, or hyphens in the heading values will be replaced with underscores;  spaces, etc... are not legal in XML element names.   This shouldn't be an issue for this usage of xlsToXMLAction as [since we are targeting an RDBMS table] it seems very unlikely you would have those characters in a field name.

<action name="actionActivity" id="000100" extensionAttributes="XLSUpload/000100">
<input property="TranslatedInput" formatter="StandardXML"/>
<output><source property="InputMessage"/></output>
<attributes xmlns="">
<extension name="activityName">xlsToXmlAction</extension>
<extension name="description"/>
</attributes>
</action>
Text 1: Example xlsToXmlAction stanza;  translates the InputMessage to TranslatedInput.    

sqlInsertAction
The sqlInsertAction does exactly what it's name implies - it inserts the contents of the StandardXML data-stream into an RDBMS table.  The target table can be specified either via an attribute of the ResultSet element of the data-stream itself or by the tableName parameter of the sqlInsertAction stanza in the workflow markup.  If the data had been transformed from the source format into StandardXML via a format description the data-stream would most likely already contain the name of the table to which the rows correspond.  But when transforming an arbitrary XLS document into StandardXML the table name in the the data-stream  will be "__undefined__", so it will have to be specified in
the action.  The most flexible way to achieve this is to use a process XATTR and reference if by label substitution.

<action name="actionActivity" id="000150" extensionAttributes="XLSUpload/000150">
  <output><source property="TranslatedInput"/></output>
  <attributes xmlns="">
    <extension name="activityName">sqlInsertAction</extension>
    <extension name="dataSource">myDSName</extension>
    <extension name="tableName">$__XATTR_TABLENAME__;</extension>
    <extension name="description"/>
  </attributes>
</action>
Text 2: Example sqlInsertAction that inserts the contents of TranslatedInput into the specified table of the named data source.

Route Creation
Grab the example markup from the wiki and use it to create a route in your OIE instance.  You should get a 301 response indicating that a collection has been representing the route.   Your target database need to be define in the OIE instance's OIESQLDataSources default.

curl -u adam -T markup.bpml http://coils.example.com/dav/Workflow/Routes

Invocation
Now we have a route we can use to upload an XLS document into an RDBMS table - provided the columns of the XLS file correspond to the column of
the target table.  Initiating the upload is as simple as a curl command:

curl -u adam -T myXLSFile.xls
"http://coils.example.com/dav/Workflow/Routes/XLSUpload?tableName=myTable"

The contents of the myXLSFile.xls will be transformed to StandardXML and inserted into the myTable table. It is still up to you to verify the data you are uploading has appropriate types and will not violate any constraints applied to the table; but you should get an informative error message if something fails.  This is a much more reliable mechanism to upload arbitrary data than exporting to CSV and all the subsequent steps.