Preserve the source and define what a correct import must contain
Keep an untouched CSV and import product codes as Text before Excel interprets them as dates. For a recurring stock report, the useful acceptance check is that codes remain exact strings while quantities remain numbers, including after a refresh.
A product called JAN1 is not necessarily a January delivery date. Nor does a shelf code 1-2 mean January 2 or February 1. Changing regional settings merely to make an identifier look less wrong does not establish what the supplier intended. Ask which fields are identifiers, and record a small set of known values before opening the file.
For example, create a disposable text file with a header line ProductCode,Quantity and these three data lines, each on its own line:
JAN1,4MAR2,61-2,2
This is an illustrative acceptance sample, not an observed supplier export. The expected import has two columns and three records. Its first column contains exactly those three strings; its second column contains the numbers 4, 6, and 2, totaling 12. Save an untouched copy before experimenting. If a production file has already been converted and saved, recover the original export instead of guessing the original codes from date serials.
Choose between a direct-entry setting and a controlled import
Use the conversion setting for supported direct-entry workflows; use column types for Power Query. Microsoft explicitly states that the automatic-conversion options do not directly affect Power Query imports, so changing the checkbox alone is not a query repair.
The current automatic data conversion instructions list Excel for Microsoft 365 and Excel 2024, including their Mac editions. On Windows, open File > Options > Data > Automatic Data Conversion. On Mac, the documented route is Excel > Preferences > Edit. Disable conversion of continuous letters and numbers to dates when those strings are identifiers. This article's import steps below target desktop Excel on Windows; they are not a claim that the web or mobile interface has matching controls.
There is a critical boundary: Microsoft's advanced-options reference describes JAN1 as a continuous string that the setting can preserve, while spaced or punctuated values may still become dates. Treat 1-2 as a separate acceptance case. That same reference describes a CSV conversion warning that can allow a one-time open without conversion; a warning is useful, but it does not define the types for a recurring query.
If the switch is missing in Excel 2021, 2019, or 2016, do not assume the installation is broken. The newer option has a narrower version scope than the general import features. Use an import interface that lets you assign column types instead.
Set the code column to Text before loading
Open a blank workbook and choose Data > From Text/CSV, select the untouched sample, and inspect the delimiter preview. Choose Transform Data rather than immediately loading inferred values; Microsoft documents this in its text and CSV import instructions.
In the query editor, select ProductCode and choose Home > Transform > Data Type > Text. If asked, choose Replace Current. Microsoft's text-preserving import procedure documents that choice and gives New Query > From File > From Text as an older-interface alternative. Some previews label the editor entry Edit instead of Transform Data.
Inspect the values before loading. If an earlier inferred step already shows dates, do not accept a later Text step that merely turns those dates into text. Return to the step where the original codes are still present, replace the unwanted type conversion there, and confirm all three codes. This is a check on the transformation sequence, not a recovery method for a damaged source file.
Keep Quantity numeric, then use Close & Load. The code column's contract is exact text; the quantity column's contract is arithmetic. Making every column Text can conceal a different import defect even while the product codes look correct.
Verify the worksheet, then deliberately test a refresh
Check the actual loaded cells against the sample, then change only a quantity in a working copy of the source and refresh. The expected result is a changed quantity total with unchanged identifiers.
Assuming the header is in A1:B1, inspect A2:A4 in the formula bar: they should read JAN1, MAR2, and 1-2. In separate empty cells, check =ISTEXT(A2), =ISTEXT(A3), and =ISTEXT(A4); each should return TRUE. Check =COUNT(B2:B4) and =SUM(B2:B4); the expected results are 3 and 12. Microsoft's references explain that ISTEXT checks for text, COUNT counts numeric cells, and SUM adds values. These English function names match an English Excel interface; use the localized equivalents in other interfaces.
For the refresh check, retain the original sample and change MAR2,6 to MAR2,7 in the working CSV connected to the query. Use Data > Refresh. Recheck the three codes, three numeric quantities, and the sum: it should now be 13. Microsoft documents refresh as reapplying the query transformations. This deliberate one-field change tests whether you are connected to the intended source and whether the text rule persists.
These are reproducible expected results, not a claim that Excel was run during this article's preparation. The Microsoft instructions and repository tool implementation were inspected; your installation, query steps, and regional settings still need the sample check.
Use a preview to find rows, not to certify original strings
CSV / Excel Online Preview (csv-preview) can help locate a record and inspect the parsed table, but it is not an authoritative view of the CSV's original characters. The current component uses a spreadsheet parser without a raw-text preservation setting, so numeric or date-like values may already be interpreted during preview.
Use an ordinary approved text editor to establish the sample's original lines. Then use the preview, if appropriate, for secondary row and header inspection. A disagreement should send you back to the source and Excel import steps; downloading a new CSV from the viewer is not a way to repair a converted identifier.
The component accepts CSV, TSV, XLSX, and XLS, treats the first row as headers, and displays at most 500 matching data rows. Search changes the display, while Export CSV includes the entire currently selected sheet's parsed rows. It cannot edit an XLSX, change Excel settings, restore original strings, or preserve workbook features in its CSV export. It also loads its parser from jsDelivr, so it is not wholly offline. Use a non-sensitive sample or your organization's approved local workflow.
Diagnose failures from what changed
A date or serial in ProductCode points to a conversion before the final check. Return to the untouched file and examine the query's type step; changing worksheet formatting afterward cannot infer the lost spelling. If a code looks correct but ISTEXT is false, inspect the stored value rather than accepting its appearance.
A COUNT below 3 or a sum below 12 in the original sample suggests quantities were loaded as text or records are missing. Check the two-column preview, delimiter and Quantity type. Do not fix this by converting the identifier column back to numbers.
If the refresh total stays at 12 after the controlled change, check that you edited the connected working file and refreshed the intended query. If identifiers change only after refresh, revisit the saved transformation steps. Keep the result out of the production import until both the initial-load and refresh checks pass.
Common questions
Does disabling date conversion also protect 1-2?
Do not rely on that. The documented control covers continuous letters and numbers, with a limitation for punctuation and spaces. Assign Text to the code column and check the punctuated sample separately.
Can I fix a converted code by formatting the cell as Text?
That cannot reconstruct the original identifier. Restart from the unchanged source and prevent the conversion before loading.
Why does the setting not fix my existing query?
Power Query has its own type transformations. Inspect and correct those steps, then rerun the sample and refresh checks.
Is a successful three-row sample enough for the entire inventory?
No. It validates these cases and your chosen workflow. Also check source record counts and representative values throughout the real file, especially any additional identifier patterns supplied later.