Import CSV into Excel: delimiters, encoding and column types

You double-click a CSV file and Excel puts everything in one column, complete with semicolons. Or the columns look fine, but a product code has lost its leading zeros. My response is to return to the original file and import it with explicit settings. Repairing values after an unwanted conversion can be much harder than reading the source correctly in the first place.

I will cover Data > From Text/CSV, delimiters, encoding, column types and a small result check. The main environment is Excel for Microsoft 365 on Windows with Power Query. Availability and menu locations can differ on Mac and in other versions. Microsoft’s text file import guide describes the available routes. The goal is a repeatable import whose decisions you understand.

CSV contains text that needs interpretation

A CSV file stores text records with fields separated by an agreed character. The extension alone does not guarantee commas: semicolon-separated exports are common too. The format does not preserve cell colours, column widths or multiple worksheets. When Excel reads the file, it must decide how to split the text and what types to assign to the resulting values. Those are separate decisions with separate possible failure modes.

A string such as 00123 might be an identifier, although the program may interpret it as a number. The date 03/04/2026 can have different meanings under different regional conventions. A neat five-column preview therefore does not establish that the import is correct. You also need the definitions of those columns and the source’s formatting rules. Visual order is only one part of the check.

Opening a CSV directly can work when the file and your settings match. There is no reason to claim that it must always fail. For analytical work, however, I prefer an explicit import because I want to inspect the interpretation before accepting it. That matters particularly for identifiers, dates and numbers whose decimal convention differs from the one used on my computer.

Preserve an unchanged source

Keep the received CSV unchanged and save the imported result in a separate XLSX workbook. That gives you a reliable starting point if a type conversion alters a value. Avoid saving a repaired view over the original before understanding the problem. Otherwise, the next attempt may begin with information that the previous attempt has already transformed or lost. Retaining the source is a practical part of troubleshooting.

Open a copy in a text editor and inspect the header, several records and examples containing accented characters. Look for the field delimiter, date order, decimal separator and quotation marks. You do not need to understand every record. Establish a few rules that you can compare with the import preview. For an ambiguous date, consult the export specification or the source owner instead of guessing from a single example.

The following synthetic dataset uses semicolons, day/month/year dates and decimal commas. It retains Polish column names and city names so both language versions of this exercise refer to exactly the same file. You can enter it in a text file saved as UTF-8 with a CSV extension.

ID;Data;Miasto;Kwota;Opis
00123;03/04/2026;Łódź;12,50;"Opakowanie; duże"
00124;04/04/2026;Kraków;7,50;Standard
00125;05/04/2026;Gdańsk;0,00;Zwrot

The intended output has three records and five columns. Data means Date, Miasto means City, Kwota means Amount and Opis means Description. The first date is 3 April 2026; IDs keep all five characters. Amounts total 20.00. The first description includes a semicolon protected by quotation marks. Those specific expectations provide a much better check than simply deciding that the preview looks plausible.

Choose From Text/CSV and inspect the preview

In an empty workbook, choose Data > From Text/CSV, select the file and inspect the preview. Some versions place this under Get Data > From File. Before loading anything, check the column boundaries and first values. Choose Transform Data to open Power Query when you need to set types. Loading immediately is convenient only after you have established that the preview’s decisions are suitable for the source.

Select a semicolon delimiter for this exercise. Choosing a comma would split on characters used inside the amounts and produce a structure that does not match the headers. If everything appears as one long string containing semicolons, inspect the delimiter setting first. The Text/CSV connector documentation describes delimiter and type-detection controls. Change one interpretation at a time so you can see which problem it resolves.

Check whether the first row became the headers. If you see labels such as Column1 with ID and Data in the first data row, use the first-row-as-headers option. If the headers are already correct, do not promote them again: that could turn the first transaction into column names. Compare every step with the actual file rather than applying a memorised sequence regardless of the preview.

Delimiters, decimal separators and quotes

A field delimiter separates columns. A decimal separator belongs inside a number. Our file uses semicolons for the first role and commas for the second. Replacing every comma with a full stop in a text editor is risky because the same character can also occur in descriptions. Set the interpretation of the relevant column instead of changing the entire file through a broad search-and-replace operation.

Quotation marks allow a delimiter to occur within a field. The description "Opakowanie; duże" should occupy one cell despite its internal semicolon. If a record splits incorrectly at such a description, inspect the export’s quoting convention. The Csv.Document reference also describes quoted line breaks. Splitting every line at every semicolon does not reproduce the full behaviour required to read CSV data properly.

Avoid stripping quotation marks indiscriminately. They may protect a delimiter or a line break that belongs to the content. If the export is malformed, request a corrected file or describe the limitation. Moving several cells by hand can make one worksheet look right, but it does not establish a rule that will survive the next refresh. Understand the source convention before saving a repeatable transformation.

Encoding controls the characters

If Łódź appears as a collection of strange characters, inspect the file encoding. Use UTF-8 for this exercise. Changing the delimiter will not repair letters, and changing the font will not correct the interpretation of bytes. Encoding is a separate import setting. Inspect several words containing different accented characters rather than checking one convenient cell and assuming that the rest of the text must be correct.

Microsoft explains how a BOM helps Excel recognise UTF-8. A UTF-8 file with that marker can open normally, while Power Query offers another route with explicit controls. Do not assume that every file containing Polish characters uses UTF-8. Choose the actual source encoding. A readable header written in basic ASCII is insufficient evidence that every accented character has been decoded correctly.

If replacement characters are already present in the raw text, changing the import setting may not recover the original information. You need a corrected export. Distinguish an incorrect reading of valid bytes from text damaged earlier in the process. Both problems can look similar in the worksheet, but they require different actions. This is another reason to retain an unchanged source when investigating an import.

Set identifiers to text before zeros disappear

Set ID to Text for 00123, 00124 and 00125. We will not add or average these identifiers, so treating them as numbers offers no benefit. If Power Query added an automatic Changed Type step that converted the IDs, edit or remove that step before assigning the intended types. Converting an already shortened 123 to text does not recreate the original leading zeros by itself.

Use the same approach for long identifiers. Excel numbers have a maximum precision of 15 significant digits, so longer codes need text storage to preserve every digit. Microsoft’s guide to leading zeros and large numbers explains the limitation. A display format that pads a number with zeros does not replace preserving the actual source code. Protect the contents before worrying about how the cell appears.

Compare several imported IDs with the source, including leading-zero examples and the longest values. Check an alphanumeric code too if one occurs later in the file. The first hundred identifiers looking numeric does not establish the correct type for the entire column. Choose the type from the field’s meaning in the source system. A small preview is a sample of values, rather than a complete definition of that field.

Interpret dates and amounts using the source locale

For Data, change the type using locale. Choose Date and a region consistent with day/month/year, such as Polish. Here 03/04/2026 means 3 April, rather than 4 March. The regional setting resolves the meaning of the text during conversion. Changing the date display afterwards cannot fix a value that was already interpreted as the wrong day. Check the resulting calendar date, not merely the familiar appearance of the cell.

For Kwota, choose an appropriate numeric type with a locale that recognises decimal commas. The values 12,50 and 7,50 should become numbers totalling 20.00. Microsoft documents type conversion using locale. Source conventions can differ from the settings of the person opening the workbook. Recording that interpretation in the query helps preserve the intended result when the workbook moves between computers.

Automatic type detection deserves inspection too. For unstructured sources, standard detection can use the first 200 rows. The Power Query data type guide recommends assigning types explicitly. Inspect later records and conversion errors, especially when early rows are unrepresentative. A column that starts with simple integers may contain decimal values later. The output needs to reflect the complete field definition, rather than the accidental contents of its first sample.

Load to a table and verify the result

Once the structure and types are correct, choose Close & Load and place the result in a worksheet table. Save an XLSX workbook to retain the query and working structure. The CSV remains the text source. The Excel table guide explains the difference between a table object and an ordinary range. Here the table is the destination for data after the import decisions have been applied.

For this exercise, check three records, five columns, intact IDs and the first date. Amounts should be numeric and total 20.00. The first description must occupy a single field, including its internal semicolon, and the city names must retain their accented letters. Inspect the final zero amount as well. It is an explicit source value here, rather than a substitute introduced for a missing observation.

A larger dataset needs equivalent checks agreed with its source: record count, date range, key uniqueness and selected control totals. The absence of error messages is insufficient. A date can be interpreted incorrectly without generating a technical error. I choose checks that establish whether the imported information still has the intended meaning. A rectangular table that happens to load is only the beginning of that assessment.

Refreshing and exporting are separate operations

A Power Query import can become a repeatable process. Refreshing reads the source again and applies the saved steps. Agree whether its name and location stay fixed, however. If a new export goes into a different folder, the existing connection may still read yesterday’s file. Check the period and record count after refresh rather than relying entirely on a message saying that the operation finished successfully.

Avoid making source corrections only by typing into the output table and expecting them to survive reloading. Decide whether the correction belongs in the source, an explicit query step or a separate amendment table joined by ID. That decision depends on data ownership. The important point is that the next refresh should not silently restore an old problem or discard an adjustment that someone assumed was permanent.

Exporting back to CSV establishes a new agreement with the recipient. Confirm delimiter, encoding, date format and units. Saving an XLSX file as CSV does not guarantee compatibility with every receiving system. Inspect the exported text and check a sample again. Cell colours, additional worksheets and formulas as a working calculation mechanism do not transfer into plain text as they do within the original workbook.

Investigate errors before removing records

Inspect individual conversion failures before deleting rows. A misplaced delimiter, an unexpected decimal convention and an invalid date require different fixes. Do not replace every error with zero: a genuine zero amount and an unrecognised amount mean different things. If the source convention cannot be established reliably, preserve the text and explain the limitation.

Also agree whether each export contains the full current dataset or only new records. Replacing a complete source with an incremental file can remove older records after refresh. Appending complete exports can count the same IDs repeatedly. This exercise assumes that the source always contains all current records, so adding the fourth record produces four records overall.

Exercise: reproduce the import

Import the sample with ID explicitly set to Text, Data interpreted as a date using the correct region and Kwota interpreted with a decimal comma. Save the result separately as XLSX. Return to the source and compare every record. You should be able to point to the setting responsible for column separation, accented characters, calendar dates and preserving the complete code 00123.

Then add a fourth record to a source copy with a new ID and an amount of 5,00, keeping the same format. Refresh and expect four records totalling 25.00. If that does not happen, inspect the source location and saved steps. This small exercise checks whether the process is reproducible, rather than whether you managed to arrange one worksheet into a plausible-looking result.

The original Polish article retains its import video; this English text explains the same workflow independently. Once the source is reliable, try percentage change calculations. First confirm that the compared values are genuinely numbers, use the same units and cover appropriate periods. A perfectly entered growth formula cannot repair data whose meaning changed while it was being imported.