Durjoy Paul
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.
- 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.
- 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.
- In step 2, set Comma as Delimiters and click on Next.
- In step 3, select Column data format as General and click on Finish.
- 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.
- 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.
- This will eventually set all the CSV data into different columns after a comma. Now, you can format the dataset according to your choice.
Read More: How to Open CSV File in Excel with Columns Automatically (3 Methods)
Similar Readings
- How to Import Text File to Excel Automatically (2 Suitable Ways)
- Excel VBA: Import Comma Delimited Text File (2 Cases)
- How to Import CSV into Existing Sheet in Excel (5 Methods)
- Excel VBA to Read CSV File Line by Line (3 Ideal Examples)
- How to Convert CSV to XLSX without Opening (5 Easy Methods)
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.
- 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.
- 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.
- 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.
- 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.
