EZ File Importer features and functionality

  • 5 minute(s) read
Prev Next

Features and functionality

This page describes what EZ File Importer can do and which settings control each behavior. The settings live in the Import Configurations and Field Mappings tables that the installation wizard adds to your app; every field is described in Set up the tables for EZ File Importer.

Supported file types

  • Excel: .xlsx, .xls, and .xlsm. You can import from one or more sheets in the same file.

  • Text: .csv.

Any other file type stops the import, and Import Run Details records a failed run.

Import data—update or add records

  • When importing data into a Quickbase table, if the key exists within the spreadsheet, any data that matches is updated in place. If the record is not found, it is added to the table. This is the same functionality that applies to the standard Quickbase import as well. To update existing records, map a spreadsheet column to the table's key field (Record ID# unless you've chosen another key).

Use column names or column letters

  • When you define the field mappings for an import configuration, you choose in Field Mapping Info whether to refer to spreadsheet columns by letter or by name (Map_Fields_By_Column_Letter or Map_Fields_By_Column_Name). Then you enter the letter or the name in each mapping's Column Name/Letter field. See Field Mappings. Using a column name is handy, especially if the spreadsheet may get rearranged from time to time.

    For example, take a spreadsheet whose first row has these headings:

    Column letter

    Column name (row 1)

    A

    Application ID

    B

    Closing Details

    C

    Selected S…

    D

    Damaged Residence Street

    To import the first column, enter A when you map by letter, or Application ID when you map by name.

    When you map by name, EZ File Importer ignores upper and lower case, extra spaces at the ends, and commas in the heading. If a column you named isn't in the spreadsheet, the rest of the file is still imported, and the run's Notes field lists the missing column.

Specify a specific location within the spreadsheet for import

  • If you need to bypass header rows or footer rows in your spreadsheet, you can specify a Starting Row # and Ending Row # in your import configuration. You set them in the import configuration's fields for the data rows.

    Note

    If you don't know the ending row, you can specify the Starting Row # and leave the Ending Row # blank.

    For example, an inventory spreadsheet has a title and filter heading in rows 1 to 3, column headings in row 4, 26 item rows in rows 5 to 30, and a TOTAL (26) row in row 31:

    Row

    Contents

    1–3

    Title (Inventory List) and a Highlight items to reorder? checkbox

    4

    Column headings: Inventory ID, Name, Description, Unit Price, Quantity in Stock, Inventory Value, Reorder Level, Reorder Time in Days, Quantity in Reorder, Discontinued?

    5–30

    The items, for example IN0017 to IN0006

    31

    TOTAL (26), with the totals of the quantity and value columns

    To import only the items, set Starting Row # to 5 and Ending Row # to 30. The title, the headings, and the total row are skipped.

    If you leave both fields blank, the import starts on the first row, or on the second row if the file has a header row. EZ File Importer stops at the first completely empty row unless you select Sheet Contains Empty Rows.

Import into multiple tables from multiple tabs within a spreadsheet

  • If your spreadsheet has multiple tabs of data and you want to direct them to multiple tables within Quickbase, you need to create multiple Import Configurations under one Configuration Group master record, one for each tab. Each of the Import Configurations can have its own set of field mappings, and so on. In each import configuration, enter the tab's position in Sheet Number (1 is the first tab), or select Import From Last Sheet to always use the last tab.

  • You can also import one sheet into multiple tables: create an import configuration for each table with the same Sheet Number.

  • Each import configuration that runs adds its own record to Import Run Details, with that configuration's sheet number.

Define a static data field for import

  • If you would like to add a field of data that doesn't exist in the import file as a column, you can define one yourself using the Field Mappings table. All you need to do is create a field mapping, enter any column letter in Column Name/Letter, select the Write A Value? checkbox, set the Value To Write field to the value you want imported, and set Field ID to the target field. The value is written to every row that is imported.

    For example, this mapping writes the text ON HOLD into field 1033 of the target table for every imported row:

    Field

    Value

    Column Name/Letter

    O

    Field ID

    1033

    Write A Value?

    Selected

    Value To Write

    ON HOLD

Record when and from which file a record was imported

  • Enter a field ID in Import Date FID to have each imported record store the date and time of the import, in your app's time zone.

  • Enter a field ID in Import File Reference FID to have each imported record store the Record ID# of the File Imports record that the file came from.

Search and update data during import

  • Included as part of the definition of the Import Configuration record is a section to allow for searching of data in Quickbase as each row in the spreadsheet is processed for import. The search can be performed on the same table as the import or another table. The search capability allows you to configure what table and query to use, as well as what column in the spreadsheet to use for the query. Then, the configuration uses a field ID# from the table that is being searched in order to update a field in the table being imported into. This is explained within Set up the tables for EZ File Importer (search settings).

Various ways to execute the import

  1. Add a file to a record in the File Imports table and save the record. The email notification on that table starts the import automatically according to your configuration settings. Select Refresh to see the results in Import Run Details.

  2. You can also automate the import so that when the file is uploaded it is automatically executed. The import is triggered when the record is saved.

    • Using our Email Parser extension, an email sent inbound is saved to the File Imports table, where the import process starts automatically. This has been helpful for the total automation of processes that have emails automatically sent to a mailbox by our Email Parser monitors, then our Email Parser saves to the import table and the importation happens automatically.