Scripting example in Jitterbit App Builder - Custom Excel import

This plugin is designed to import a formatted excel document.

It allows for data indented 1 column in, ignoring title formatting before getting to headers, header fields which span 2 rows, skips over rows with subtotals/grand total, and ignores an existing footer. On Business Object running event:

BusinessObjectID provides the TableID of target for imported data.

File provides the file to be imported (binary data field)

AttachmentID and AttachmentTypeID: GUIDs to identify source of imported data

if (columnName == "Billing") //where "Billing" is the value in second column for the beginning of first header row.

if (firstColumnName == "Grand Total:") //first column first row value after all records of desired imported data

if (SecondColumnName == "Invoice Number:") //first column value of last row of title data before first header rowAt the bottom, target columns get matched to a count of the column number of excel for source data. i.e. the following line will indicate to get the 10th column of data and insert it into the "Product" field in target Business Object

Use case

This could be used when another service or an internal business process is managed via a formatted excel, that is maintained and later needs to be ingested into App Builder as a data source for a on-going business process. This is helpful during a transition when a business process is replaced in App Builder in portions, rather than its entirety at once, and this is becomes the best option to ensure accurate and seamless continuity of data with no additional steps for the business to format data before bringing into App Builder for updated processes.

Plugin references

Column	Data Type	Description
`BusinessObjectID`	`Unique ID`	provides the TableID of target for imported data
`File`	`binary data field`	provides the file to be imported
`AttachmentID`	`Unique ID`	identify source of imported data
`AttachmentTypeID`	`Unique ID`	identify source of imported data

Custom Excel import script

#r "ExcelDataReader.dll"
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Vinyl.Sdk.Filtering;
// TARGET
// The MRP table it will populate
string strBusinessObjectID;
strBusinessObjectID = Row["BusinessObjectID"].Value.ToString();
Guid targetMRPTableId = Guid.Parse(strBusinessObjectID);
// SOURCE
// File to be imported
var file = (byte[])Row["File"].Value;
string strAttachmentID;
strAttachmentID = Row["AttachmentID"].Value.ToString();
string strAttachmentTypeID;
strAttachmentTypeID = Row["AttachmentTypeID"].Value.ToString();
//Guid attachmentID = Guid.Parse(strAttachmentID);
// SERVICES
var tableService = Services.GetService<ITableService>();
var eventService = Services.GetService<IEventService>();
// EVENT TABLES
var mrpTable = await GetEventTable(eventService, tableService, targetMRPTableId);
// Read file
using (var stream = new MemoryStream(file))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        // Generates the Primary Key for the SRP as it will also be used as a FK
        // on the MRP
        //var id = Guid.Parse(strAttachmentID);
        await ConsumeMRP(eventService, mrpTable, reader, strAttachmentID, strAttachmentTypeID);
    }
}
/// <sumary>
/// For a given table id, returns its EventTable
/// </sumary>
private async Task<EventTable> GetEventTable(
    IEventService eventService,
    ITableService tableService,
    Guid tableId)
{
    // This workaround is not needed anymore in the latest 2.7 hotfix
    // but anything before then, to get the EventTable from a given
    // tableId, we need to raise a filter event with no rows
    var table = tableService.GetTable(tableId);
    var filter = table.CreateFilter();
    filter.Limit = 0;

    var eventTable = await eventService.InvokeFilterEventAsync(filter);
    return eventTable;
}
/// <sumary>
/// Tries to set a cell value, checking if column is available first
/// </sumary>
private void TrySetValue(EventRow row, string columnName, object value)
{
    if (row.TryGetCell(columnName, out EventCell cell))
    {
        cell.Value = value;
    }
}
private string GetColumnName(object value)
{
    string str = Convert.ToString(value ?? "");
    str = Regex.Replace(str, " *[\r\n]+", " ");
    return str.Trim();
}
/// <sumary>
/// Populate the MRP until the first cell is empty
/// </sumary>
private async Task ConsumeMRP(
    IEventService eventService,
    EventTable importTable,
    IExcelDataReader reader,
    string attachmentID,
    string attachmentTypeID)
    {
    while(reader.Read())
    {
        var columnName = reader
            .GetValue(1)?
            .ToString()
            .Replace(":", "")
            .Trim();

        if (columnName == "Billing") {
            // MRP started
            break;
        }
    }


    var fields = new List<string>();

        for (var index = 1; index < reader.FieldCount; index++)
        {
            fields.Add(GetColumnName(reader.GetValue(index)));
        }
    if (!reader.Read())
    {
        // MRP cut short? Throw exception?
        return;
    }
    // Append sub-title
    for (var index = 0; index < fields.Count; index++)
    {
            if (!reader.IsDBNull(index+1))
            {
                fields[index] += " " + GetColumnName(reader.GetValue(index+1));
            }
    }

    int rowcount= 0;

    while(reader.Read())
    {
        rowcount = rowcount+1;
        string SecondColumnName="";
        string firstColumnName="";

            if(! reader.IsDBNull(1))
            {
                SecondColumnName = reader.GetValue(1).ToString().Trim();
            }
            else
            {
                SecondColumnName ="";
            }
            if(! reader.IsDBNull(0))
            {
                firstColumnName = reader.GetValue(0).ToString();
            }
            else
            {
                firstColumnName="";
            }



        if (firstColumnName == "Grand Total:")
        {
            break;
        }
        if (SecondColumnName == "Invoice Number:")
        {
            continue;
        }
        if (SecondColumnName == "")
        {
            continue;
        }

            // Creates a new row in the staging table
            var dataRow = await eventService.InvokeNewEventAsync(importTable);

            dataRow["stGarnishmentID"].Value = Guid.NewGuid();
            dataRow["AttachmentID"].Value = attachmentID;
            dataRow["AttachmentTypeID"].Value = attachmentTypeID;
            //testRow6["Product"].Value = reader.GetValue(10);
         //   await eventService.InvokeInsertEventAsync(testRow6);

            //row["Invoice Number"].Value=reader.GetValue(1);

            for (var index = 0; index < fields.Count; index++)
            {
                var columnName = fields[index];
                var value = Convert.ToString(reader.GetValue(index+1));
                TrySetValue(dataRow, columnName, value);
            }
         await eventService.InvokeInsertEventAsync(dataRow);



    }

}