The Import module facilitates data transforming and loading into QBO tables. Use cases include:

• Monitoring an FTP site for files to import
• Users dragging and dropping spreadsheets, delimited or XML files onto a web page
• Accepting an inbound web request, transforming the data, and executing method signatures

The Import module includes several classes to manage the importing of data, including:

• Batch Engine: processes spreadsheets (typically Excel or CSV), assuming each row comprises a method signature.
• XmlEngine: processes XML documents, assuming nodes match QBO class and properties match class properties.
• SqlBulkEngine: bulk copies spreadsheet data into SQL server, and (optionally) executes a post-import statement to transform and load the data into QBO tables.
• TaskEngine: creates a task template based on a spreadsheet's columns, and creates a task for each row in the spreadsheet.

The BatchEngine handles everyone's favorite method of data exchange: the spreadsheet. Any QBO method signature can be invoked using a spreadsheet, simply by defining the appropriate column names. If you name the first two columns as ClassName and Operation, the remaining columns simply need to match the QBO class properties (column names).

Example 1: Create Contact records

Example 2: Launch an underwriting workflow for loans 12345, 23456, and 34567:

Example 3: Create welcome documents for new employees:

Generic QBO classes, including Contact, Decision and Attachment, can be a child of any other QBO object. The Object property defines the class that the generic object is a child of, and the ObjectID property defines the primary key (identity column) of the parent record.

Columns matching the ImportFileQueue properties will populate the ImportFileQueue columns, instead of the target ClassName. To work around such conflicts, you may specify ParameterString. In the example above, both ImportFileQueue and Attachment have Object and ObjectID columns; without using ParameterString, the ImportFileQueue would be bound to a Person, rather than the Attachment being bound to a Person.

The batch engine is processed in 2 steps:

• An ImportFileQueue record is created for each row in the spreadsheet, and
• Each row in the ImportFileQueue is processed by the Queuing module

This allows you to isolate failures on a row-by-row basis, as well as control the system resources allocated to processing this data.

Spreadsheets with non-QBO column names can be mapped to QBO column names. Assume we wish to save a spreadsheet like this:

A power user can create an ImportFileTemplate including the following:

• Name: My Custom Contact Import
• Import Engine: BatchEngine
• Parameters: ClassName=Contact&Operation=Save
• Add four ImportFileMap rows:
  • First => FirstName
  • Middle => MiddleName
  • Last => LastName
  • Zip => PostalCode

Once created, the following method signature will start watching an FTP site, and process matching spreadsheets against this template:

ImportFile/Watch?Template=My Custom Contact Import&FileObject=SomeClientFtpSite&Path=/outbound/*.xslx

Both the BatchApply and Workflow Advanced Step features store ad-hoc data (e.g. user-defined fields) in the ImportFileQueue module. By default, this data is associated with the ImportFile module, which is the parent of ImportFileQueue. However, it is frequently useful to expose this data as a child of the objects created by the ImportFileQueue. This is configured by:

• Navigating to Configuration > Data Tuning
• Select the Entity View from the Data Tuning Menu
• In the row for Import File Queue, click on the Entity Parent column's `Included' link, and enter the following custom SQL:

SELECT 'ImportFileQueue' AS Object,
 ImportFileQueueID AS ObjectID,
 ImportFileQueue AS Label,
 Object AS Parent,
 ObjectID AS ParentID
FROM ImportFileQueue WITH (NOLOCK)

UNION ALL

SELECT 'ImportFileQueue' AS Object,
 ImportFileQueueID AS ObjectID,
 ImportFileQueue AS Label,
 DecisionStep.Object AS Parent,
 DecisionStep.ObjectID AS ParentID
FROM
ImportFileQueue WITH (NOLOCK)
  INNER JOIN DecisionStep WITH (NOLOCK)
    ON DecisionStep.DecisionID = ImportFileQueue.ObjectID
WHERE
  ImportFileQueue.Object = 'Decision'
  AND ImportFileQueue.ObjectID IS NOT NULL

If the Import File Queue Entity Parent column link says Custom instead of Included, this change has already been made.

Some Excel spreadsheets can result in duplicate rows being processed, or even fail to process at all.

Quandis uses Microsoft's ACE drivers to read Excel spreadsheets. If Excel has been 'configured' with anything 'extra' such as filters or regions, the ACE drivers could error, such as sometimes list a row twice. For example, in the image below, note the filters that have been added to the header row. This is an example of a spreadsheet that the ACE driver's 'get confused' with, resulting in duplicate rows.

To avoid this, save the spreadsheet as a .CSV file, getting rid of all the 'extra' Excel features. Once done, you can import the .CSV file, or convert it back to Excel (.XLS or .XLSX) and import the 'clean' version.

Another method is highlighting each entire sheet, right clicking and hitting remove all formatting.

Unfortunately, Quandis does not control the Microsoft ACE drivers, so we cannot 'fix' the issue on our side!

More information can be found online about the Microsoft ACE drivers and their capabilities.

Excel features that seem to cause errors are:

• Filters
• Regions
• Pivot Tables
• Calculated Fields
• Functions

Tip: If working with 'labels' that only contain numbers such as postal codes or loan numbers, include a single quote at the beginning of the values to retain leading zeros, such as '02171 or '0000001.

To run the 64 bit ACE drivers on IIS, ensure the IIS Application Pool Enable32BitApplications setting is set to false

The XmlEngine processes heterogeneous method signatures.

Example: insert a message

<ImportCollection>
  <MessageItem>
    <Object>Loan</Object>
    <ObjectID>127</ObjectID>
    <Message>This is the subject</Message>
    <BodyText>Message Content Goes here</BodyText>
  </MessageItem>
</ImportCollection>

Example: insert an attachment by Base64 encoding the binary file as a Content node:

<ImportCollection>
  <AttachmentItem>
    <Attachment>My Test File</Attachment>
    <FileName>TestFile.txt</FileName>
    <Object>Foreclosure</Object>
    <ObjectID>7288</ObjectID>
    <Description>Description goes here</Description>
    <Content><![CDATA[VGVzdCBEYXRhIEdvZXMgSGVyZQ==]]></Content>
  </AttachmentItem>
</ImportCollection>

Example: nested relationships

<LoanCollection>
	<LoanItem>
		<Loan>12345</Loan>
		<Property>
			<Address>123 Main Street</Address>
		</Property>
		<Servicer>Wells Fargo</Servicer>
		<Messages>
			<MessageItem>
				<Message>Foo</Message>
			</MessageItem>
			<MessageItem>
				<Message>Foo</Message>
			</MessageItem>
		</Messages>
	</LoanItem>
</LoanCollection>

In this example:

• PropertyID is a foreign key on the Loan table; QBO recognizes the <Property> node as representing a foreign key
• ServicerID is a foreign key; QBO recognizes <Servicer> element as representing Organization.Organization
• Messages is a child class to all QBO classes; QBO will import 2 Message rows from this example, bound to the Loan

The SqlBulkEngine allows a power user to easily map text file data into multiple tables. It works as follows:

• Creates (or updates) a staging table to match the columns present in a text file
• Bulk copies text data to a staging table
• Executes a statement to process data staging table
  • this leverages ImportFileMap.Destination to offer some nifty sugar when designing statements; see below

There a several options (placed in ImportFileTemplate.Parameters) one can use to configure the SqlBulkEngine, including:

• Truncate: (optional, defaults to false) if true, the staging table will be truncated
• TableName: (optional, defaults to file name) name of the staging table to bulk copy data into
• TargetTableName: (optional, defaults to null) name of a target table to ensure each text data column exists in
• HeaderRow: (optional, defaults to true) whether the text file contains a header row
• Delimiter: (optional, defaults to ',') the delimited used in the text file
• CharSet: (optional, defaults to 'OEM') character set used by the text file
• InferSchema: (optional, defaults to 'NoMapping')
• NoMapping: Import File Map rows are created if there are none
• Always: Import File Map rows are always created (if not present)
• Never: Import File Map rows are never created automatically

When configuring a statement, several SQL substitution patterns are available:

• {Import.StagingTable} => name of the staging table
• {Import.TargetTable} => name of the target table
• {Import.AllColumns} => emit a list of staging table columns
• {Import.Select.*} => emit select clause for columns that have a destination beginning with *
  • this emits the staging table column names
• {Import.Insert.*} => emit insert clause for columns that have a destination beginning with *
  • this emits the destination table column names
• {Import.Update.*} => emit update clause for columns that have a destination beginning with *
  • this emits {DestinationColumn} = {StagingColumn}
• {Import.Foreach.*}...{Import.Endeach.*} => for i=0..10, repeat clause for columns that have a destination beginning with *, and the source contains i
  • {Import.X} => i (iteration) during a for each loop
  • this is currently limited to a maximum of 10 iterations

Assume a client provides a text file containing the following columns, and a power user has defined a destination mapping in the associated ImportFileMap rows:

• Property_Address: maps to Property.Address
• Property_City: maps to Property.City
• Account_Number: maps to Loan.Loan
• Account_Balance: maps to Loan.UPBAmount
• Borrower1_First: maps to Contact.FirstName
• Borrower1_Last: maps to Contact.LastName
• Borrower2_First: maps to Contact.FirstName
• Borrower2_Last: maps to Contact.LastName
• Borrower3_First: maps to Contact.FirstName
• Borrower3_Last: maps to Contact.LastName

The following statement:

INSERT INTO Property ({Import.Insert.Property}) SELECT {Import.Select.Property} FROM {Import.StagingTable}
INSERT INTO Loan ({Import.Insert.Loan}) SELECT {Import.Select.Loan} FROM {Import.StagingTable}
{Import.Foreach.Borrower}
INSERT INTO Borrower ({Import.Insert.Borrower}, Sequence) SELECT {Import.Select.Borrower}, {Import.X} FROM {Import.StagingTable}
{Import.Endeach.Borrower}

will emit (assuming the staging table name is 'MyStagingTable'):

INSERT INTO Property (Address, City) SELECT Property_Address, Property_City FROM MyStagingTable
INSERT INTO Loan (Loan, UPBAmount) SELECT Account_Number, Account_Balance FROM MyStagingTable
INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower1_First, Borrower1_Last, 1 FROM MyStagingTable
INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower2_First, Borrower2_Last, 2 FROM MyStagingTable
INSERT INTO Borrower (FirstName, LastName, Sequence) SELECT Borrower3_First, Borrower3_Last, 3 FROM MyStagingTable

In the future, if the text file is extended to include:

• Account_Status: maps to Loan.Status
• Property_Zip: maps to Property.PostalCode
• Borrower4_*: maps to (same contact mappings)
• Borrower5_*: maps to (same contact mappings)

no changes to the statement will need to be made.

Some columns may need values translated between the staging an destination table. From the example above:

• Assume Loan.Status need to map 'A' to 'Active', 'I' to 'Inactive'
• Assume Loan.Loan should be 10 characters long, but Account_Number truncates leading zeros

Such translations should be handled in the statement like this:

-- Translate data before copying it elsewhere
UPDATE {Import.StagingTable} SET
  Account_Status = CASE Account_Status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE Account_Status END,
  Account_Number = REPLACE(STR(Account_Number, 10), SPACE(1), '0')

-- Okay, now copy elsewhere
INSERT INTO Property ({Import.Insert.Property}) SELECT {Import.Select.Property} FROM {Import.StagingTable}
INSERT INTO Loan ({Import.Insert.Loan}) SELECT {Import.Select.Loan} FROM {Import.StagingTable}
{Import.Foreach.Borrower}
INSERT INTO Borrower ({Import.Insert.Borrower}, Sequence) SELECT {Import.Select.Borrower}, {Import.X} FROM {Import.StagingTable}
{Import.Endeach.Borrower}

The ImportFile/Apply method signature supports bulk-applying a method signature to every row returned by a statement. Examples include:

• Add a workflow to all loans matching some search criteria
• Add a task to all foreclosures matching some search criteria
• Send an email to all contacts matching some search criteria

The method signature for ImportFile/Apply is:

ImportFile/Apply?ClassName={ClassName}&Operation={Operation}&{Parameters to pass to ClassName/Operation}&Signature={Method Signature}

The Signature parameter may use substitution in it's query string of parameters. If substitution is specified, the parameters are substituted against the row of data returned by ClassName/Operation. For example:

ImportFile/Apply?ClassName=Loan&Operation=SmartSearch&SmartSearch=123123&Signature=Decision/Save?Object=Loan%26ObjectID={LoanID}%26Template=Hello World

does the following:

• Executes Loan/SmartSearch?SmartSearch=123123
• For each row returned, builds a method signature from Decision/Save?Object=Loan&ObjectID={LoanID}&Template=Hello World
  • this will replace {LoanID} with the LoanID column returned by Loan/SmartSearch
  • if the Hello World workflow already exists for a loan returned by the operation, normal repeatability rules apply
  • any column from Loan/SmartSearch may be used in this method signature

A more complex example is:

ImportFile/Apply?ClassName=Contact&Operation=Search&State=MA&Signature=Message/Send?ToAddress={Email}%26Subject=Hello {FirstName}%26BodyText=Welcome to QBO, {Suffix} {LastName}!

does the following:

• Executes Contact/Search?State=MA
• For each row returned, builds a method signature from Message/Send?ToAddress={Email}&Subject=Hello {FirstName}&BodyText=Welcome to QBO, {Suffix} {LastName}!
  • this will replace {Email}, {FirstName}, {Suffix} and {LastName} with the matching column returned by Contact/Search

The Signature parameter is itself a query string. If you're typing the full URL to leverage ImportFile.ashx/Apply, you must replace & with %26 manually. For example:

// The value of Signature parsed on the server will be Decision/Save?Object=Loan
Signature=Decision/Save?Object=Loan&ObjectID={LoanID}
// The value of Signature parsed on the server will be Decision/Save?Object=Loan&ObjectID={LoanID}
Signature=Decision/Save?Object=Loan%26ObjectID={LoanID}: 

If you are not typing the Signature query string directly, but use javascript to calculate the value being passed over the wire, javascript will take care of this substitution for you! For example:

<form>
  <input type="hidden" name="ClassName" value="Loan"/>
  <input type="hidden" name="Operation" value="Search"/>
  <input type="hidden" name="State" value="MA"/>
  <input type="hidden" name="Signature" value="Decision/Save?Object=Loan&ObjectID={LoanID}"/>
</form>

When parsing this data to pass to the server, the browser / javascript will recognize that the value being passed for the Signature parameter includes ampersands, and will automatically substitute & with %26 for you. See Templates/Application/GenericTemplate.Popup.xslt for an example.

If you wish to include an audit trail with your use case, you can call ImportFile/Batch instead of ImportFile/Apply. ImportFile/Batch will:

• Create an ImportFile record,
• Create ImportFileQueue records for each row returned by the query, and
• Queue ImportFile/Import

This allows you to have an audit trail (in the form of ImportFileQueue rows), and breaks the activity into smaller chunks (each row's method signature is queued as a separate job).

Import module functionality can be extended by creating new classes that implement the IImportEngine interface. For example, custom binary files creates by mainframe export routines could be processed by creating a C# class along these lines:

using qbo.Import;
namespace acme.Import.Engines
{
	public class MainframeEngine: AbstractEngine 
	{
        public override async Task<bool> ImportAsync(IDictionary<string, object> parameters)
		{
			// QBO handles the storing, reading, and writing of the binary file with GetImportStreamAsync() for you. 
			using (Stream importStream = await GetImportStreamAsync()) 
			{
				// add custom code here to process the mainframe binary file
			}
		}
	}

}

Once such a plugin is uploaded to a QBO server farm, make it available to power users by adding an ImportEngine configuration node:

<ConfigurationEntryCollection>
	<ConfigurationEntryItem>
		<ConfigurationEntry>ImportEngine/MainframeEngine</ConfigurationEntry>
		<Source>ImportEngine.config</Source>
		<ConfigurationType>qbo.Import.Configuration.ImportEngineCollection</ConfigurationType>
		<ConfigurationKey>MainframeEngine</ConfigurationKey>
		<ConfigurationXml>
			<ImportEngine Name="MainframeEngine" Type="acme.Import.Engines.MainframeEngine, acme.Import.Engines" />
		</ConfigurationXml>
	</ConfigurationEntryItem>
</ConfigurationEntryCollection>

Lastly, create a template (or multiple templates) to leverage this new MainframeEngine:

<ImportFileTemplateCollection>
	<ImportFileTemplateItem>
		<ImportFileTemplate>Mainframe Import</ImportFileTemplate>
		<ImportEngine>MainframeEngine</ImportEngine>
		<Async>True</Async>
		<Repeatable>One</Repeatable>
		<SubscriberID>acme.com-ImportFileTemplate-MainframeEngine</SubscriberID>
	</ImportFileTemplateItem>
</ImportFileTemplateCollection>