How to Open CSV File with Columns in Excel (3 Easy Ways)

Sometimes you need to open a CSV file in Excel. You can do it by opening a CSV file or importing it using several processes. This article will provide you with a proper guideline to open a CSV file in Excel with Columns. I hope you enjoy the article and enhance your Excel knowledge.


Download Practice Workbook

Download this practice workbook


What Is CSV?

CSV means comma-separated values where you can save your data in a tabular format. It is mainly a text file where your information is separated by a comma. CSV is faster than Excel. Sometimes, text editors need to edit files but they can’t do it in Excel. CSV files give text editors a good platform to edit text.


3 Methods to Open CSV File with Columns in Excel

To open a CSV file in Excel with columns, we have shown three processes where you can convert a CSV file to Excel. All three methods are really easy to approach. To use all the methods effectively, we take a CSV file that denotes the product name, Salesperson, and the product’s unit price and quantity.


1. Open CSV File with Columns Using Legacy Wizard in Excel

The first and most used method to open a CSV file in Excel is based on Legacy Wizard. This method will import the CSV file in Excel and modify the delimiter and put it in different columns.

Steps

  • First, you need to enable the text import wizard. Go to the File tab in the ribbon and select More From there, select Options.

  • Excel Options window will pop up. Select the Data. From the Show legacy data import wizards, and click on From Text (Legacy). Finally, click on ‘OK’. It will enable the text import wizard.

Open CSV File with Columns Using Legacy Wizard in Excel

  • To import a CSV file, go to the Data tab in the ribbon and select Get Data from the Get & Transform Data group.

  • From the Get Data option, select Legacy Wizards where you’ll get From Text (Legacy). Click on it.

Open CSV File with Columns Using Legacy Wizard in Excel

  • A new window will appear where you can find your CSV file and finally click on Import.

  • Text Import Wizard dialog box will occur where you need to go through all three steps. In the first step, choose the file type as Delimited to describe your data. Click on My data has headers which will set your first row as a header. Finally, click on Next.

Open CSV File with Columns Using Legacy Wizard in Excel

  • In step 2, set Comma as Delimiters and click on Next.

  • In step 3, select Column data format as General and click on Finish.

Open CSV File with Columns Using Legacy Wizard in Excel

  • A dialog box will appear where you can select the place where you want to put your data. then, click on ‘OK’.

  • Now, the problem is the CSV file imports one column that is not what we want. To move all the data into different columns after the comma, we need to select the whole dataset.

  • Now, go to the Data tab in the ribbon and select Text to Columns from the Data tools group.

  • Convert Text to Columns Wizard dialog box will appear. Here, we need to do the same steps as the above process. In the first step, choose the file type as Delimited to describe your data. Click on Next.

Open CSV File with Columns Using Legacy Wizard in Excel

  • Set Comma as Delimiters and take a look at the Data Preview where you will find all the data are separated by columns. Click on Next.

  • In the third and final step, set the Column data format as General and also set your preferred Destination. Then click on Finish.

Open CSV File with Columns Using Legacy Wizard in Excel

  • This will eventually set all the CSV data into different columns after a comma. Now, you can format the dataset according to your choice.

Open CSV File with Columns Using Legacy Wizard in Excel

Read More: How to Open CSV File in Excel with Columns Automatically (3 Methods)


Similar Readings


2. Using Power Query to Open CSV File with Columns

Our second most popular method is by utilizing Power Query. Power Query is one of the most powerful features in Excel. We can do any higher-level problem easily. To open a CSV file in Excel Using Power Query, you can follow the following steps carefully.

Steps

  • First, go to the Data tab, and select From Text/CSV from the Get & Transform Data group.

Using Power Query to Open CSV File with Columns

  • A new window will appear where you can select your CSV file and click on Import.

  • A new dialog box will appear where data from the CSV file is shown in one column. You need to Transform Data to Power Query for further modification.

  • After clicking Transform Data, the Power Query dialog box will appear where you need to split all the text into different columns and set the first row as a header.

Using Power Query to Open CSV File with Columns

  • To split all the text after delimiter, we need to go to the Home tab in the Power Query, and in the Transform group, select Split Column. In the split column option, select By delimiter which will eventually split the column after the delimiter.

Using Power Query to Open CSV File with Columns

  • Split Column by Delimiter dialog box will appear. In the Select or enter delimiter segment, select Comma as your preferred delimiter and also select Each occurrence of the delimiter from the Split at section.

  • To make the first row a header, go to the Home tab in the Power Query and select Use First Row as Headers from the Transform group.

Using Power Query to Open CSV File with Columns

  • Now, select Close & Load from the Home tab.

  • It will appear on a different sheet in Excel but you can copy and paste it to your preferred sheet. It will provide the desired result where texts import in different columns after the comma.

Read More: Excel VBA to Import CSV File without Opening (3 Suitable Examples)


3. Using Open Command

We can open a Comma Separated Values by using a simple Open Command in Excel. Even though the CSV file is created in a different format, you can easily open it in Excel

Steps

  • First, go to the File tab and select the Open command.

  • A new dialog box will appear. Select Text Files from the drop-down box in the lower right bottom. Now, select your CSV file and click on Open.

  • It will open the CSV file in one column, then you can split the text into several columns after comma by using Text to Column just like the first method.

Read More: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)


Conclusion

We have discussed three different methods to open a CSV file to Excel with columns. All the methods are fairly easy to digest. I hope you enjoy the whole article. If you have any questions, feel free to ask in the comment box, and don’t forget to visit our Exceldemy page.


Related Articles

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo