In this article, I will discuss how you can convert CSV files into excel files with multiple columns (.xlsx format). As CSV files are easy to operate and open with simple applications, often we receive data in .csv format. Later, due to further calculation or better representation of data we have to convert CSV files into excel files. Let’s go through the article to learn converting CSVs into excel.
Download Practice Workbook
You can download the practice workbooks that we have used to prepare this article.
What Is a CSV File?
CSV stands for comma-separated values. This is a plaintext file format where data is separated by commas. You can open a CSV file using simple text editors such as Notepad. Besides, we can open CSV files through spreadsheet applications such as Excel or Google Sheets.
What Is an Excel File?
Excel files generated from Microsoft Excel mainly have a .xlsx file extension. These files can be opened through any spreadsheet software like Google Sheets, Open Office, or Microsoft Excel. Unlike CSV files, you can do various calculations in excel files. Other than that, in excel files you can apply varied data representation techniques to understand the pattern of data more closely.
5 Methods to Convert CSV to Excel with Columns
1. Apply ‘Save As’ Option to Turn CSV Files into Excel
Suppose, we have the below CSV file opened with Notepad. The data in the below file are separated into multiple columns. However, we can only see comma-separated values in Notepad.
Now, I will convert the CSV file into an excel file. Follow the below steps to perform the task.
- First, do not open the CSV file. Instead right-click on the file and go to Open with > Excel.
- As a result, the CSV file is now open in Microsoft Excel. Next, from the Excel Ribbon, click on the File tab.
- Now go to File > Save As > Browse.
- Then the Save As dialog shows up. Here, go to the location where you want to save the file.
- After that, give a File Name and choose the Save as type: Excel Workbook (*.xlsx). Press Save when you are done with the process.
- At last, you will see the above CSV file is transformed into an Excel file. You can see data are separated into columns. Later, format the data as per your requirement.
2. Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns
This time I will convert CSV file data into multiple columns in excel. Here I will use the text to column function in excel to transform single columns data into multiple columns.
- Initially, open the CSV file with excel.
- Next, select the column (column A) that has comma-separated values.
- Now, go to Data > Text to Columns.
- As a consequence, the Convert Text to Columns Wizard appears. Click on Delimited and press Next.
- Put a checkmark on Comma when the next wizard comes up. Press Next again.
- Then choose the Destination location and press Finish.
- As a result, we will get the below output in excel.
- Although our data is separated into columns now, this is still a CSV file. To convert the file into an excel file we have to save the file in a .xlsx format. To do that go to File > Save As > Browse (see Method 1).
- When the Save As dialog appears, type the File Name and choose the .xlsx file format, and press Save (see Method 1).
3. Convert CSV to Excel Files Using ‘From Text/CSV’ Feature
I will import a CSV file into excel using the ‘From Text/CSV’ feature in this method. Later, I will save the data as an excel file with columns. Follow the below steps to do the task.
- Firstly, open Excel and go to Data > From Text/CSV icon.
- Next, the Import Data dialog will appear. From there, select the CSV file that you want to import and press Import.
- Consequently, the CSV file is imported into excel as below. Now, click on Load.
- As a result, we can see the data from the CSV file is loaded as in the below table.
- Click on the table, and thus the Table Design tab appears.
- Go to Table Design > Convert to Range.
- Excel will prompt the below warning, press OK.
- Upon clicking OK, we will get the below range. If you want you can clear the cell formatting by going to Home > Editing > Clear > Clear Formats.
- Later you can format the dataset as you like. Go to the File tab to bring the Save option.
- Afterward, click on Save.
- When the Save As dialog come up, give a name to your file and press Save. So, finally, now you have the CSV file converted into an excel file (with columns).
You can get the ‘From Text/CSV‘ by following the path Data > Get Data > From File > From Text/CSV too.
4. CSV to Excel File Conversion with ‘Legacy Wizard’ Feature
Now I will import a CSV file using the Legacy Wizard from the Get Data option. Before importing the CSV file, I will show you how you can add the legacy wizard to excel.
- After opening Excel, first, go to the File tab.
- Then go to Options.
- Consequently, the Excel Options dialog shows up. From the dialog, go to Data options.
- Now put a checkmark on From Text (Legacy) and press OK.
- As a result, the From Text (Legacy) feature is added to excel. To apply the feature go to Data > Get Data > Legacy Wizard > From Text (Legacy).
- Consequently, the Import Text File dialog appears, choose the CSV file you want to get in excel, and press Import.
- After that, the Text Import Wizard shows up. Here, click on the Delimited, put a checkmark on My data has headers, and press Next.
- Put a checkmark on Comma and again press Next.
- Press Finish after that.
- As a consequence the Import Data dialog shows up, choose the data destination location, and press OK.
- Finally, we can see that CSV data is imported into excel, separated by columns.
- Likewise described in Method 3, we will save the data. To do that, go to the File tab.
- Then go to Save, type a file name and click Save (see Method 3).
5. Excel VBA to Transform CSV to Excel with Columns
You can use Excel VBA to convert CSV files into excel files with multiple columns. Suppose we have a CSV file in a specified location stated below.
Now I will convert the CSV file into Excel with columns. Let’s follow the below process to do the task.
- Open Excel, and go to Developer > Visual Basic to bring the VBA window.
- When the VBA window appears, go to Insert > Module.
- Then, type the below code in the newly opened Module. Save the file and run the code using the F5 key.
Sub Convert_CSV_to_EXCEL() Dim w As Workbook Set w = Workbooks.Open("C:\Users\User\OneDrive\Desktop\ExcelDemy\How to Convert CSV to Excel with Columns\CSV FIle.csv") w.SaveAs Filename:="C:\Users\User\OneDrive\Desktop\ExcelDemy\How to Convert CSV to Excel with Columns\CSV FIle.xlsx", _ FileFormat:=xlWorkbookDefault, _ ReadOnlyRecommended:=False, CreateBackup:=False End Sub
- Upon running the code, you will see the below excel file with multiple columns is created.
- Now if you go to the CSV file location, you will see the newly created excel (with .xlsx extension) file too.
In the above article, I have tried to discuss several methods to convert CSV files into excel files elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.