prepared by the NADALI editorial team using AI
A shop exports orders as CSV, opens the file to tidy it, then asks AI to compare branches. The total looks reasonable, but a product identifier has lost its leading zero and an empty amount has become zero. The analysis now describes different data from the original export.
This guide is for small-business owners, operations staff and analysts preparing exports for AI. Check the transfer before asking for conclusions: keep the original, declare column meanings, import deliberately and reconcile the exact file being handed over. The workflow and six-record example below are editorial proposals, not results of testing Excel, Calc or an AI product.
1. Keep an untouched export and a short file specification
Save the original separately from working copies. Record the source system, export time, selected period, filters and whether one record represents an order, an order line or another event. Obtain the expected record count and relevant totals from the same source and scope. If that baseline is unavailable, mark completeness as unverified; a count computed only after import cannot establish that the export was complete.
Write a short specification alongside the file:
- Character encoding, field separator and quote character.
- Exact headers and expected number of columns.
- Text identifiers, date order and numeric decimal separator.
- Units or currency for each measure, plus permitted missing-value markers.
- The record key and whether repeats are legitimate.
For a first AI request, the specification and invented examples are often enough. Remove unnecessary personal or confidential columns from the approved working copy, not from the retained original. Use only an approved processing destination.
2. Treat identifiers as text before conversion
Order references, product codes and postal codes describe identity, not quantity. Keep their characters unchanged, including leading zeros. Do not decide the type from appearance: an identifier may contain only digits, look like a date or contain E.
Microsoft documents Excel’s 15-significant-digit numeric precision and recommends text for long numeric codes. Applying text formatting after entry does not repair digits already changed. Import the identifier column as text from the untouched source, then compare the complete values rather than their abbreviated display. Microsoft: leading zeros and large numbers.
Do not reconstruct a lost zero by guesswork. A five-character display mask is not evidence that every source identifier had five characters. If corruption occurred, restart from the original and fix the import rule.
3. Select an import route that exposes the rules
In Excel, use Data → From Text/CSV and inspect the separator preview; choose Transform Data before loading when types need attention. Opening a CSV directly uses current default format settings. Exporting a worksheet to CSV also does not export every sheet in the workbook. Microsoft: text import and export.
In Power Query, inspect the automatically added Changed Type step. Remove or replace an incorrect conversion before setting identifiers to Text; adding Text after a destructive numeric step cannot restore the source. Type detection for unstructured sources examines the first 200 rows. For dates and amounts, use an explicitly appropriate type and, where necessary, Change Type → Using Locale. Microsoft: Power Query data types.
Excel’s Automatic Data Conversions controls are documented for Microsoft 365 and Excel 2024. They can disable selected conversions, but do not directly control Power Query imports. Treat them as an additional safeguard, not your file specification. Microsoft: automatic conversions.
In LibreOffice Calc, open the CSV with Edit filter settings selected to inspect the import dialog instead of silently reusing the previous import settings. LibreOffice: opening CSV files. Select the character set, separator and language; assign identifier columns the Text type. Leave Merge delimiters off when adjacent separators represent empty fields. When importing a data-only export, leave Evaluate formulas off so text starting with an equals sign is not evaluated. LibreOffice: Text Import.
Menu labels and available routes vary by version. No new subscription or paid AI feature is required by this workflow; use the approved tools you already have.
4. Resolve dates, decimal marks and encoding explicitly
Ask the source owner whether 03/04/2026 means 3 April or 4 March. Neither is an error a parser must reject. Preserve the original field, then create a separate normalised date only after establishing the rule. Check the resulting day, month and year against source records. If timestamps matter, also record their time zone and do not silently discard the time.
A field separator and a decimal separator serve different purposes. A semicolon-separated file can legitimately contain decimal commas. Import 1250,40 as the intended amount, not two fields or the integer 125040. Do not globally replace commas: that can alter quoted descriptions and other columns. Excel’s legacy Text Import Wizard provides separate controls for character set, delimiters, date order and decimal/thousands separators. Microsoft: Text Import Wizard.
Check recognisable text such as Київ, Львів and a product description after decoding. Garbled text or replacement characters mean stop and verify the source encoding. Save a converted copy only after the characters match. Calling a file “UTF-8” in its name does not establish its encoding. Changing encoding alone is not proof that the original characters have been recovered; re-import from an intact source and compare.
5. Reconcile a small illustrative export
All records and amounts here are fictional. The declared format is UTF-8, semicolon-separated, double-quote text qualifier, day/month/year dates and decimal commas without thousands separators. ID is text. All Amount values use the same hypothetical currency. Blank Amount means unknown, while 0,00 is a known zero. The header is not a data record.
ID;Date;Branch;Amount;Amount_status;Note
00123;01/10/2026;Kyiv;1250,40;known;"Пакунок; великий"
00124;02/10/2026;Lviv;79,60;known;Львів
012345678901234567;03/10/2026;Kyiv;0,00;known;Київ
JAN1;04/10/2026;Lviv;;missing;Очікується
5E2;05/10/2026;Lviv;100,00;known;Звичайний
00128;06/10/2026;Kyiv;-30,00;known;КоригуванняAfter import, expect six records, six columns and six distinct IDs. JAN1 and 5E2 must remain literal identifiers. The quoted semicolon in the first Note belongs inside one field. The long ID must retain all 18 characters, and the dates mean 1–6 October 2026.
Five amounts are known: three positive, one zero and one negative. In decimal-point notation, their sum is 1250.40 + 79.60 + 0.00 + 100.00 − 30.00 = 1400.00. Kyiv has three records totalling 1220.40. Lviv has three records, two known amounts totalling 179.60 and one unknown amount. These figures were checked with decimal arithmetic; the example was not run through the named spreadsheet interfaces.
1400.00 is the sum of known amounts, not a complete total for all six records. Replacing the blank with zero leaves that sum unchanged but falsely increases the known-value count to six. Dropping the zero row also leaves the sum unchanged but reduces the record count to five. This is why totals, counts and exact keys need separate checks.
For real exports, compare counts and sums by a useful group as well as overall. Match identifiers to the source and investigate repeats before deleting anything. Equal totals can hide missing and duplicated records or values assigned to the wrong branch. Where full comparison is impractical, document the sample and its limits.
6. Check the saved file and define the AI handoff
Re-import the final saved CSV using the declared rules and repeat the checks. A correct workbook is not proof of a correct exported file. Count parsed records, not physical text lines: quoted fields can contain line breaks. Python’s CSV documentation explicitly distinguishes these counts and describes string-based reading without default numeric conversion. Python: CSV reading and writing.
Send the approved file with its specification and an instruction such as: “Preserve ID as text. Blank Amount is unknown, not zero. Before analysis, report record and column counts, missing and zero amounts, duplicate keys and known-amount totals by Branch. Flag mismatches; do not repair or infer values.”
Compare those outputs with the independently recorded controls. A model saying “all data loaded” is insufficient. If the application exposes no inspectable parsed data, record that limitation rather than claiming end-to-end fidelity.
Checklist before sharing the file
- Original export retained; period, filters and record definition recorded.
- Headers, delimiter, quotes and encoding match the specification.
- Complete identifiers survive unchanged; dates and decimals have verified meanings.
- Missing, zero and invalid values remain distinguishable.
- Record counts, keys and grouped totals reconcile with the source.
- Saved file rechecked; unresolved issues, reviewer and version recorded.
FAQ and stopping rule
Do quotation marks guarantee that an ID stays text? No. Check the importer’s type rule; quoting alone is not a universal type declaration.
Can AI fill a missing amount? It may propose an investigation, but an inferred value is not recovered source data. Keep any estimate separate and labelled.
What if the sum matches but identifiers do not? Stop the handoff and repair the import from the original. A matching total does not make altered identifiers acceptable.
Proceed when the transferred records match the agreed source and unresolved limitations are explicit. This checks data fidelity, not source truth, formula correctness or the quality of later AI conclusions. Sources checked on 26 October 2026.
