Skip to main content
PrimeConnect
PrimeConnect
Importing Data

Field Mapping & Saved Templates

11 min readLast updated Oct 12, 2026Intermediate

How Auto-Matching Works

When you upload a file and reach the mapping step, PrimeConnect automatically reads every column header in your spreadsheet and attempts to match each one to the corresponding QuickBooks field for the selected entity type. This process, called auto-matching, uses fuzzy string comparison to handle variations in naming.

How it works

PrimeConnect compares your column header text against a list of known QuickBooks field names and common aliases. The comparison is case-insensitive and ignores special characters, so minor differences in naming, spacing, or punctuation are tolerated. Here are some examples of how auto-matching resolves common column names:

Your Column HeaderMatched QuickBooks Field
"Invoice Number", "Inv #", "InvoiceNo"RefNumber
"Customer Name", "Customer", "Bill To"CustomerRef
"Date", "Invoice Date", "TxnDate"TxnDate
"Amount", "Total", "Line Amount"Amount
"Item", "Item Name", "Product"ItemRef
"Description", "Desc", "Memo"Description / Memo
"Quantity", "Qty"Quantity
"Rate", "Price", "Unit Price"Rate
"Account", "Account Name"AccountRef

Match indicators

After auto-matching completes, each column shows one of two states:

  • Green checkmark — PrimeConnect found a confident match. The matched QuickBooks field is displayed in the dropdown. You should still verify it is correct.
  • Empty dropdown — no match was found. You need to manually select the correct field or leave it unmapped (see Handling Unmapped Columns).
Tip: Always review auto-matched fields before proceeding. Auto-matching is accurate in most cases, but ambiguous column names (like "Name" or "Type") may be matched to the wrong field. A quick review takes a few seconds and prevents import errors.

Manual Field Mapping

When auto-matching does not find a match for a column, or when you need to override an incorrect auto-match, you can manually assign the mapping.

How to map a field manually

  1. Find the column in the mapping list that needs to be mapped (or re-mapped).
  2. Click the dropdown next to the column name. This opens the list of available QuickBooks fields for the selected entity type.
  3. Search or scroll to find the correct field. The dropdown includes a text filter input at the top — type a few characters to narrow down the list (e.g., type "ref" to see all reference fields).
  4. Click the desired field to select it. The column is now mapped.

Changing an existing mapping

To change a mapping that was set by auto-match or a previous manual selection, simply click the dropdown again and choose a different field. The previous mapping is replaced instantly.

Clearing a mapping

To unmap a column (so that it is ignored during import), click the dropdown and select the blank/empty option at the top of the list. The column will then be treated as unmapped.

Required vs Optional Fields

QuickBooks requires certain fields to be present for each record type. In the mapping screen, these fields are visually distinguished so you know which mappings are mandatory.

Required fields

Required fields are marked with a red asterisk (*) next to the field name. The Next button will be disabled until all required fields have a mapping assigned. You cannot proceed to the validation step with unmapped required fields.

Required fields vary by entity type. Here are some common examples:

Entity TypeRequired Fields
CustomersName
VendorsName
InvoicesCustomerRef, at least one line item (ItemRef + Amount or Quantity + Rate)
BillsVendorRef, at least one line item
Journal EntriesAccountRef, Amount, debit/credit designation
ItemsName, ItemType
AccountsName, AccountType

Optional fields

All other fields are optional — mapping them adds more data to the QuickBooks record, but the import will succeed without them. Optional fields include things like memo, description, due date, PO number, ship address, terms, and class.

Editing Rule Inputs and Excluded Rows

Edit the original source cell on File Updates to refresh that row's read-only rule results. Edits to a column used for concatenation also refresh its result. If a skip condition starts matching, the row is excluded; when none of its skip rules match anymore, the exclusion clears.

Rows marked Skipped by mapping rule retain their cell values and receive no data-type or reference validation errors. They create no missing customers, items, accounts, or other references in QuickBooks.

A constant can populate a field without a source column on every nonblank file row. An unmapped note counts as file data; internal row identifiers and status fields do not make an empty file row populated.

Saving Mapping Templates

If you regularly import files with the same column structure, you can save your mapping configuration as a template for instant reuse in future imports.

How to save a template

  1. Configure all your field mappings in Step 3 of the import wizard.
  2. Click the Save button at the top of the mapping screen. The Save Map As dialog opens.
  3. Enter a descriptive name for the template (e.g., "Monthly Invoices from Accounting", "Vendor Import from ERP", "Customer List Update").
  4. Click Save. The template is stored locally in PrimeConnect's database and is available for all future imports of the same entity type.

What gets saved

A mapping template stores the complete column-to-field mapping configuration, including:

  • Each QuickBooks field and the file column mapped to it
  • The entity type the template applies to
  • Any columns that are intentionally left unmapped
  • All mapping rules on every field, in order
  • The Included state of each field — the checkbox at the left of each row that controls whether the field is sent to QuickBooks

Mapping versions

Every saved mapping carries a version number. The number goes up each time you save changes to that mapping, so two imports that used the same mapping name can still be told apart. Each import records the mapping name and version it was run with, together with a snapshot of the mapping as applied, in its operation history entry.

If you load a saved mapping and then change a field, a rule, or an Included checkbox, the page marks the mapping as unsaved. Save again to create the next version, or leave it unsaved to use the changes for this import only.

Note: Templates are specific to an entity type. A template saved for Invoices will not appear when you are importing Customers, and vice versa. This prevents accidental misapplication of mappings.

Loading Saved Mappings

Loading a saved template applies a complete field mapping configuration in one click, eliminating the need to manually map each column.

How to load a template

  1. In Step 3 (Map Fields) of the import wizard, click the Load button at the top of the mapping screen. The Load Map dialog opens.
  2. A list of saved templates for the current entity type is displayed. Select the template you want to apply.
  3. PrimeConnect applies the saved mappings to your current file. Each column header in your file is matched to the corresponding column name in the template. Mapping rules and the Included checkbox of each field are restored along with the column choices.
Note: Mappings saved before mapping rules were introduced still load exactly as before. They simply have no rules and every field is treated as included; you can add rules and save a new version at any time.

What happens if columns differ

If your new file has columns that were not in the saved template (e.g., a new column was added), those columns will remain unmapped. If the template references columns that are not in your new file (e.g., a column was removed), those mappings are silently skipped.

After loading a template, you can still adjust individual mappings before proceeding. The template gives you a starting point — you have full control to fine-tune as needed.

Tip: For the fastest import workflow, use the same file structure every time and load your saved template. With consistent column headers, the entire mapping step becomes a single click.

Handling Unmapped Columns

It is common for spreadsheet files to contain columns that have no corresponding field in QuickBooks. These might be internal reference numbers, notes, formulas, or data from another system that is not relevant to the QuickBooks import.

What happens to unmapped columns

Columns that are not mapped to any QuickBooks field are completely ignored during the import. They will not cause errors, warnings, or any side effects. PrimeConnect simply skips them when building the QuickBooks request for each row.

This means you do not need to remove extra columns from your spreadsheet before importing. Leave them in place — they will be harmlessly ignored.

Identifying unmapped columns

In the mapping screen, unmapped columns are visually distinguished — they appear with an empty dropdown and no green checkmark. You can review the list at a glance to confirm that only non-essential columns are unmapped.

Caution: Make sure important data columns are not accidentally left unmapped. If a column containing critical data (like amounts or customer names) is unmapped due to an unusual header name, the data will be silently excluded from the import. Always verify that all essential columns have mappings assigned.

Mapping Rules

Mapping rules let you clean up, fill in, or combine values from your file as part of the mapping itself — without editing the spreadsheet first. A rule belongs to a single QuickBooks field, runs on every row, and is saved together with the mapping so it is reused on your next import.

Typical uses: supply a default when a cell is blank, set a fixed value for a field your file does not contain, strip stray spaces, change text case, replace one phrase with another, build a value from two columns, keep only part of a value, or skip rows you do not want imported.

For Excel cells stored as numbers or dates, rules receive consistent text regardless of your Windows region: 1234.5 stays 1234.5, and January 15, 2026 becomes 2026-01-15 without a time. This also applies when adding another column's value. Text cells keep their supplied text, and fields with no rules keep their original values. Continue to choose the appropriate number and date formats for your import.

Opening the Mapping Rules dialog

On the Map Fields page, every QuickBooks field row has a Rules column. The button in that column reads Add rule when the field has no rules yet, and 1 rule, 2 rules, and so on once rules exist. Click the button to open the Mapping Rules dialog for that field.

In the dialog, use Add to create a rule, Remove to delete the selected rule, and Move up / Move down to change its position in the list. The rules you see apply only to that one QuickBooks field — other fields keep their own lists. Click OK to apply your changes, or Cancel to leave the field's rules exactly as they were.

Rule types

Each rule has a kind that decides what it does. The kinds are:

RuleWhat it does
Default if blankUses the value you type whenever the cell in the file is blank. Cells that already have a value are left alone.
ConstantAlways uses the value you type, for every row. This is the only rule that works when the field has no file column mapped — handy for fields like a class, terms, or a fixed account that are the same on every record.
Find and replaceReplaces one piece of text with another. It matches the exact text you type (not a pattern or wildcard). Tick ignore case if upper and lower case should count as the same.
Trim spacesRemoves spaces from the start and end of the value.
Upper caseChanges the whole value to UPPER CASE.
Lower caseChanges the whole value to lower case.
Add text from another columnAppends a separator you choose, followed by the value of another file column. For example, join "First Name" and "Last Name" with a space to build a full customer name.
Take part of the textKeeps only part of the value, using a start position and a length — for example, the first 10 characters of a long reference number. The start position counts from 0 (so 0 is the first character), and a length of -1 means "to the end of the text".
Skip row if…Skips the whole row when this field's value (after any rules above it) meets the condition. Conditions are equals, not equals, is blank, is not blank, and contains; tick ignore case to compare without regard to case. See Skipped rows below.
Note: A Constant rule does not turn an empty spreadsheet row into a record. Rows that are completely blank in your file are still ignored, even if a Constant rule would give one of their fields a value.

Rule order and the live preview

Rules run as an ordered list, top to bottom. Each rule receives the result of the rule above it, starting from the value in the mapped file column (or from nothing, if the field has no file column). Order matters: "Find and replace" followed by "Trim spaces" can give a different result from "Trim spaces" followed by "Find and replace" when the text you search for has a space at the start or end. Select a rule and use Move up or Move down to change the order.

A Skip row if… rule that matches stops the list for that row — rules below it are not evaluated, because the row will not be imported.

The Preview — first 5 rows panel at the bottom of the dialog shows the first five values from the file for that column, side by side: the Source value as it appears in the file and the Result after all rules have run. It updates as you edit the rules. Rows a skip rule would remove show skipped instead of a result. Use the preview to confirm a rule does what you expect before you click OK.

Derived columns on the File Updates page

Rules never change your original file column. Instead, PrimeConnect writes the result into a separate derived column named after the source column and the QuickBooks field it feeds, for example "Customer Name ▸ CustomerRef". The derived column appears on the File Updates page (the editable preview table shown before the import runs) as read-only, next to the original column.

If you edit the original cell on the File Updates page, the derived value is recomputed immediately, so what you see is always what will be sent to QuickBooks. The derived column is what the import uses; the original column is kept for reference only.

Two QuickBooks fields may be mapped to the same file column. Each keeps its own rules and its own derived column, so one source column can feed, for example, both a name field and a memo field with different clean-up applied.

Skipped rows

When a Skip row if… rule matches, the row is left out of the import entirely. On the File Updates page and in the results, the row shows the status "Skipped by mapping rule: …" followed by a short description of the rule that matched.

Skipped rows:

  • are never sent to QuickBooks;
  • do not create missing references — a customer or item that appears only on skipped rows will not be flagged or auto-created;
  • do not count as imported or as failed. PrimeConnect treats them the same way it treats blank rows in your file.
Tip: Skip rules are a quick way to import only part of a file. On the field mapped to your Amount column, add "Skip row if… is blank" to drop subtotal and spacer rows. On a field mapped to a Status column, add "Skip row if… equals Void" to leave out voided transactions. A skip rule only sees the column its own field is mapped to, so the column you want to test must be mapped to some QuickBooks field.

Rules and required fields

A required field normally needs a file column. If your file has no column for it, add a Constant rule to that field instead — the Map Fields check accepts a required field that has a Constant rule even with no file column. No other rule type satisfies this check, because only a Constant guarantees a value on every row.

Required fields are always included in the import; their checkbox cannot be cleared.

Note: If the Map Fields page previously failed to load its fields with an "invalid cast" error on your computer, update to the latest version. This was fixed in the same release that added mapping rules (see Updates & Installation).

Best Practices

Follow these recommendations to get the most out of PrimeConnect's field mapping system:

  1. Name columns to match QuickBooks field names. The closer your column headers are to the actual QuickBooks field names, the more accurate auto-matching will be. Use names like "CustomerRef", "TxnDate", "RefNumber", and "Amount" for the best results. Check the sample files for the exact field names PrimeConnect expects.
  2. Create one template per file format. If you receive data from multiple sources (e.g., an accounting system, a CRM, and a POS), create a separate mapping template for each source. Name them clearly (e.g., "CRM Customer Export", "POS Daily Sales").
  3. Review mappings before every import. Even when using a saved template, take 30 seconds to review the mapping screen. File structures can change over time — a renamed or reordered column could lead to data being imported into the wrong field.
  4. Use the sample files as a reference. PrimeConnect's sample files use the exact column names that auto-matching expects. Use these as a template when creating your own import files.
  5. Do not map columns that contain formulas. If your spreadsheet has calculated columns (e.g., "Total = Qty x Rate"), do not map them. QuickBooks calculates totals from line item quantities and rates — importing a pre-calculated total can cause discrepancies.
  6. Keep headers on the first row. PrimeConnect reads the first row of your file as column headers. If you have title rows, instructions, or other content above your data headers, remove them before importing.

Still Need Help?

Can't find what you're looking for? Our support team is here to help.

Contact Us
Contact Support