To fix most Excel CSV import issues, import the file through Data > From Text/CSV instead of double-clicking it. Check the delimiter and file origin in the preview, then choose Transform Data to set sensitive columns—such as IDs, postal codes, and dates—to the right data type before loading.
What happens when Excel imports a CSV
A CSV (comma-separated values) file is plain text arranged in rows and fields. It does not retain an Excel workbook’s cell formatting, formulas, multiple sheets, charts, or column widths. A value such as 00127 is simply characters in the file; when Excel opens or imports it, Excel may interpret those characters as the number 127 and display the leading zeros no longer.
Excel has two common ways to bring in a CSV. Opening it directly—by double-clicking it or opening it from Excel—can be convenient, but offers less control over how columns are interpreted. The import route uses Excel’s text/CSV connector and provides a preview where you can inspect separators and encoding, and, if needed, edit the data in Power Query before loading it.
CSV is also a family of text formats rather than a guarantee that every file uses commas. Depending on the software and regional settings that created it, a file may use semicolons or tabs between fields. A sound import begins by confirming what is actually in the file rather than assuming its name tells the whole story.
Import a CSV with control over the results
- In Excel for Microsoft 365 on Windows, open a blank workbook or the workbook where the data should go. Choose Data > From Text/CSV, browse to the file, and select Import.
- Review the preview. Check the displayed delimiter and the first few rows, especially any field containing commas, dates, leading zeros, accented letters, or long numbers.
- If the preview is correct and no columns need special handling, choose Load. To inspect or change column types first, choose Transform Data.
- In Power Query Editor, select a column and use Home > Data Type to choose a suitable type, such as Text or Date. When finished, choose Home > Close & Load.
For a reusable import, load the data as a query rather than treating the worksheet as a one-time paste. If the source CSV is later updated, the query can usually be refreshed from the workbook. On Mac, the Power Query connector and available options depend on the Excel version and build; look under Data > Get Data for a text/CSV option. If it is not available, update Excel or use a compatible import workflow rather than assuming Windows menu paths will be identical.
Fix the wrong delimiter or columns joined together
If the whole row appears in one Excel column, or fields are split in the wrong places, the delimiter is the first thing to check. In the import preview, choose the separator that appears between fields in the source: commonly comma, semicolon, tab, or another character. A semicolon-separated file can still have a .csv extension.
For example, a row such as Rina Patel;West;12 needs a semicolon delimiter to become three fields. Choosing comma instead will leave it as one field. Conversely, choosing semicolon for a comma-separated file produces a similar problem. If the preview looks wrong, try another delimiter before loading rather than splitting columns manually after the fact.
A comma inside a field does not always mean that the field should split. CSV files commonly enclose such a field in double quotes, as in "Patel, Rina",West,12. A correctly formed importer treats the quoted name as one value. If the quote marks are missing or malformed in the source, the row can still split incorrectly; in that case, correcting the source file or asking its provider for a properly formatted export is often safer than trying to repair every row in Excel.
When opening a CSV directly, Excel may use the list separator associated with regional settings, which can differ from the separator used by the file. Importing through the preview gives you an opportunity to select the file’s actual delimiter. If the separator changes between files, do not assume changing a computer-wide regional setting is the best fix; use the import controls for the individual file where possible.
Preserve leading zeros, long numbers, and IDs
Excel interprets many imported values as numbers, dates, or other types. That is useful for amounts you intend to calculate, but damaging for identifiers that only look numeric. Postal codes, account references, product codes, and employee IDs can have meaningful leading zeros. Set these columns to Text in Power Query before loading so Excel keeps values such as 00127 as text.
Very long digit strings need similar care. Excel supports only 15 digits of numeric precision; digits beyond that may be changed when a long identifier is treated as a number. Import those values as text if they are identifiers rather than quantities. Formatting a cell as Text after digits have already been lost does not restore the original value, so set the type before loading or re-import the original CSV.
There is a related misconception that putting an apostrophe in front of a value is a universal CSV fix. An apostrophe typed into a worksheet can signal text entry, but a CSV file is plain text and its contents may be handled differently by different programs. For reliable import, set the column’s type to Text during import and check the resulting values. Avoid altering the source with extra characters unless the system that will read it explicitly expects them.
Stop dates, decimals, and other values changing
A value such as 03/04/2025 is ambiguous: it can mean March 4 or April 3 depending on the date convention. Excel may infer a date and convert the value based on its interpretation. If the field is a date, verify the intended day-month order and select an appropriate date type or locale in the import workflow. If it is a code or an exact text label, set it to Text instead of allowing automatic date conversion.
Decimal and thousands separators can also differ between regions. A value written as 1,25 in one convention may mean one and a quarter, while a comma in another file may separate fields. If amounts appear as text or are interpreted with the wrong decimal place, inspect both the file’s actual format and the locale used to interpret it. Power Query’s type and locale options can help when the source convention is known; do not guess based only on how a value looks in one row.
Keep the source data’s meaning in mind when choosing types. Dates intended for sorting and date arithmetic should be real dates; product codes should generally remain text; amounts used in calculations should be numeric. After loading, spot-check representative examples and compare totals or row counts with the source system. A successful import is not just a neat preview—it preserves the information in a form that suits the task.
Resolve garbled characters and blank-looking fields
Accented characters or non-Latin text that appear as question marks or strange symbols usually point to an encoding mismatch. In the text/CSV preview, check File Origin and choose the encoding that matches the source. UTF-8 is common; Excel’s list may label it 65001: Unicode (UTF-8). If the provider specifies another encoding, use that rather than cycling through options at random.
Some text may look blank because the source contains empty fields, spaces, or unusual characters. A row such as 104,,In progress has an empty second field; it is not necessarily a missing column caused by Excel. Check the raw file in a plain-text editor when the preview does not explain the result. Do not save a file from a text editor with a different encoding or separator unless you intend to change the source format.
Line breaks inside quoted fields can also make a file look irregular when viewed as raw text. A well-formed CSV parser can keep a quoted multiline field together, but a malformed quote can make subsequent rows appear shifted. If only certain records are affected, inspect those exact lines or request a fresh export from the producing application.
Troubleshoot a file that will not import cleanly
- Everything is in one column: Reopen the import preview and select the separator used in the file.
- Leading zeros disappeared: Re-import the original and set the relevant column to Text before loading.
- Dates changed meaning: Check the source’s date convention and use a suitable type or locale, or use Text if the value is not truly a date.
- Characters look corrupted: Select the matching file origin or encoding, commonly UTF-8, and inspect the source if the issue remains.
- Only some rows are malformed: Look for inconsistent separators, unclosed quotes, embedded line breaks, or a damaged export in those records.
- The file is too large: A worksheet is limited to 1,048,576 rows and 16,384 columns. If the data exceeds those limits, use Power Query or another data tool to transform or analyze it without loading every row to a sheet.
For difficult cases, open a copy of the CSV in a plain-text editor and inspect a few affected lines. This can reveal whether the file uses tabs, semicolons, quotes, or a particular line-ending or encoding pattern. Keep the original unchanged, especially if it is the only copy or is generated by another business system.
Choose between importing, opening, and saving as CSV
Direct opening is quickest for simple files that contain ordinary text and numbers, and where Excel’s automatic interpretation is acceptable. The import route is preferable when column types, separators, or encoding matter, or when the same source will be refreshed. Pasting from a text editor can be useful for a small one-off snippet, but it is less reliable for large files and quoted fields containing separators.
Saving a workbook as CSV is not the reverse of keeping a full Excel workbook. A CSV export writes a plain-text representation of the active sheet, so other sheets, formulas as formulas, formatting, and workbook features are not preserved. Excel may show a warning about features that cannot be saved in the selected format. Keep an .xlsx copy if you need to retain the workbook, and use CSV only when a receiving system expects that exchange format.
When Excel creates a CSV, regional settings and the chosen CSV format can affect the delimiter and encoding. If another application requires a specific format, check its import requirements and verify the exported file in a text editor. The extension alone does not guarantee a particular delimiter or character encoding.
Frequently Asked Questions
Why does Excel put my CSV data in one column?
The delimiter selected by Excel does not match the one in the file. Use Data > From Text/CSV, inspect the preview, and select the comma, semicolon, tab, or other separator that actually divides the fields.
How do I stop Excel from removing zeros at the start?
Import the original CSV through the text/CSV connector, choose Transform Data, and set the identifier column to Text before loading. Changing the format after Excel has removed the zeros does not recreate them.
Can I make Excel stop turning values into dates?
Set the affected column to Text during import if it is not meant to be a date. If it is a date, confirm the source’s day-month convention and choose a suitable date type or locale before loading.
Why are special characters wrong in my CSV?
The import encoding may not match the file. Check File Origin in the preview and select the encoding specified by the source, often UTF-8. If the characters remain incorrect, the file may already have been exported with damaged text.
Will Excel preserve every part of my workbook in a CSV?
No. CSV stores plain-text rows and fields, not workbook features such as multiple sheets, formatting, charts, or formulas as formulas. Save an .xlsx copy when those features need to be retained.
Related
- Excel Data Validation: Create a Yes-or-No Dropdown
- Excel Conditional Formatting Based on an Adjacent Cell
- Excel Conditional Formatting for Absolute Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Not Working? Causes and FixesIf VLOOKUP is not working, set its final argument to FALSE for an exact match, then check that…
- DDE Error in Excel: What It Means and How to Fix ItA DDE error in Excel usually means Windows or another program cannot pass a request to Excel through…
- Excel #VALUE! Error When a Linked Workbook Is ClosedYes—Excel can return #VALUE! when a formula uses certain functions, such as SUMIF or COUNTIF, to evaluate ranges…
- Excel MID Formula Not Working? Common Fixes That WorkIf Excel’s MID formula is not working, first check that its starting position and character count are correct:…
- COUNTA Excel Not Working? Find the CauseIf COUNTA seems wrong, first check what is actually in the cells: the function counts any nonempty cell,…
- Excel Won’t Let You Delete a Row? Try These FixesTo delete a worksheet row in Excel for Microsoft 365 on Windows, select its numbered heading, then choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.