Clean restaurant export data without losing the order trail

Clean the working data without losing the evidence

Cleaning restaurant data means making its structure and meaning reliable enough for a particular question. It does not mean deleting every awkward row or changing headings until a tool accepts the file. A useful cleaning process preserves the original, records the transformation and explains any change in counts or totals. Another authorised person should be able to reproduce the result and understand which values remain uncertain.

This guide uses a small original fictional dataset. Its column names are teaching labels, not a replica of a merchant portal export. All identifiers, quantities and amounts are invented. The examples demonstrate text import, record grain, duplicates, dates, missing values and joins. They do not verify your account, repair an incomplete upstream response or extend the settlement analyser to unsupported overseas platforms or arbitrary spreadsheet files.

Begin with the question you want to answer. A menu-demand question needs reliable item quantities and a complete relevant population. A payout question needs recognised settlement records and cash evidence. A current menu snapshot cannot substitute for a history of items sold. The first useful cleaning decision is often choosing the right dataset rather than applying more transformations to the wrong one.

Preserve the source copy before opening it for editing

Keep the original download in authorised private storage and create a separate working copy. Record the source, outlet scope, selected dates, generation time, module and export version where available. Do not overwrite the original after experimenting with spreadsheet formats. If a transformation damages an identifier or changes a date interpretation, the original is the dependable place to start again.

Give the working copy a clear purpose. It might be an item-sales analysis, a settlement review or a set of anonymised teaching rows. Each needs a different selection of fields. Keep financial records, customer details and banking references out of public examples. Reducing unnecessary personal information in an analysis copy is different from altering the original business evidence. Preserve the original under the business's actual retention and access requirements.

Keep a version note when the source is regenerated. A later export can contain corrected orders or more complete detail. It is not automatically a duplicate of the earlier file. Record which version is approved for the analysis and how the difference was checked. Avoid combining several overlapping snapshots without an identity and version rule, because that can create extra copies of the same activity.

Use trusted synthetic files while learning the import controls. A real export can contain free text and private fields that are unnecessary for practice. Do not enable macros, external links or remote content to understand a CSV. If a file behaves unexpectedly, inspect it with an appropriate trusted tool and preserve the source rather than accepting an unexplained conversion prompt.

Read the export index and module before the table

An archive can contain several kinds of output. Identify which file is an item table, which describes order-level money, which contains payout cycles and which provides diagnostics. A generated file does not prove that every upstream record was recovered. Read job warnings, failed-module information and counts before using a total as complete. Completion of an attempt is different from completeness of the underlying business population.

Our current export-format reference documents the supported product fields. Zomato recovered item lines and Swiggy order-level finance have different meanings. Swiggy finance amounts do not establish dish, variant or add-on quantities. A menu-category snapshot is also not a record of products sold. Cleaning cannot create missing detail that the selected response never contained.

Check whether an apparently empty file contains a marker or diagnostic table. A row saying that no records were returned is not a sale and does not prove zero business activity. A diagnostic structure using source, path and value may be useful for investigation while remaining unsuitable as a normalised payout dataset. Retain it for the relevant purpose rather than renaming its columns into a fictional settlement format.

Record what the source can and cannot support. If detail recovery failed for some orders, an item analysis can understate the selected period. If settlement summaries are available, a separate payout question may still be answerable. Keep those conclusions separate instead of declaring the entire export either perfect or useless. The limitation should follow the affected dataset and decision.

Write what one row means

Before removing duplicates or adding amounts, write a sentence describing one row. It might represent one item line within an order, one order-finance record, one payout cycle or one adjustment. This is the dataset's grain. The grain determines which identifiers can repeat legitimately and which totals can be summed. A spreadsheet row is a storage location, not automatically an order or a customer.

Inspect a known example with several components. One order can contain two different dishes and a later partial refund. Its order identifier might appear in several rows without any duplicate error. An order-level payout total repeated beside each dish should not be summed once per dish. Find the actual relationships before choosing an aggregation or identity rule.

Keep platform and outlet scope attached to records. The same-looking order number in two systems may not identify the same transaction. An outlet rename can also create confusing labels. Use a documented mapping and retain source identifiers. Do not replace every identifier with a shortened visible label when that removes the information needed to distinguish records.

Use the report dictionary for terminology, then apply the actual provider's definitions. A generic term can help explain the question but cannot override a source's field meaning. Where the meaning remains uncertain, mark it as unconfirmed and obtain the relevant documentation or authorised support explanation before using it in a financial conclusion.

Choose the delimiter and encoding from the actual file

A delimiter separates fields. A CSV often uses commas, but a particular file can use another separator. Choose the import setting that produces the expected columns and inspect a known row. If everything appears in one cell, the delimiter may be wrong. If words split across unexpected columns, quoted text or another separator may have been interpreted incorrectly. Do not repair the appearance by manually shifting cells without understanding the structure.

Encoding controls how text characters are represented. Wrong encoding can distort dish names, currency symbols or other text. Keep the original bytes and use the appropriate file-origin or encoding setting in the import tool. A damaged display does not prove the underlying value was wrong. Reimport from the preserved source when necessary rather than typing guessed replacement characters into business records.

Inspect quoted fields. A product name containing a comma can remain one field when correctly quoted. A quoted free-text field can also contain a line break. The common format documented in RFC 4180 allows these structures, so counting physical text lines is not always equivalent to counting records. Use a proper CSV reader rather than blindly splitting on every comma or line break.

Preserve headers and raw field content during initial inspection. A blank header, repeated header inside a combined file or unexpected extra column needs investigation. Record whether it comes from a genuine schema change, a summary row or a malformed export. The goal is a dependable interpretation, not making every file look like the first example you learned.

Protect identifiers as text before conversion

An identifier is a label, even when it contains digits. Store outlet, order and reference codes as text where appropriate. The fictional outlet label 001 must remain different from an automatically converted number displayed as 1 if the original code matters. Applying a prettier display format later does not necessarily restore the original underlying identifier or prove which code the source supplied.

Microsoft documents Excel's fifteen-significant-digit numeric precision. A long digit-only identifier imported as a number can lose its original trailing digits. Formatting the damaged value as text afterwards does not reconstruct them. Return to the original source and import the column as text before conversion. Do not guess missing digits from adjacent orders or invent a padded identifier to make a join succeed.

In Excel's supported import workflow, inspect the text/CSV preview and set identifier columns to text using the available import or Power Query controls. Exact menus vary by version and host. Microsoft also documents automatic conversion settings for supported versions. Confirm the result with a known identifier rather than assuming a setting took effect. A harmless synthetic file is a good place to practise.

Keep identifiers separate from display labels. A restaurant name can be helpful for reading, while the source identifier supports reliable matching. If an analysis copy needs redaction, use a consistent local alias and retain the authorised mapping privately. Randomly replacing an identifier in every row can destroy the relationships needed to count orders or match settlement references.

Interpret dates explicitly instead of relying on appearance

The text 03/04/2026 is ambiguous without a known convention. Under a day-first interpretation it means 3 April; under a month-first interpretation it means 4 March. A spreadsheet can convert either into a plausible date without displaying an error. Preserve the raw text, determine the source convention and create a separate interpreted date. Do not choose the result simply because it fits the month you expected.

Power Query's locale affects conversion of text into dates and numbers. Select a suitable locale or explicit conversion method and check a known record. A date such as a day beyond twelve can help expose a mistaken month-first assumption, but that check alone does not establish every date field's meaning. Read the provider's date definition as well as its formatting convention.

Distinguish event dates. Order creation, order completion, customer payment, payout initiation and bank posting are different questions. A refund can retain an original order date while being processed later. Do not group every financial effect by whichever date column appears first. Write the event used by the analysis and retain other dates when they help explain timing differences.

Keep time-zone context and timestamp units. A numeric timestamp may use seconds, milliseconds or another representation. Compare it with a known source record and the relevant documentation before converting. An overnight restaurant service can also cross a calendar boundary. Preserve the original value and conversion rule so a reviewer can investigate an apparent missing day without guessing how the date was produced.

Define money units, currency and signs before summing

Record the currency using an unambiguous code. A dollar symbol alone can refer to several currencies, and changing the label does not convert amounts. Determine whether the source stores major currency units, smaller units or formatted text. Test a known transaction against the official record. A factor error can make an entire report wrong while preserving convincing-looking ratios between its numbers.

Choose a sign convention for the working model. You can preserve signed adjustments or store positive deductions in named columns, provided you document the conversion. Do not mix the conventions. If a fee is already negative, adding it reduces the total; subtracting it increases the total. Retain the raw signed value and a separate normalised amount when a conversion is needed.

Check whether the starting amount already includes reductions. A sales figure net of discounts and refunds should not have those same components subtracted again. Tax, tips, delivery charges and funded promotions can have report-specific treatment. Read the source definition before deciding that two fields differ because of a particular fee. A difference alone does not identify its cause.

Handle rounding at a disclosed stage. Retain available calculation precision and display appropriate decimal places at the end. A small difference might reflect rounding, but do not use that explanation for every unmatched amount. Compare the provider's documented base and method when checking a real charge. A generic cleanup rule cannot establish whether an actual invoice is correct.

Keep blank, zero and error distinct

A genuine zero means the selected field has a known value of zero. A blank can mean unavailable, not applicable or missing. A failed conversion can mean that the import did not understand the source representation. Preserve those distinctions. Turning all three into zero makes a worksheet easier to calculate while hiding the information needed to judge whether the answer is reliable.

Create a reason column for unusable values. A missing quantity, unsupported currency marker and malformed date require different follow-up. Keep the original text and source reference in a private exception table. Decide which analysis is affected. A missing dish quantity might prevent a complete item-demand total without preventing a separate settlement review based on supported payout records.

Do not fill a missing value from another amount unless the relationship is established. Customer-paid money cannot reliably reveal how many dishes were ordered. A payout amount cannot reveal a menu variant. A similarly named product is not necessarily the same size or recipe. An inferred value should be explicitly labelled with its method and uncertainty rather than presented as observed source data.

When an actual estimate is necessary for a management decision, keep it separate from verified values. Record the source, purpose and sensitivity to the assumption. Obtain appropriate advice where an accounting or tax treatment matters. A public tutorial should help readers recognise uncertainty, not encourage them to invent business records that appear more complete than the evidence.

Normalise labels with an explicit mapping

A normalised label gives compatible records a consistent analysis category. Keep a mapping table from the raw label to the chosen category and record why the mapping is valid. Trimming accidental surrounding whitespace can be useful in a working field, but do not silently alter raw identifiers or free text that could have meaning. Preserve the original and the transformation rule.

Do not merge products just because their names are similar. A small meal, large meal and meal with a paid addition can have different prices and costs. Use catalogue, variant and portion information when available. If the source does not distinguish them reliably, state that limit. A tidy product chart is not worth losing the distinctions needed for recipe or profitability analysis.

Keep historical labels when the business renames a product or location. A current menu snapshot does not establish every historical price or product definition. Use dated transaction evidence for historical sales and retain legitimate snapshots for menu-change analysis. Record effective dates in the mapping so a later reviewer knows whether a trend reflects changed demand or a changed grouping rule.

Review category changes before applying them to older periods. A new grouping can change the apparent share of sales without any underlying activity changing. If historical data are restated, explain the rule and preserve the prior result. Do not compare one month under the old mapping and another under the new mapping as if they were directly equivalent.

Meet the fictional dirty file

Our teaching file has five rows. Its columns are platform, outlet, order, event, line, kind, quantity and amount. The platform label is simply Teaching and the outlet label is the text 001. These are authored worksheet fields. They are not actual portal headers and should not be renamed into an import format to make the settlement analyser accept them.

The first row is order E1, event S1, line 01: a sale of two portions with a line amount of 300. The second is the same order and event, line 02: one portion with a line amount of 120. The third is order E2, event S2, line 01: one portion with amount 250. The fourth is a confirmed repeated export copy of that third row. The fifth is order E2, event R2, line 01: a refund amount of negative 50, with no quantity supplied.

The raw amount total is 870: 300 plus 120 plus 250 plus another 250 minus 50. We know the fourth row is a repeated copy because the fictional source history explicitly says so. In real data, matching values alone would not establish that fact. Keep the provenance that supports removal and retain the separate refund event.

The example is deliberately small enough to check manually. It contains repeated order identifiers that are legitimate, a repeated copy that should be removed and a refund with unknown quantity. It tests whether the cleaning method respects meaning instead of applying one broad repeated-ID rule. No private merchant data is needed to learn those distinctions.

Remove only the confirmed repeated copy

Use the defined combination of platform, outlet, order, event and line to inspect possible copies in this fixture. The two E2 sale rows match on that key and are confirmed copies by the stated history. Retain one and record the other in the removed-copy log. The accepted amount total becomes 620. The change from 870 is exactly the removed 250, so the amount-control bridge explains the transformation.

Keep both E1 item lines. Their shared order and event identifiers do not make them duplicates because their line identities differ. Keep the E2 refund as well. Its event identifier differs from the sale, and its financial effect is legitimate under the example. Deleting every repeated order identifier would lose both real item detail and the refund, producing an unreliable result.

The accepted file contains three sale item lines, four sold portions and two distinct sale orders, plus one refund event. The refund quantity remains unknown because the source did not supply it. Do not subtract one portion simply because the refund refers to E2, and do not count the refund as another sold order. Each count should answer a clearly named question.

A real duplicate rule may need additional fields or version handling. Some sources update records while retaining an identifier, and some lack a stable event or line key. Document the limitation and review candidate copies before removal. A useful rule explains what proves identity and which version is retained; it does not promise that every source shares this fixture's structure.

Handle overlapping export windows and revised records

Two downloads covering overlapping dates can contain repeated copies of the same activity. Record each file's coverage and generation time before combining them. Determine whether the later file merely repeats records or updates their status or amounts. A changed record is not necessarily an additional event. Conversely, a separate refund event should not be discarded as a newer version of the original sale.

Choose a version rule supported by the source. You might retain a confirmed complete later snapshot for a defined population while preserving the earlier file as evidence. That is different from choosing the largest amount or the latest-looking row without explanation. Keep the source filename and row reference so the reviewer can trace which version supplied the accepted value.

Compare control totals before and after the decision. Record copies removed, revisions accepted, new events added and unresolved conflicts. The total can change for several reasons, so a single deleted-row count is not a complete explanation. A revision log should distinguish a corrected value from a new sale. Keep unresolved versions quarantined until their meaning is established.

Do not combine months by copying every visible table into one sheet without reading headers and summary rows. A repeated header is not a business record, and a month total pasted beneath its detail can duplicate the amount. Keep each file's metadata and only append compatible data populations. If schemas differ, document the mapping before combining them.

Avoid multiplying money when joining tables

A join connects tables using matching values. Before joining, state the intended relationship: one order to several items, one payout to several adjustments or another supported structure. If both tables have several rows for the same key, expanding the matches can create several combinations. That can repeat money even though the software performed the requested operation correctly.

For a small invented example, one order has two item lines and two adjustment lines. Joining both tables on order alone creates four combinations. Summing item amounts after expansion can count each item more than once; summing adjustments can repeat them too. The correct approach depends on the question. You might aggregate both sides to one row per order first, or preserve separate related tables without adding repeated parent totals.

Use compatible key types and the correct column pairing. A text identifier with leading zeros is not safely equivalent to a damaged numeric conversion. A key may also need platform, outlet, event or date scope. Microsoft documents matching one or multiple columns in Power Query; the analyst still has to choose a relationship appropriate to the data. Matching column headings alone does not establish that relationship.

Check the expanded row count, unmatched keys and amount controls. Keep unmatched records for investigation rather than deleting them to produce a perfect match rate. If totals change unexpectedly after a merge, stop and inspect the relationship. Do not patch the final number manually, because the same structural error can affect every later calculation based on the joined table.

Write before-and-after controls for every material step

Choose controls appropriate to the grain. For the fictional dirty file, record raw rows, accepted rows, sale item lines, distinct sale orders, quantities and signed amounts. These measures answer different questions. A row count alone cannot prove the file is correct. A money total alone can conceal missing and duplicated amounts that happen to cancel each other.

Record the expected effect before applying the step. Removing the confirmed repeated 250 row should reduce the amount total by 250 and remove one row while preserving two distinct sale orders. If a different effect appears, inspect the rule. A transformation that does not behave as expected is an investigation, not a successful cleanup merely because the file became smaller.

Keep checks at source, accepted data and final summary stages. A later filter can exclude a status or channel unintentionally even when earlier cleaning was correct. Preserve the filter rule and selected population with the output. If the final chart represents completed orders only, label it that way. Do not present it as all activity unless the source and selection support that conclusion.

Review controls after refreshing the data. A new column, label or record type can change behaviour. A process that worked for the previous export should not silently assume the next one has identical structure. Log the refresh date and any schema differences. Keep a small known-record test so a change in import settings or source formatting becomes visible.

Quarantine unresolved records without hiding their effect

A quarantine table holds records whose interpretation is not yet reliable. Keep the source reference, raw value, reason and next investigation step. The term does not imply the record is fraudulent or useless. It means the selected analysis cannot yet use it confidently. An invalid date, ambiguous currency and unknown event type need different questions and should not be grouped under one vague error label.

State how quarantined records limit the result. If a meaningful portion of item detail is unavailable, do not call the menu ranking complete. If a payout amount is unresolved, show the affected clearing balance or uncertainty rather than declaring a precise shortfall from guessed values. The scope note should help the reader understand which decision remains supported.

Assign an owner and review date. A quarantine file that nobody revisits becomes permanent hidden incompleteness. Use actual provider documentation, original records or authorised support to resolve the issue. When the interpretation is established, record the evidence and move the record through the appropriate transformation. Preserve the history so the reviewer knows why the result changed.

Do not fill gaps by copying a nearby row or assigning a standard fee without evidence. A clean-looking table can be less reliable than a visibly incomplete one if it contains invented business values. Keep estimates clearly labelled and separate when they are appropriate for a particular planning question. They should never be presented as recovered source transactions.

Protect free text and private fields during practice and sharing

Free text can include customer names, instructions or other information unnecessary for a financial review. Remove unneeded personal fields from the working analysis copy, while retaining original records under authorised business controls. Public screenshots should use synthetic or carefully redacted examples. A visible table can contain hidden columns, metadata or references that deserve inspection before sharing.

Spreadsheet programs can interpret formula-like text when opening CSV files. OWASP explains why ordinary quoting is not a universal protection across programs and save cycles. Use trusted synthetic material for tutorials, import untrusted free-text columns appropriately as text and do not enable macros or external content to inspect a file. A claim that a CSV writer applies guards does not make arbitrary files from elsewhere trustworthy.

Keep sanitisation and business-data interpretation separate. A display-safe copy can alter characters that matter to downstream matching. Document any such change and retain raw values privately. Do not strip meaningful negative signs from numeric amounts merely because formula-like free text requires care. Treat typed money and untrusted text according to their actual roles.

Use privacy-safe support questions. State the module, selected dates, expected record type and observed issue, with a harmless sample when possible. Never send passwords, OTPs, cookies, tokens or unrelated customer data. For a legitimate account issue requiring private evidence, use the provider's authenticated channel and share only the material needed for that investigation.

Respect the supported settlement import formats

The current settlement analyser supports its recognised Zomato and Swiggy settlement JSON, matching CSV structures and supported ZIP contents. It is not a general invoice, PDF, Excel or foreign-platform importer. Check the format documentation and the current tool instructions before selecting a file. A financial-looking table is not automatically a supported settlement source.

Do not rename unrelated headers to imitate a recognised format. That can cause a parser to interpret the wrong amounts under plausible labels. Preserve native headers and use a manual worksheet for unsupported data. The teaching labels in this guide deliberately describe an original fixture; they do not instruct readers to turn arbitrary files into fake exporter output.

Keep locations separate where a tool's consolidation behaviour is not established. A batch-selection feature does not by itself prove reliable outlet-level deduplication or cross-currency comparison. For a multi-outlet review, retain explicit outlet keys and verify the complete workflow or use separate supported runs and a clearly mapped manual comparison. The outlet guide explains the relevant scope.

Current tools process their selected supported data according to their documented browser workflow. They do not gain access to merchant accounts or bank systems because a file was cleaned. A local calculation can organise the inputs you provide; it cannot verify source completeness, determine tax treatment or certify that a provider charged correctly.

Document the transformation log for a repeatable handover

For each step, record the purpose, input source, rule, output and control result. Use plain descriptions such as preserve order IDs as text, interpret the specified date using the confirmed convention or remove the confirmed overlapping copy. Keep the log readable to somebody who did not build the process. An unexplained sequence of spreadsheet clicks is difficult to review or reproduce.

Keep raw and normalised values when the interpretation could matter later. Record the source label and target category rather than overwriting the original. Save the outlet mapping, event identity rule and exclusions with the result. When a provider changes its structure, the reviewer can then identify which assumptions require revision instead of trusting an old cleanup routine silently.

Use versioned outputs and record the refresh date. A later correction should create an explainable new result. If the accepted total changes, identify whether the cause was a new source record, a revised source amount, a different mapping or a repaired transformation. These are different types of change, and the next user should not have to guess which occurred.

Finish the handover with the supported conclusion and remaining limitations. For the fictional fixture, the accepted signed amount is 620, with two sale orders, three sale item lines, four sold portions and a separate refund event with unknown quantity. That is a useful precise description. It does not establish the final platform payout or complete restaurant profitability.

Keep related fields together during ordinary spreadsheet work

A simple manual edit can damage a sound import. Sorting only an amount column while leaving order identifiers in place separates values from their records. Keep the full related table together for sorting or filtering, and preserve a source-row reference that lets you trace the original. After any substantial manual rearrangement, check a known record across several fields rather than inspecting only the total.

Distinguish hiding from deleting. A filter can make records invisible while leaving them in a formula's population, depending on the formula and application behaviour. Record whether the final summary uses all accepted rows or a selected subset. Do not assume a visible subtotal and a whole-column total answer the same question. Use a known small example to establish how the chosen calculation responds to filters before relying on it for real records.

Keep notes and calculated fields outside the raw source structure. If a working table needs an analyst comment or assigned category, add it deliberately and document the meaning. Avoid inserting unlabeled totals or explanatory sentences among transactional rows where a later append or refresh could treat them as data. A reader should be able to distinguish business records, source metadata and analyst interpretation without guessing from formatting alone.

Choose an output that matches the accepted evidence

After cleaning, decide which result the accepted population supports. The fictional file can describe its item-line amounts and event structure, but it cannot establish a final bank transfer. A complete settlement file may support a payout bridge while lacking recipes, payroll or rent needed for restaurant profitability. Keep the output narrow enough that the evidence can actually support its label.

A product chart should use compatible products, quantities and periods. An exception chart should describe the population of unresolved records and not imply that every exception is a monetary loss. A cash chart should use actual cash evidence or clearly labelled forecast assumptions. Give the reader the denominator, unit and exclusions needed to interpret the result. Colour and layout cannot repair a misleading measure.

Save the accepted-data version used for a shared result. If the underlying file later changes, refresh deliberately and explain any movement. Keep the previous result where the business's process requires it, together with the reason for the revision. This turns a one-time cleaning exercise into a reliable reporting routine that another authorised person can repeat.

Practise the checks before making a chart

Explain why the raw total of 870 becomes 620. Only the confirmed repeated 250 copy is removed. The negative 50 refund remains, and the two E1 item lines remain. Then explain why counting accepted records does not give the sale-order count. The accepted population contains several kinds of records, and the distinct sale orders are E1 and E2.

Explain why the refund quantity stays unknown. The source provides a refund amount, not a returned portion count. The amount cannot reliably identify the dish or quantity without supporting detail. A blank quantity is an honest limit in the fixture. Changing it to one would create a fact the source did not supply.

Finally, inspect a sample date, identifier and quoted product name. State the raw representation and chosen interpretation. Predict the expected row and amount controls before applying a transformation. If the result differs, investigate rather than continuing to the chart. A clear chart is useful only when the underlying population and units remain trustworthy.

Sources and current scope

Reviewed 17 October 2026. Microsoft's identifier guidance, Power Query type and locale documentation, Text/CSV connector guidance and merge documentation support the import distinctions. Exact controls vary by application and version; no live spreadsheet or merchant account was tested for this article.

RFC 4180 describes common CSV quoting conventions and is an informational document, not a guarantee of every dialect. OWASP explains spreadsheet formula interpretation risks. Our fixture, control totals and teaching methods are original. Use the market examples for source boundaries and the month-end guide to connect accepted data with financial evidence. Keep real exports private and preserve the original record trail.