CSV Import DSC

 Browse Space
Added by Kendy Yus, last edited by Lachlan Aldred on Apr 01, 2009  (view change)

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

Component: com.avoka.livecycle.csvImport 8.0

CsvImport Service

CsvImport: The CSV import service extracts data from a Comma Separated Value (CSV) file to be used within a LiveCycle process. Many tools export CSV data. For example databases use this format to dump the contents of their tables, MS Excel spreadsheets can be exported/imported to/from the CSV format. Business processes, being across many knowledge based systems and being knowledge intensive in nature require a way to import CSV data into the process. This service provides the ability to import CSV into your process to an XML format. Sometimes the datasets being imported from are extremely large (i.e. in excess of 50 MB) in which case it is not feasible for the CSV data to be transformed into XML. In this case the service allows us to import data from the CSV and, line by line, update straight into a database table.

Operation Title: Import a Csv File into XML   (importCsv)

Import a Csv File into XML: This operation imports a CSV file into an XML format. XML is a common data format in LiveCycle processes and so when data is stored in a document/file as CSV and the process we create needs to 'reason' about the contents of the CSV file, converting it into XML makes our life easier. This is only feasible for smaller CSV files. For CSV files larger than 10 MB it is recommended to use the 'Import a Csv File into SQL' operation.

Input Parameter Type Description Editor
Input Document java.lang.String The CSV document input. This can either point to a document variable in the LiveCycle process or refer to a file on the file system of the LiveCycle server. This is mandatory. CSV Input Settings
Design Time Template java.lang.String A design-time example of the expected CSV document input. This should refer to a file on the file system of the LiveCycle server. This is optional. CSV Input Settings
Use Headers boolean This tells the CSV parser that the first line of the CSV contains descriptions of each field – and not data values. CSV Input Settings
Is Excel Format boolean This tells the CSV parser that CSV file was produced by Microsoft Excel. CSV Input Settings
XML Root Element java.lang.String A simple name of the root XML element to be generated. This name must be in Non Colonized Name (NCN) format, meaning it must contain no spaces, punctuation marks, namespaces, colons etc. This is mandatory. CSV To XML Mappings
XML Repeating Element java.lang.String A simple name of the repeating XML element to be generated. The repeating element sits just underneath the root element and is repeated for each line of the CSV file. This name must be in Non Colonized Name (NCN) format, meaning it must contain no spaces, punctuation marks, namespaces, colons etc. This is mandatory. CSV To XML Mappings
CSV to SQL Mappings java.util.Map This screen allows us to model a mapping between each CSV field and an XML element. Each element here occurs underneath a repeating XML element. Pressing 'Load CSV Headers from Template' pulls the Header fields from the CSV document and fills the table. CSV To XML Mappings
  java.util.Map In LiveCycle XML variables do not require an XML schema, but it helps to have one. An XML variable with a configured schema allows us to use the XPath builder into internal node of the XML variable. This page allows us to generate an XML Schema based on the 'CSV to XML Mappings'. XSD Generation
Output Parameter Type Description
XML Formatted Data java.lang.String The generated XML document, built from the input CSV file, returned in string format.

Operation Title: Import a Csv File into SQL   (csvToSql)

Import a Csv File into SQL: This operation imports the contents of a CSV document and writes each line of it, as a row, into a selected database table. In many situations the CSV file is too large to be stored into LiveCycle as a process variable. In such cases it becomes necessary to write the CSV file contents into a database – line by line. This operation allows us to update such a table with new values for existing rows, or to create new rows as needed. This is fully configurable.

Input Parameter Type Description Editor
Input Document java.lang.String The CSV document input. This can either point to a document variable in the LiveCycle process or refer to a file on the file system of the LiveCycle server. This is mandatory. CSV Input Settings
Design Time Template java.lang.String A design-time example of the expected CSV document input. This should refer to a file on the file system of the LiveCycle server. This is optional. CSV Input Settings
Delimiter java.lang.String This is the sequence of characters used to separate field values. It is optional and is the comma (,) by default. CSV Input Settings
Use Headers boolean This tells the CSV parser that the first line of the CSV contains descriptions of each field – and not data values. CSV Input Settings
Is Excel Format boolean This tells the CSV parser that CSV file was produced by Microsoft Excel. CSV Input Settings
Purge Table First boolean This tells the service to purge the contents of the table being written to before starting the CSV import. CSV Input Settings
Dynamic Insert/Update boolean This tells the service to perform an insert if the row does not already exist in the target database table. And if the table already contains a row then the service should perform an update to it. CSV Input Settings
JNDI Data Source java.lang.String The JNDI name of the database to be written to. Datasource
Table Name java.lang.String The name of the database table being written to. Datasource
Date Format java.lang.String The format for parsing dates from the CSV file. See Java SimpleDateFormat for more information. Data Format
Time Format java.lang.String The format for parsing times from the CSV file. See Java SimpleDateFormat for more information. Data Format
Date-Time Format java.lang.String The format for parsing date-times from the CSV file. See Java SimpleDateFormat for more information. Data Format
Boolean True java.lang.String The format for parsing a boolean true value from the CSV file (e.g. 'true', 'yes', '1'). Data Format
Boolean False java.lang.String The format for parsing a boolean false value from the CSV file (e.g. 'false', 'no', '0'). Data Format
CSV to SQL Mappings java.util.Map This screen allows us to model a mapping between each CSV field and a column in a database table. Pressing 'Load Table' pulls the Header fields from the CSV document and fills the table. It also loads the column names of the target database table into the UI. Select the database column that the CSV field maps to by selecting it in the 'Map To' drop-down box. Select the data type for each CSV field by selecting from the 'Datatype' drop-down. Identify the key column of the CSV file. Csv to SQL Mappings
Output Parameter Type Description
Records Processed long Number of Records Processed
Import Timestamp java.lang.String Timestamp upon completion of CSV import.
Error Record java.lang.String Running record of errors.
Number Of Error Record long Record of number of errors.

For how to configure license information for this component service - CsvImport, please click on the following link:
Avoka LiveCycle ES Component License Configuration

Please send us any questions or comments on this documentation regarding CsvImport by clicking on the following link:
Feedback

Licence Configuration:

Parameter Type Description
License Key java.lang.String The license key provided to you when you purchased a production license for this Solution Component. If blank this Solution Component will assume evaluation mode and allow 100 invocations or 6 hours up-time.
License Organisation java.lang.String The organization name you provided to Avoka for which you are licensing this Solution Component.