Import from Excel into Jitterbit App Builder

App Builder supports the ability to import data from Microsoft Excel. There are two different methods you can use to import from Excel, depending on your business case scenario you can select which is best for your needs. Both import from Excel options are described in this article.

App Builder requires that the columns in the Excel file being uploaded align to the Column Heading values defined in the App Builder Table structure being targeted to import into (or created if it is a new Table). App Builder also requires that the Excel Header values appear on the first row of the Excel file.

When you are importing data from Excel, App Builder assumes that the information being imported resides on the first tab of the resultant Excel file. At this time you cannot specify a different tab in the Excel file to import from.

Use Excel spreadsheet to import a table

1. After accessing the Table wizard, select the Excel Spreadsheet option
2. Click Next
3. Click the Browse link and locate the file to upload
4. Select the file and click Open
5. Confirm the upload is complete and click Next

6. Review the data mapping settings App Builder will use to import the file and make any changes necessary:

   1. Table Name = name that will be assigned to the Table
   2. File Type = represents the file extension for upload. Can be .csv, .txt, .xls, .xlsm, or .xlsx
   3. Delimiter Type = used for CSV and is typically a comma, if you are importing Excel this will be ignored
   4. Contains Header = Boolean field that indicates if file contains a header row
   5. Header Row = Numeric field that indicates the row representing the Header values
   6. Starting Data Row = Numeric field that indicates the row where data values start

   7. Customize Columns = Options include Auto or Customize. Defaults to Auto.

      • If you select Customize, then click Next, you can verify column names and data types are correct as well as preview the first few rows of the import. Click Previous to correct any issues with columns and data types, otherwise, click Import.

7. Click Import

8. Click Create Page to initiate the Create Pages wizard

9. First select a Menu to link the new page to. Options:

   1. Home = new page will be linked from the Home page
   2. Configuration = new page will be linked under the Configuration menu
   3. Reports = new page will be linked under the Reports menu
   4. None = no navigation link will be generated for the new page

10. Click Next

11. Review the available Panel Types and select the desired option

    1. Click Previous if any modifications are required

12. Click Next to proceed

13. the updates App Builder will make based on the information provided

    1. Click Previous if any modifications are required

14. Click Create Pages

Import via an event option

If you have a regular data feed that needs to be updated, importing from Excel via an Event is the option you'll want to use. Here we will assume you have a pre-existing Table defined in App Builder that matches the structure of the Excel file you're looking to import. The columns in the Excel file must align to the Column Heading values defined in the App Builder Table structure being targeted to import into. App Builder also requires that the Excel Header values appear on the first row of the Excel file.

It is recommended to create an Excel template with the correct headers, paste the data you want to import under those headers, and then bring them into the system using the App Builder Import Plugin.

The following prerequisites must be configured to import an Excel file into App Builder:

1. Create the File System Server (local or network) to store file(s)
2. Create the Data Storage to temporarily store the data
3. Link the Data Storage to the application you'll be importing to

For help setting up the File System, please refer to the App Builder file system article.

Once the prerequisites are configured, you can setup an Event and Action to execute the Excel file import.

1. Create an Event (e.g., named Import)

2. Create an associated Action with the following settings:

   • Type: Import
   • Import Type: type of file you are importing. This is CSV or Excel for Excel
   • File System: the file system data source you'll be reading the file from
   • Truncate: if checked, this setting will delete the contents of the target table
   • Target Table: the table the records will be stored in
   • Delimiter: used for CSV and is typically a comma, if you are importing Excel leave this blank
   • Completed Data Source: file location where the import file will be moved to. Typically this is the same value as the File System.