Marketplace Data Consolidator By Shirish

Consolidation Complete!

All marketplace data is consolidated into a unified Master Excel workbook. Sheet 1 (Orders_and_Refunds) unifies all OrderPrincipal & Refund entries across ALL marketplaces with Date & NetSuite Order_ID (Marketplace, Sub_Marketplace, File_Name, Date, Order_ID, Order_Type, Amount), followed by dedicated detailed worksheets for each marketplace (Amazon, Allegro, Bol, etc.), Summary breakdown, and Error Log.

Select a marketplace and portal to see which raw headers convert into each Master Standard column.
• To delete a wrong alias: Click the red × on any badge.
• To add a new alias: Type the header name into "Add header name..." and click ➕ Add (or press Enter).
• To add a new Master Column: Click ➕ Add New Output Field above or below the table.
• All changes are saved automatically into your browser storage and will be used immediately for consolidation!

Select Allegro (All 4 Accounts) to drop one main folder containing expondo, expondopl, SmA-MSW, and SmA-R-Catering subfolders.
The engine automatically detects each subfolder, loads dual sheet operacje, Raw, translates Polish transaction types, and generates all 5 sheets!

All 10 European Amazon regional portals (BE, DE, ES, FR, IT, NL, PL, SE, UK and Amazon_All) share a unified mapping. Any edits you make here are automatically synchronized across all Amazon subportals!

All 16 Bol headers are mapped out-of-the-box. Dutch operation types are automatically translated:
• Verkoopprijs artikel(en), ontvangen van kopers en door bol door te storten ➔ Order (Sheet 2: OrderPrincipal)
• Correctie verkoopprijs artikel(en) ➔ Refund (Sheet 3: Refund)
Export generates all 5 sheets (Combined_Data, OrderPrincipal, Refund, Summary, Error_Log) with native numeric formatting.

• Target Sheet: Automatically extracts from Rows (ignores metadata sheets).
• All 22 Cdon columns are preserved in the dedicated Cdon worksheet.
• Invoice ➔ OrderPrincipal | Credit Note ➔ Refund.
• Regional portals automatically routed from Channel: Sweden (Cdon_SE), Denmark (Cdon_DK), Finland (Cdon_FI).
• Generates the Universal Orders_and_Refunds sheet with Date & NetSuite Order_ID with native numeric formatting.

eBay Multi-Regional Engine Active: Automatic multi-language consolidation (DE, IT, UK, FR) preserving all 38 columns cleanly.
• eBay DE: Bestellung ➔ OrderPrincipal | Rückerstattung ➔ Refund.
• eBay IT: Ordine ➔ OrderPrincipal | Rimborso ➔ Refund.
• eBay UK: Order ➔ OrderPrincipal | Refund ➔ Refund.
• eBay FR: Commande ➔ OrderPrincipal | Remboursement ➔ Refund.
• All 38 columns are preserved in the dedicated Ebay worksheet.
• NetSuite Sync: Populates Order_ID in Orders_and_Refunds for seamless ERP reconciliation.

• Target Sheet: Automatically extracts from Marketplace Payout Report.
• All 20 to 22 Galaxus columns (including return deductions) are preserved in the dedicated Galaxus worksheet.
• Summary Footer Rows (Total): Cleanly excluded to ensure exact order reconciliation and zero bogus validation warnings.
• Sales Price (positive) ➔ OrderPrincipal | Sales Price (negative / - Refund) ➔ Refund.
• NetSuite Sync: Order_ID (Galaxus Order ID) mapped directly into Orders_and_Refunds for ERP matching.
• Generates the Universal Orders_and_Refunds sheet with native numeric formatting.

• Strict File Filter: Only files starting with Rawdata_ (case-insensitive) are accepted; non-rawdata summary or invoice files are automatically skipped.
• Auto Text-to-Columns: Semicolon ; CSV & single-column Excel lines are automatically detected and parsed into 53 distinct columns.
• All 53 Kaufland columns are preserved in the dedicated Kaufland worksheet.
• Release order / Freigabe... ➔ OrderPrincipal | Refund / Cancel / Storno / Erstattung... ➔ Refund.
• NetSuite Sync: Order_ID (order_number) mapped directly into Orders_and_Refunds for ERP matching.
• Generates the Universal Orders_and_Refunds sheet with native numeric formatting.

• Auto Text-to-Columns: Semicolon ; CSV & single-column Excel lines are automatically detected and parsed into 43 distinct columns.
• All 43 Leroy Merlin columns are preserved in the dedicated LeroyMerlin worksheet.
• Received / Closed ➔ OrderPrincipal | Refunded ➔ Refund.
• NetSuite Sync: Order_ID (Order number) mapped directly into Orders_and_Refunds for ERP matching.
• Generates the Universal Orders_and_Refunds sheet with native numeric formatting.

• Auto Text-to-Columns: Semicolon ; CSV & single-column Excel lines are automatically detected and parsed into 6 distinct columns.
• All 6 Metro columns are preserved in the dedicated Metro worksheet.
• Payout amount / Auszahlungsbetrag ➔ OrderPrincipal | Customer refund / Adjustment / Kundenrückerstattung / Anpassung ➔ Refund.
• NetSuite Sync: Order_ID (Bestellnummer) mapped directly into Orders_and_Refunds for ERP matching.
• Generates the Universal Orders_and_Refunds sheet with native numeric formatting.

Raw Configuration JSON (Developer Mode)

1. Automated Marketplace & NetSuite Consolidation Engine

All column mappings, headers, multi-language variations (Polish, German, French, Italian, Dutch), and NetSuite Order ID linkages are centrally unified and automated directly in the engine.
- NetSuite Sync: The engine automatically captures each marketplace's native order identifier (e.g. order-id for Amazon, Bestellnummer for Ebay DE / Metro, Numero ordine for Ebay IT, Galaxus Order ID for Galaxus, order_number for Kaufland, Order number for Leroy Merlin, OrderID for CDON, identyfikator for Allegro, Bestelnummer for Bol, Auftragsnummer for OTTO) into the universal Orders_and_Refunds sheet.
- Locked Settings: Settings and column mappings are managed directly in code to guarantee consistency and avoid manual configuration drift.

2. How Multi-Sheet Excel Detection Works

Some Excel files have multiple sheets (e.g. Summary JE, Raw, operacje, Bank Payment). The engine automatically ignores pivot/summary sheets and looks for transaction sheets configured under Target Excel Sheet(s) (such as operacje, Raw). It also scores sheet headers against your mappings to ensure the correct transaction data sheet is always loaded!