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.
What Is CSV File Format?
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.
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. Using Legacy Wizard to Open CSV File in Excel with Columns
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
2. Using Excel 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: How to Open Large CSV Files in Excel
3. Opening CSV File with Columns from Folder Selection Tab
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: Open CSV File in Excel Without Formatting
Download Practice Workbook
Download this practice workbook
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.
Related Articles
- How to Read CSV File in Excel
- How to Open CSV with Delimiter in Excel
- How to View CSV File in Excel
- CSV File Not Opening Correctly in Excel
- [Solved:] Excel Is Opening CSV Files in One Column
<< Go Back to Open CSV in Excel | Import CSV to Excel | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
