CSV Opens in One Column in Excel? Choose the Delimiter on Import

Open a copy of the CSV in a plain-text editor and look at the header and a few rows. A file with a CSV extension may use commas, semicolons or tabs. If a field has quotation marks around it, a comma inside the quotes can be part of that value. Look for the separator between fields.

In Excel, find From Text/CSV on the Data tab, under Get Data if it is not visible directly. Select the file. In the preview, choose Comma, Semicolon or Tab to match the file. Check that the headers and several rows line up. If the columns split correctly but letters look wrong, choose the file’s encoding in File Origin, such as UTF-8 if that is what the exporter used.

If Data Type Detection is available in the preview, choose Do not detect data types to load the columns as text. To set selected columns instead, choose Transform Data. In Power Query, remove any automatic Changed Type step that already converted the values, then set the ID or postal code columns to Text. Check the zeros in the preview before choosing Close & Load.

Work on a copy or choose an empty destination with room to the right. Select the column, then use Data > Text to Columns > Delimited. Pick the separator and inspect the preview. If Excel already changed a value when it first opened the CSV, go back to the original file and import again.

Compare a few rows with the original and check a value that starts with zero. Save the Excel result as an Excel Workbook (.xlsx) if you want to keep the table and formatting. Keep the original CSV available for a fresh import.