How to Import CSV and Text Files into Excel 365

Step-by-step

Excel stores data in cells, so a text file needs a way to mark where one value ends and the next begins. That character is the delimiter. The most common is the comma, which gives you CSV (comma-separated values) files. Financial programs often use tabs instead, because commas already appear inside numbers like $10,000. Those are tab-delimited files.

1. Start the import

Go to Data > Get & Transform Data > From File > From Text/CSV and choose your file. The example is a customer list (company name, phone number, address) separated by commas.

2. Check the preview

Excel examines the file and splits it into columns based on the delimiter it detects. Leave File Origin and Data Type Detection at their defaults unless you’re dealing with unusual data. If the Delimiter was guessed wrong (for example, your file is tab-delimited), pick the right one from the drop-down.

3. Choose how to load it

• Load puts the data on a new worksheet exactly as shown in the preview.
• Load To lets you choose a table, PivotTable, PivotChart or just a connection, on a new or existing sheet.
• Transform Data opens the Power Query editor so you can clean the data first.

If you see empty columns after loading, check the source file. In the example, an extra comma after a name or phone number creates an empty column.

4. When Excel won’t split the data: Text to Columns

Sometimes each row still lands in a single column. In that case, import it anyway, select the cells and go to Data > Text to Columns.

1. Step 1: choose Delimited if a character separates the values, or Fixed width if the columns are lined up with spaces.
2. Step 2 (Delimited): tick the delimiter (the example uses a semicolon), or choose Other and type it. If your file uses more than one, such as a comma and a space, tick them all plus Treat consecutive delimiters as one. Values in double quotes are treated as text; change the Text qualifier if your file uses single quotes. Step 2 (Fixed width): click in the preview to add column break lines and drag them into place.
3. Step 3: click each column and choose a format (General, Text or Date) or Do not import column (skip). Set the Destination if you want the result somewhere else, then click Finish.

Tip: you can always import everything as General and change the formats afterwards in Excel.