Set up the tables for EZ File Importer

The following tables are used by the EZ File Importer extension and are automatically installed and related together within your application.

Table	What it's for	Related to
Configuration Groups	The control (master) table that brings everything together.	—
File Imports	Holds the spreadsheet you want to import. Older apps and articles call this table Import Files.	Configuration Groups
Import Configurations	One record for each import: the target table, the header and data rows, and the sheet.	Configuration Groups
Field Mappings	Which spreadsheet column goes to which field.	Import Configurations
Import Run Details	A log with one record for each import run.	File Imports

The following sections describe each table and how to set it up.

Important
EZ File Importer finds the fields in these tables by their names. Don't rename or delete the fields the installation wizard created, in particular Configuration Group in the Import Configurations and Field Mappings tables, and the fields in Import Run Details.

Configuration Groups

The Configuration Groups table is a control table that brings together the File Imports and Import Configurations tables. This table acts almost as a central location, also known as the Master table, for configuring the imports needed.

To get started, add a record and then follow the steps for each of the other dependent tables: File Imports and Import Configurations.

Field or button	What it's for
Configuration Group (required)	The name of the group. Choose a meaningful name; it's also the value passed as `impconfiggroup`.
Import Files (report)	The File Imports records related to this group, with their Related Key Value and Related Configuration Group.
Add Import File (button)	Opens a new File Imports record already related to this group.
Import Configurations (report)	The Import Configurations records related to this group, with their Type, Target Table Name, and Target Table DBID.
Add Import Configuration (button)	Opens a new Import Configurations record already related to this group.

Note
File Imports is covered next since it's the second table in this group of five. We recommend building your Import Configuration records first (covered in the section after it), then returning here to define your File Imports record.

File Imports

The File Imports table is where the file is loaded or saved. This table basically contains a file attachment field that holds the file that needs to be imported. Additionally, this table has 2 related tables. The first is the Configuration Groups master table, and the second is a dependent table containing records for each time an import is executed, called Import Run Details—essentially a log table.

Each File Imports record has these fields:

Field	What it's for
Configuration Group	The name of the related configuration group, looked up from Related Configuration Group. For example, Import Assets file.
File (required)	The file attachment that holds the spreadsheet to import.
File Name	The name of the uploaded file, for example Assets_import v4.xlsx.
Refresh (button)	Reloads the record so you can see the import's progress. It appears once a file has been uploaded.
Import Generation Arguments	The formula that tells the extension what to do. See below.
Related Key Value and Related Key Value (Formula)	An optional value for the file that you can use in your own formulas or as the `relkeyval` parameter.
Related Configuration Group (required)	The configuration group whose import configurations process this file.
Import Run Details (report)	The run records for this file, with status, notes, records processed, start and end times, duration, sheet number, and related import file.

Important
One very important field installed on this table is Import Generation Arguments. This field is sent along with the Email Notification and instructs the extension with the following information:

The following is a look at the formula text field, with comments on each line describing what that value represents. The wizard also adds a configid parameter, which you leave in place.

"clientid=Q2"                                                  //Client ID
& " appid=" & AppID()                                          //Application ID
&" dbid=" & Dbid()                                            //DBID of the File Imports table
&" recid=" & [Record ID#]                                     //Record ID of the import file
&" fafid=6"                                                   //FID of the File Attachment field
& " impconfigdbid=" & [_DBID_IMPORT_CONFIGURATIONS]            //DBID of the Import Configurations table
&" impconfiggroup=" &"\"" & [Configuration Group] & "\""  //Import Config Group used in processing the file
& " fldmapdbid=" & [_DBID_FIELD_MAPPINGS]                      //DBID of the Field Mappings table
&" impadbid=" & [_DBID_IMPORT_RUN_DETAILS]                    //DBID of the Import Run Details table
&" imparelfid=13"                                             //FID of the field that relates File Imports to Import Run Details
& " apptoken="                                                 //Application token (if required)
&" type=ezfileimportermultisheet"                             //Tells the email address which extension to run

The type value is ezfileimportermultisheeteu for EU accounts. Each parameter is described in EZ File Importer formula URL parameters.

Here is a sample of what the field's value might look like with data (the IDs are examples):

clientid=Q2 appid=bqexampleapp dbid=bqexamplefiles recid=5 fafid=6 impconfigdbid=bqexamplecfg impconfiggroup="Import Assets file" fldmapdbid=bqexamplemap impadbid=bqexamplerun imparelfid=13 apptoken= type=ezfileimportermultisheet

Tip
A Refresh button is installed in the File Imports table as a way to check if the import is completed. Once a file is added or modified within a record in this table, the import process is fired via Email Notification, so it's often handy to select the Refresh button to see what has occurred.

Import Configurations

The Import Configurations table stores all the key information about each import. Things like the DBID of the table into which we need to import data, Field Mapping setting (like by Column Name or by Column Letter), Sheet Number of the Excel file that contains the data, etc. See the following for a detailed rundown of all fields and what they do:

Note
If multiple tabs need to be imported into multiple tables, you need multiple Import Configuration records.

General settings—Required

These fields are the base set needed to define the related Configuration Group record as well as the main table that the import affects. This defines the fields of the import and where they get imported in the target Quickbase table, like Field Mappings with field id #'s, and so on.

Field	What it's for
Related Configuration Group (required)	The configuration group (master record) this import configuration belongs to. The Configuration Group field shows its name.
Type	A descriptive name for the import, for example Assets excel file import.
Target Table Name	The name of the table the data is imported into (free-form text, for your reference), for example Assets.
Target Table DBID (required)	The Quickbase table ID of the target table. To find it, open the table and copy the ID from the browser's address bar: in `https://yourcompany.quickbase.com/db/<table ID>?a=td`, the table ID is the part after `/db/`. The View Target Table button (field Link To Target Table) opens the table.
Field Mapping Info (required)	How the field mappings refer to spreadsheet columns: Map_Fields_By_Column_Name or Map_Fields_By_Column_Letter.
File Contains a Header Row	Select if the spreadsheet has a header row with column names. Select it whenever you map by column name.
Header Row #	The number of the row that holds the column names (for example `1`). Data starts on the next row unless you set Starting Row #.
Sheet Contains Empty Rows	Select if the data rows can contain empty rows. Otherwise the import stops at the first empty row.
Sheet Number	The position of the sheet (tab) to import, counting from `1`. Leave it blank for the first sheet.
Import From Last Sheet	Select to always import from the last sheet instead of the sheet number.
Import Date FID	The field ID, in the target table, that stores the date and time of the import.
Import File Reference FID	The field ID, in the target table, that stores the Record ID# of the File Imports record the data came from.

These fields allow for some advanced functionality, like searching capability. Since the extension processes data row by row from the import spreadsheet, it can query any table in Quickbase using any column in the spreadsheet to find data and update records during the import.

Field	What it's for
Search Table Name	The name of the table to search (free-form text, for your reference).
Search Table DBID	The Quickbase table ID of the table to search. The View Search Table button (field Link To Search Table) opens it.
Query To Search	A Quickbase query on the search table. Put the spreadsheet column whose value should be compared in square brackets, using the column's letter or name as set in Query Mapping.
Query Mapping	Whether the brackets in Query To Search hold a column name (By_Column_Name) or a column letter (By_Column_Letter).
Search Table Value to Return (FID)	The field ID, in the search table, whose value is taken from the record the query finds. If you leave it blank, the Record ID# of the found record is used.
Target Table Field to Update (FID)	The field ID, in the target table, that receives the value returned from the search table.
Search Columns Contain Empty Cells	Select if the columns used in the query can have empty cells. Otherwise processing stops at the first empty row.
Same Query Result for ALL Rows	When this is checked, whatever the search returns from the first query is used on every record, updating the field ID# in Target Table Field to Update (FID).
Update Records Only	When this field is checked, the process only updates records found by the search query. A spreadsheet row with no matching record is skipped and not added.

For example, with these settings:

Field	Value
Search Table Name	Test
Search Table DBID	the table ID of the Test table
Query To Search	`{'8'.EX.'[B]'}`
Query Mapping	By_Column_Letter
Search Table Value to Return (FID)	7
Target Table Field to Update (FID)	1035

for each row in the spreadsheet, EZ File Importer searches the Test table for the record whose field 8 equals the value in spreadsheet column B. It then takes the value of that record's field 7 and places it in field 1035 of the record being imported.

To also update the record the search finds, add a field mapping with Update Search Table? selected. See Field Mappings.

Data rows—Starting Row # and Ending Row #

Specify the data region using a Starting Row # and Ending Row # (optional):

Starting Row #: the first row of data in the spreadsheet.
Ending Row #: the last row of data in the spreadsheet.

Leave both blank if the data starts on the first row, or on the second row if the file has a header row. Add a Starting Row # if the data begins at row 3 or greater. The Ending Row # is optional unless you know where the data rows end. For an example, see Specify a specific location within the spreadsheet for import.

Field Mappings

The Field Mappings table, which is shown on the import configuration as a list of its field mapping records, is where you specify which column's data from the spreadsheet is imported into which field of the Quickbase table. Use the Add Field Mapping button on the import configuration to add a mapping.

Field	What it's for
Column Name/Letter (required)	The column letter (for example `A`) or the column name from the spreadsheet, as set by Field Mapping Info on the import configuration.
Field ID	The field ID of the field in the target table that receives the column's data.
Write A Value?	Select to write a fixed value instead of reading a column.
Value To Write	The fixed value to write when Write A Value? is selected.
Update Search Table?	Select to write the value to the record found in the search table (see the search settings above), using Field ID as a field in the search table.
Related Import Configuration (required)	The import configuration this mapping belongs to.

Important
Two mappings in the same import configuration can't write to the same field. If they do, the import fails with the note You have more than one column mapping to the same field.

For example, an import configuration for an assets spreadsheet has 15 field mappings. Fourteen map a column letter to a field, and the last one writes a fixed value:

Column Name/Letter	Field ID	Write A Value?
A	1016
B	1004
C	1005
D	1020
E	1010

Import Run Details

The Import Run Details table is related to the File Imports table as a dependent table that allows you to view what has occurred in each import run. EZ File Importer adds one record for each import configuration it processes, so a file with two sheets imported by two configurations adds two records. It provides details such as:

Whether the process ran successfully
Number of records processed
Start and end times

Field	What it shows
Status	The result, followed by an underscore and the date and time. See the statuses below.
Notes	Details about problems, such as a column that wasn't found or the reason a run failed.
Records Processed	The number of spreadsheet rows imported. 0 when the run failed.
Start Time, End Time, Run Duration	When the run started and ended, and how long it took.
Sheet Number	The sheet number the run used. A run for the first sheet shows 1.
Related Import File	The File Imports record the run belongs to.

For example, a file with two sheets might produce these records:

Status	Records Processed	Sheet Number
Process Completed _ 9/28/2022 2:42:00 PM	27	1
Process Completed _ 9/28/2022 2:42:00 PM	6	2

Statuses and notes

Status starts with	What it means
Process Completed _	The rows were imported.
Process Completed with Exceptions _	The rows were imported, but something was skipped. Notes lists it, for example Specified Column doesn't exists in the Spread Sheet : <column>.
Process Failed _	The import didn't happen. Notes has the reason, for example Please upload a proper file with valid extension i.e (.xls,.xlsx or .csv or .xlsm), You have more than one column mapping to the same field, or Quickbase's own error text when it refuses the import.

For what to do about each one, see EZ File Importer troubleshooting.