Excel CSV Import Settings: Delimiters and Data Types

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, use Data > From Text/CSV to import a CSV and choose its file origin or encoding, delimiter, and data type detection in the preview. For the safest results, check those settings before loading; they determine how Excel separates fields and interprets values such as dates, long numbers, and codes with leading zeros.

Importing a CSV through Excel’s data tools

A CSV file is plain text in which separators divide fields and line breaks divide records. It does not reliably carry spreadsheet formatting, column types, or formulas. Importing through Excel’s data tools lets you inspect how the text will be interpreted before it becomes worksheet data, rather than trusting Excel to guess when the file opens.

In a Windows desktop workbook, choose Data > From Text/CSV, select the file, and review the preview. The main controls commonly include File Origin, Delimiter, and Data Type Detection. You can load the result directly or choose Transform Data to inspect and adjust it in Power Query first. The exact preview and available controls can vary slightly with Excel updates and the file’s contents.

Use the import route especially when the source is unfamiliar, contains identifiers, uses a nonstandard separator, or must be brought into Excel repeatedly. If the preview does not match the columns and values you expect, do not load it yet: change the relevant setting and confirm the preview again.

Choosing the delimiter

The delimiter is the character that marks the boundary between fields. Commas are conventional in CSV files, but other regional or business systems may use semicolons, tabs, or another character. A wrong delimiter can make a whole record appear in one column, or split a text value into extra columns.

Choose the delimiter that matches the source file, using the preview as a check. For example, a row such as R-104,North,27 should appear as three fields if commas separate the values. If the same row is tab-separated, selecting comma will not split it correctly. Some files use semicolons because their regional number conventions use commas as decimal marks; do not assume the file extension alone tells you which separator it uses.

Quoted fields are another reason to inspect the preview. In a conventional CSV, a comma inside a quoted field is part of that field, not a separator. For instance, a field containing a city and region might be written as "Portland, Oregon". A well-formed CSV importer recognizes the quotation marks and keeps that text together. If the source uses unusual quoting rules, check the output carefully or ask for a consistently formatted export.

File origin, encoding, and garbled characters

File Origin controls how Excel decodes the file’s bytes into text. UTF-8 is common, but files can use other encodings. If names or symbols appear as nonsense characters, question marks, or replacement symbols, the delimiter may be correct while the encoding is not. Try the encoding identified by the source system; if it is unknown, inspect the preview with the available choices rather than guessing from the file name.

UTF-8 files with a byte-order mark are often recognized automatically, but not every text file identifies its encoding in a way Excel can detect. A file can also contain mixed or damaged character data that no import setting can fully repair. If the preview is readable but the loaded worksheet is not, reopen the import and compare the File Origin setting before changing the data itself.

Encoding and delimiter solve different problems: encoding determines which characters appear, while the delimiter determines where columns break. Changing one will not necessarily fix the other. Keep a copy of the original file when investigating, since resaving text in another application can change its encoding or separators.

Data type detection: dates, numbers, and text

Data Type Detection tells Excel how to interpret values rather than leaving every field as text. Depending on the import interface, you may be able to detect types from an initial portion of the file, inspect the entire dataset, or disable detection. Detection is convenient for ordinary tables, but it can be risky when a column contains mixed values or values that only look numeric.

  • Dates: A value such as 03/04/2025 is ambiguous: it could mean March 4 or April 3, depending on the date convention. Check the source’s stated format and the result in the preview.
  • Numbers: Decimal and thousands separators vary by locale. A value written as 1.234,50 may not be interpreted like 1,234.50. Confirm that imported values have the intended magnitude and decimal places.
  • Mixed columns: A column containing numbers in most rows and text labels in a few may be classified unexpectedly. In Power Query, review the column’s type and correct it before loading.
  • Long numeric strings: Account references, tracking codes, and long identifiers are not measurements. Treat them as text if arithmetic is not intended.

When importing through Power Query, choose Transform Data if you need control before loading. You can inspect column types and, when regional interpretation matters, use the column’s Change Type > Using Locale option to specify how dates or numbers should be read. A type choice is not merely a display format: once Excel has converted a text value into a number or date, the original characters may not be recoverable from the displayed result.

Preserving leading zeros and identifiers

Excel may interpret a value such as 00127 as the number 127, removing the zeros because they do not affect the numeric value. That is correct for ordinary arithmetic, but wrong for postal codes, product IDs, employee numbers, and similar identifiers. Import those columns as text when every character is significant.

For a one-time Power Query import, select the relevant column and set its type to Text before loading. In the legacy Text Import Wizard, if available, select the column in the final step and choose Text as its column data format. Microsoft 365 for Windows also has automatic data conversion controls under File > Options > Data; their availability and names can depend on the Excel update. Those options can reduce certain automatic conversions, but they are not a substitute for checking the type of important columns during import.

Do not use a custom number format as the only protection for an identifier. A display format can make a number look padded without preserving the original text, and it does not turn the value into a text identifier. If the source must retain leading zeros, import it as text and verify the first few values after loading.

A reliable step-by-step import

  1. In Excel, open the workbook that should receive the data and choose Data > From Text/CSV.
  2. Select the CSV and inspect the preview. Set File Origin if characters are decoded incorrectly, and select the delimiter that produces the expected columns.
  3. Review Data Type Detection. If the file has ambiguous dates, identifiers, or mixed columns, choose Transform Data rather than loading immediately.
  4. In Power Query, verify the column names, types, and representative rows. Set identifier columns to Text and adjust date or number interpretation where needed.
  5. Choose Close & Load to put the result in Excel. Check the first and last rows, totals where appropriate, and any values that were at risk of conversion.

For a recurring workflow, keep the query in the workbook and use Data > Refresh All after the source file is updated or replaced in the expected location. Review the query’s source and transformation steps if the incoming file changes its column order, header names, encoding, or delimiter. A saved query can make repeat imports consistent, but it cannot guarantee correct results if the source format changes without notice.

Importing versus opening a CSV directly

Double-clicking a CSV or opening it with File > Open is quick, but Excel may apply regional and automatic conversion behavior without giving the same opportunity to review the data first. This is often fine for a simple, clean file. It is less suitable when a file contains leading zeros, ambiguous dates, long identifiers, or non-ASCII text. Use the import preview when preserving the original meaning matters.

The older Text Import Wizard may also be available in desktop Excel, sometimes through Data > From Text (Legacy) or an enabled legacy import command. It provides a step-by-step delimiter and column-format workflow. The modern From Text/CSV route uses Power Query and is generally more useful for repeatable transformations. On Mac, Power Query and import controls depend on the Excel version and update; use the available Data tab import command and verify the preview, since the Windows menu path and dialog are not identical in every Mac build. Excel for the web may not expose the same desktop import controls.

After editing imported data, remember that CSV is a text format, not a full Excel workbook format. Saving as CSV does not preserve multiple sheets, formulas as formulas, cell formatting, or Power Query steps. Keep the working workbook as .xlsx when you need those features, and export a CSV copy only when another system requires one.

Common import problems and what to check

  • Everything appears in one column: Reopen the import and select the separator actually used by the file. Check whether it is comma-, semicolon-, or tab-delimited.
  • Columns split in the wrong places: Check the delimiter and whether quoted text contains separator characters. Compare several rows, not just the header.
  • Dates are reversed or changed: Confirm the source’s date convention and use the appropriate locale when converting types. Do not infer meaning from a date that Excel has already reformatted.
  • Zeros disappear: Reimport the identifier column as Text. Formatting the already-converted number afterward may not restore the original value.
  • Accents or symbols look wrong: Check File Origin or encoding. If no encoding produces correct text, the source may be malformed or may have been altered before import.
  • Rows or columns seem missing: Check the source file and preview, then consider worksheet capacity. A worksheet supports up to 1,048,576 rows and 16,384 columns; larger data may require a database or another analysis tool.

Frequently Asked Questions

What is the safest way to import a CSV into Excel?
Use Data > From Text/CSV, inspect the preview, and set the delimiter and data types before loading. Treat identifiers as text and verify dates and numbers against the source.

Why does Excel put my CSV in one column?
The selected or automatically assumed delimiter may not match the file. Import it through the Data tab and try the delimiter shown by the source format, such as comma, semicolon, or tab.

How do I stop Excel removing leading zeros?
Import the affected column as Text. Do this during the import or in Power Query before loading; converting a value to text after zeros have been discarded cannot reliably reconstruct them.

How do I fix garbled characters in a CSV?
Change File Origin to the encoding used by the file, commonly UTF-8 or another encoding specified by the source. If the original file is damaged or uses mixed encodings, an Excel setting may not be enough to repair it.

Will Excel remember my CSV import settings?
A Power Query import can retain its transformation steps in the workbook, which is useful for refreshing similar source data. Directly opening a CSV is not the same saved, repeatable query workflow, and a changed source layout may require adjustments.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.