How POS Import Works

What is this?

Margin Master prices your items, so it needs your point-of-sale data: every SKU, its cost, its current price, what is on hand and what sold in each of the last twelve months. Getting POS data in is the first of the two data flows a new store sets up (the other is vendor data), and it works one of two ways depending on the point of sale:

Either way you start the import from Data > Get POS Data (Ctrl+I) - the item is named for your system, for example Import Epicor Data - and the import ends by rebuilding the grid.

The POS System Type on Tools > Options > Store Information decides everything else: which Options tab appears for the connection, which file pattern is expected, and which guide in this chapter applies to you. The Supported Point-of-Sale Systems page lists every system and links to its guide.

Where files go

File-based imports read from the Import Path on Tools > Options > Miscellaneous. After a successful import the file is renamed to NAME[ext].BAK so it is not imported twice; backups older than seven days inside Margin Master's own folder are cleaned up automatically. Margin Master never deletes a file it did not create, and never touches a folder outside C:\MarginMaster.

What happens after the data arrives

Whatever the source, the same steps follow: the store's rows are matched to the vendor catalog through the Store Manager, zone pricing is applied, the main table is rebuilt and the selection lists are refreshed. On a large database this takes a few minutes; if the rebuild is postponed to keep the window responsive, the Click to update data badge appears.

The remainder of this page is the detailed route each system's data takes - useful when an import does something unexpected.

POS Systems at a Glance

Top-Level Dispatch Flow

When you click Import POS Data, ImportFilesViewModel scans the configured Import File Path and routes each file (or connection) through the appropriate pipeline:

flowchart TD
    A([User clicks Import POS Data]) --> B[ImportFilesViewModel\nScans ImportFilePath folder]
    B --> C{POS Type}

    C -- "Epicor MySQL Compass" --> D[MySQL Compass\nDirect Connection\nMainMMViewModel.ImportData2]
    C -- "Mi9" --> E[Mi9DataService\nSQL Server Direct]
    C -- "RockSolid" --> F[RockSolidImportExportService\nOLEDB – Access .RS file]
    C -- "NCR Counterpoint" --> G[NCRCounterpointService\nSQL Server / Access]
    C -- "All other file-based POS" --> H[RSImportFileHelperService\nImportAsync]

    H --> I{File extension\nor BisTrack config?}
    I -- ".xlsx / .xls  Catalyst, Falcon" --> J[Excel BCP Handler\nexcelBcpImporter]
    I -- "BisTrack DirectConnect\nconfigured" --> K[BisTrack Direct SQL\nBisTrackSQLService]
    I -- "Spruce / RockSolid MAX" --> L[CSV Special Handler\nSprucewareNetRockSolidMaxImportExportService]
    I -- "Westlake .jda file" --> M[WestlakeJDAFileImportService]
    I -- ".csv / .txt  all others" --> N[Standard BCP Pipeline]

    D --> Z([Post-Import Pipeline])
    E --> Z
    F --> Z
    G --> Z
    J --> Z
    K --> Z
    L --> Z
    M --> Z
    N --> Z

Import Pipelines

Standard BCP Pipeline

Applies to: AS/400, ARS, BisTrack (CSV mode), Burdens, Catalyst (Excel), CounterWorks, Dimension, ECS, Epicor FTP, Falcon, Intact, Nitterhouse, PacSoft, Paladin, Procom, Prosperity, SMSPro, Transact, Versys, and most other file-based POS systems.

flowchart TD
    A([RSImportFileHelperService.ImportAsync]) --> B[1 – Resolve Mapping Type\nLoad .map file at runtime via Roslyn\nor use pre-compiled DataMappings class]
    B --> C[2 – Track Mapping Version\nRecord file date in FileHelpersMappingVersionTable]
    C --> D[3 – Create BCP Staging Table\nDrop + recreate {POS}_bcp from CreateBCPTable.sql]
    D --> E{Pre-process\nrequired?}
    E -- "Catalyst .csv conversion" --> F[PreProcessData\nFormat transformation]
    E -- No --> G
    F --> G[4 – BCP Bulk Load\nExecute sqlcmd with .fmt format file\nFill {POS}_bcp staging table]
    G --> H[5 – BCPFinalize.sql\nDeduplicate, clean nulls\nMove to {POS}_import or {POS}_current table]
    H --> I[6 – Extract Store Numbers\nSELECT DISTINCT Store from staging]
    I --> J[For each store:\nCreate {POS}_{Store}_import\nand {POS}_{Store}_current tables]
    J --> K[7 – BCPToStoreImport.sql\nMerge BCP rows → per-store tables]
    K --> L[Remove duplicate SKUs\nby composite key StoreNumber + SKU]
    L --> M([Post-Import Pipeline])

Key files per POS system (in MarginMaster.DataImport/ImportFileLayouts/{POS}/):

BisTrack — CSV Batch Mode

BisTrack is the most complex file-based import. Stores export one CSV per store, so Margin Master pre-loads all files into a single staging table before processing them individually.

flowchart TD
    A([Multiple BisTrack *.csv files\nin ImportFilePath]) --> B[PreloadBisTrackBcpAsync\nLoad ALL CSV files at once into BisTrack_bcp]
    B --> C[Build file → store mapping\nRecord which file belongs to which store]
    C --> D[Deduplicate across all files once\nCreate SKU+Store index on staging table]
    D --> E[For each CSV file:\nImportAsync called per-file]
    E --> F[Skip BCP load — already staged\nUse store from pre-built map]
    F --> G[BCPToStoreImport.sql\nProcess only that store's rows]
    G --> H([Post-Import Pipeline per store])

Variants — BisTrack has five field layout variants. Margin Master detects the active variant from the store's Options:

BisTrack — Direct SQL Connect

When BisTrack Direct Connect is configured (in Options → BisTrack), Margin Master queries the live BisTrack SQL Server instead of reading files.

flowchart TD
    A([BisTrack Direct Connect\nconfigured in Options]) --> B[BisTrackSQLService\nConnect to BisTrack SQL Server]
    B --> C[BisTrackDirectConnectCreateTable.sql\nCreate local staging table]
    C --> D[BisTrackDirectConnectQueryBisTrackServer.sql\nSELECT inventory data from BisTrack DB]
    D --> E[BisTrackDirectConnectToStoreImport.sql\nMove data into per-store import table]
    E --> F[BisTrackDirectConnectFinalize.sql\nClean up staging]
    F --> G([Post-Import Pipeline])

Mi9 — SQL Server Direct

Mi9 exports data via a direct SQL Server connection to the Mi9 database. There are no intermediate files.

flowchart TD
    A([Mi9DataService]) --> B[Open SQL Server connection\nto Mi9 database]
    B --> C[Query Mi9 inventory tables\nfor the configured stores]
    C --> D[Z.BulkOperations BulkMerge\nUpsert into Mi9PointOfSale_{Store} table\nkeyed on StoreNumber + SKU]
    D --> E[Retry logic for timeouts\nand deadlocks up to 3x]
    E --> F([Post-Import Pipeline])

RockSolid — Access Database

RockSolid stores keep a local Access database (.RS file). Margin Master reads it directly via OLEDB.

flowchart TD
    A([RockSolidImportExportService]) --> B[Open Access .RS file via OLEDB]
    B --> C[Query inventory tables\nfrom Access database]
    C --> D[Extract to SQL Server\nRockSolid_import staging table]
    D --> E[Deduplicate by SKU + Store]
    E --> F[Insert to RockSolid_{Store}_current\nor RockSolid_{Store}_mainTable]
    F --> G([Post-Import Pipeline])

Spruce / RockSolid MAX

These systems produce CSV files but bypass the standard BCP format-file pipeline, using a dedicated service instead.

flowchart TD
    A([SpruceWareNet or RockSolidMAX CSV]) --> B[SprucewareNetRockSolidMaxImportExportService]
    B --> C[Parse CSV with FileHelpers]
    C --> D[Upsert into Spruce_import\nor RockSolidMAX_import staging table]
    D --> E[Per-store extraction and table creation]
    E --> F([Post-Import Pipeline])

Epicor — FTP File Import

Epicor stores that use the FTP import path export a CSV that is retrieved automatically and loaded into the local Epicor table (which has the same schema as the Store table).

flowchart TD
    A([FTP CSV retrieved via\nEpicor FTP connection]) --> B[Standard BCP Pipeline\nRSImportFileHelperService.ImportAsync]
    B --> C[Staged in Epicor_bcp]
    C --> D[Finalized into Epicor table\nsame schema as Store table]
    D --> E[RebuildAceMainTablesFromPosTableAsync\nJOIN Epicor directly with Ace_Current]
    E --> F([Post-Import Pipeline])

The Epicor table is used instead of Store so that Epicor and non-Epicor import paths do not interfere with each other on multi-store databases.

Epicor — MySQL Compass

Stores running Epicor through a MySQL Compass middleware skip the file import entirely and query the Compass database directly.

flowchart TD
    A([MainMMViewModel.ImportData2\nUseMySqlImportManager = true]) --> B[Connect to MySQL Compass database]
    B --> C[For each store:\nGet POS store conversion mapping]
    C --> D[Query Compass_IN table\ninventory data]
    D --> E[Optional: Query INX table\ndiscontinued items]
    E --> F[Optional: Query DW table\ndeleted items]
    F --> G[Upsert into MySqlCompass_{Store}_import\nand _current tables]
    G --> H([Post-Import Pipeline])

Westlake (JDA)

Westlake stores export a proprietary JDA-format file. A dedicated service handles detection and parsing.

flowchart TD
    A([WestLake JDA file\nin ImportFilePath]) --> B[RSImportFileHelperService detects\nWestLake POS type]
    B --> C[WestlakeJDAFileImportService.ImportFileAsync\nSpecialized JDA file parser]
    C --> D[Parse JDA format\ninto Westlake_import staging table]
    D --> E[Per-store extraction and table creation]
    E --> F([Post-Import Pipeline])

Post-Import Pipeline

After any POS import completes and data is in SQL, Margin Master runs a fixed sequence of steps to apply vendor pricing and rebuild the main analysis tables. This pipeline is the same regardless of which POS system imported the data.

flowchart TD
    A([POS data in SQL\nStore or POS-specific table]) --> B[1 – UpdateMainTableZonePricing\nApply Ace zone-specific pricing from AceZoneRecords]
    B --> C[2 – UpdateDIBMSURetail\nApply DIB MSU retail pricing updates]
    C --> D[3 – RebuildMainTables\nJOIN Store + Ace_Current → Temp main tables\nAce_InsertIntoMainTableFromStoreAndAceCurrent.sql]
    D --> E[4 – BuildMainTableFromTempMainTables\nFinalize main table from temp tables]
    E --> F[5 – CreateTopPanel\nBuild selection box data\nProductGroup2, VendorNumber, VendorName]
    F --> G[6 – RebuildAllManagedIndexes\nApply RSIX_ non-clustered indexes\nregistered in ManagedIndexes table]
    G --> H([Main table ready\nPricing analysis available])

Steps 1 and 2 only run when the respective vendor data is present. Steps 3–6 always run when any data changed.

Tips

If an import seems to complete but the main table is not updated, check that the post-import pipeline ran by looking at the status bar messages. Each step logs its name and row count.

BisTrack imports with many stores are significantly faster with the batch pre-load (all files staged in one pass). If one file fails, the rest of the batch continues — failed stores are listed in the import summary.

For RockSolid, the .RS file must not be open in another application (locked by RockSolid) when Margin Master imports it. Schedule imports during off-hours or after closing.

Epicor MySQL Compass imports do not require any file export from the POS. The connection runs on-demand from the Import dialog.

Common Questions

Why does importing one POS file update all stores? Some POS systems (like Mi9 and MySQL Compass) export all stores in a single connection or file. Margin Master automatically splits the data by store number during import.

What happens if a file has duplicate SKUs? Each pipeline includes a deduplication step. The last occurrence of a duplicate SKU + Store combination wins. For BisTrack batch mode, dedup runs once across all files in a single pass.

Can I re-import the same file twice? Yes — the staging table is truncated and rebuilt on each import. Re-importing overwrites the previous data for the affected stores.

Why is there a _bcp, _import, and _current table?

• _bcp: Raw BCP load from the flat file (temporary, dropped after finalize)
• _import: Cleaned and deduplicated staging for this import run
• _current: Persistent store data — the source for main table rebuilds