How to Convert CSV to Excel with Columns (5 Methods)

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.

Apply ‘Save As’ Option to Turn CSV Files into Excel

Now, I will convert the CSV file into an excel file. Follow the below steps to perform the task.

Steps:

  • First, do not open the CSV file. Instead right-click on the file and go to Open with > Excel.

Apply ‘Save As’ Option to Turn CSV Files into Excel

  • As a result, the CSV file is now open in Microsoft Excel. Next, from the Excel Ribbon, click on the File tab.

Apply ‘Save As’ Option to Turn CSV Files into Excel

  • Now go to File > Save As > Browse.

Apply ‘Save As’ Option to Turn CSV Files into Excel

  • 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.

Steps:

  • Initially, open the CSV file with excel.
  • Next, select the column (column A) that has comma-separated values.

Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns

  • Now, go to Data > Text to Columns.

Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns

  • As a consequence, the Convert Text to Columns Wizard appears. Click on Delimited and press Next.

Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns

  • Put a checkmark on Comma when the next wizard comes up. Press Next again.

Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns

  • Then choose the Destination location and press Finish.

  • As a result, we will get the below output in excel.

Insert ‘Text to Column’ Option to Convert CSV to Excel with Columns

  • 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).

Read More: How to Convert CSV to XLSX Command Line (with Easy Steps)


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.

Steps:

  • Firstly, open Excel and go to Data > From Text/CSV icon.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • Next, the Import Data dialog will appear. From there, select the CSV file that you want to import and press Import.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • Consequently, the CSV file is imported into excel as below. Now, click on Load.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • As a result, we can see the data from the CSV file is loaded as in the below table.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • Click on the table, and thus the Table Design tab appears.
  • Go to Table Design > Convert to Range.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • Excel will prompt the below warning, press OK.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • 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.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • Later you can format the dataset as you like. Go to the File tab to bring the Save option.

Convert CSV to Excel Files Using ‘From Text/CSV’ Feature

  • 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).

Note:

You can get the ‘From Text/CSV‘ by following the path Data > Get Data > From File > From Text/CSV too.

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


Similar Readings


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.

Steps:

  • After opening Excel, first, go to the File tab.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • Then go to Options.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • 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.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • 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).

CSV to Excel File Conversion with ‘Legacy Wizard’

  • Consequently, the Import Text File dialog appears, choose the CSV file you want to get in excel, and press Import.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • After that, the Text Import Wizard shows up. Here, click on the Delimited, put a checkmark on My data has headers, and press Next.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • Put a checkmark on Comma and again press Next.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • Press Finish after that.

CSV to Excel File Conversion with ‘Legacy Wizard’

  • 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).

Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)


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.

Excel VBA to Transform CSV to Excel with Columns

Now I will convert the CSV file into Excel with columns. Let’s follow the below process to do the task.

Steps:

  • Open Excel, and go to Developer > Visual Basic to bring the VBA window.

Excel VBA to Transform CSV to Excel with Columns

  • When the VBA window appears, go to Insert > Module.

Excel VBA to Transform CSV to Excel with Columns

  • 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

Excel VBA to Transform CSV to Excel with Columns

  • 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.


Conclusion

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.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo