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 Header | Matched 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).
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
- Find the column in the mapping list that needs to be mapped (or re-mapped).
- Click the dropdown next to the column name. This opens the list of available QuickBooks fields for the selected entity type.
- 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).
- 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 Type | Required Fields |
|---|---|
| Customers | Name |
| Vendors | Name |
| Invoices | CustomerRef, at least one line item (ItemRef + Amount or Quantity + Rate) |
| Bills | VendorRef, at least one line item |
| Journal Entries | AccountRef, Amount, debit/credit designation |
| Items | Name, ItemType |
| Accounts | Name, 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
- Configure all your field mappings in Step 3 of the import wizard.
- Click the Save button at the top of the mapping screen. The Save Map As dialog opens.
- Enter a descriptive name for the template (e.g., "Monthly Invoices from Accounting", "Vendor Import from ERP", "Customer List Update").
- 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.
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
- 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.
- A list of saved templates for the current entity type is displayed. Select the template you want to apply.
- 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.
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.
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.
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:
| Rule | What it does |
|---|---|
| Default if blank | Uses the value you type whenever the cell in the file is blank. Cells that already have a value are left alone. |
| Constant | Always 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 replace | Replaces 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 spaces | Removes spaces from the start and end of the value. |
| Upper case | Changes the whole value to UPPER CASE. |
| Lower case | Changes the whole value to lower case. |
| Add text from another column | Appends 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 text | Keeps 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. |
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.
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.
Best Practices
Follow these recommendations to get the most out of PrimeConnect's field mapping system:
- 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.
- 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").
- 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.
- 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.
- 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.
- 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.
