8 Easy Ways To Convert CSV to Excel

In this Excel tutorial, I’ll explain how to convert CSV to Excel using various methods in step-by-step.

CSV files are a widely used format for data interchange because they are simple, human-readable, and can be easily imported and exported by various data analytics tools including Excel. CSV files are lightweight and easy to maintain for large tabular datasets.

Therefore, most business apps that collect, store, and analyze datasets use CSV files. Many open-source data repositories also export databases in CSV rather than XLSX or XLS.

Hence, you must learn the skill to convert CSV to Excel so you don’t need to stop your data analysis and visualization work when the input file is CSV and not XLSX.

Reasons to Convert CSV to Excel

Here are the factors that make the skill to convert CSV to Excel important:

  • If you plan to use the database for data analytics regularly, you should convert it to XLSX from CSV.
  • Manipulating data in a CSV file is a challenging process. Instead, you can efficiently analyze data in Excel using formulas, tables, charts, and so on.
  • CSV is primarily used to store large databases easily and affordably. It’s not suitable for data analysis.
  • You can create multiple worksheets in a single Excel workbook for better data organization and segmentation. A CSV file format doesn’t offer this feature.
  • You can set up data validation rules in an Excel worksheet, like policies and restrictions for data entry. This feature is also not available in a CSV file.
  • You can’t sort and filter data points in a CSV file. But, you can perform extensive and complex sorting and filtering in Excel.
  • Excel supports collaborative work with features such as comments, allowing you and your colleagues to contribute to and discuss the content of the spreadsheet.

Convert CSV to Excel Using the Save As Dialog

Open CSV in Excel
Open CSV in Excel
  1. Save the CSV file in a directory on your PC.
  2. Right-click and hover the cursor over Open with.
  3. When the overflow context menu shows up, click Excel.
Go to File tab
Go to the File tab
  1. Excel should instantly open the CSV file in a worksheet.
  2. Click on the File tab.
Convert CSV to Excel using Save As
Convert CSV to Excel using Save As
  1. Click Save As and choose Browse.
  2. Click the Save as type drop-down on the Save As dialog and choose the XLSX file format.
  3. Click Save to complete the process.

That’s it! You’ve changed the CSV file into an Excel file.

Convert CSV to Excel Using Get Data

If you’re already in an Excel workbook, you can use the Get Data ribbon command to import a CSV file into your worksheet. Then, you can save it as an XLSX or XLS file.

Get Data
Get Data

Navigate to Data > Get & Transform Data and click on the Get Data button. Hover the mouse cursor over From File and click on the From Text/CSV option.

Import data
Import data

Use the Import Data dialog to open a CSV file from your PC.

Data import wizard
Data import wizard

You should now see the data import wizard of Excel. The wizard’s preview will show how Excel shall import the data of the CSV file to your worksheet. If the columns and rows look okay, click Load. This would import the content of the CSV file to the current worksheet.

If you want to load the data of the CSV file to another worksheet, click the Load drop-down arrow and click on the Load To option.

New worksheet
New worksheet

Select the New worksheet option on the Import data dialog to open the content of the CSV file in a new worksheet instead of the current worksheet.

Convert CSV to Excel Using Google Sheets

Google Sheets Open menu
Google Sheets Open menu
  1. Open a new Google Sheets workbook and click the File menu button.
  2. Click Open on the context menu.
Upload a CSV file to Sheets
Upload a CSV file to Sheets
  1. Go to the Upload tab on the Open a file dialog.
  2. Click Browse to access the Open dialog.
  3. Use this dialog box to upload a CSV file from your PC.
  4. Google Sheets will process the CSV data and add its content to your Google Sheets worksheet.
Download as XLSX
Download as XLSX
  1. Now, again click the File menu and hover the cursor over the Download menu.
  2. Choose Microsoft Excel from the overflow menu.
  3. Google Sheets shall convert the CSV to XLSX and start downloading it on your PC’s Downloads folder.
  4. Now, you can simply double-click the downloaded file to open it directly in your Excel desktop app.

Convert CSV to Excel Using Wtools.Io

If you prefer to use online software and are willing to upload your CSV file on a third-party server, you can try Wtools.Io’s Convert CSV to Excel web app.

Convert CSV to Excel
Convert CSV to Excel
  1. Visit the Convert CSV to Excel portal.
  2. Click the Browse button on the right side above the preview panel.
  3. Use the Open dialog to import the CSV file into the tool.
  4. Below the CSV configuration section, enter the character used as a delimiter in the CSV Separator field.
  5. Click Convert & Download.
  6. You can open the downloaded XLSX file in your Excel app.

Convert CSV to Excel Using an Excel Add-In

You can use the CSV Import+Export Excel add-in by Emurasoft to convert CSV to Excel. The add-in is a freeware tool available on the Office Add-ins store.

Installing the Add-In

Add-ins store
Add-ins store

Navigate to the Developer tab on the Excel app and click on the Add-ins command to open the Office Add-ins dialog box. Click on the Store tab to access the Excel add-ins store.

CSV Import+Export Excel
CSV Import+Export Excel

Search for CSV in the Search field and click on the Add button to the right of the CSV Import+Export Excel add-in. Click on the Continue button to complete installing the add-in.

Using the Add-In

Upload CSV to add-in
Upload CSV to add-in

Now, go to the Insert tab and click on the Import CSV command inside the CSV commands block. Click Choose File to select the target CSV file from your computer.

Import csv button
Import CSV button

Next, you can customize several parameters of the CSV file you’re importing. For example, you can edit Encoding, Delimiter, and Newline Sequence. Click Import CSV again to complete the CSV import process.

CSV Import+Export Excel add-in shall copy and paste the CSV file’s content into a worksheet of the open Excel workbook. Follow the Save As Dialog method mentioned earlier to save the CSV file as an Excel file (XLS or XLSX).

Convert CSV to Excel Using Power Pivot

An Excel worksheet can contain up to 1,048,576 rows of data. If the CSV you’re converting to Excel contains more than 1,048,576 rows of data, you need to use a different tool like Power Pivot.

Power Pivot shall allow you to load a large CSV file of more than 1 million rows of data. Then, you can perform complex calculations, aggregations, etc. You might also want to do data clean-up and transformation before the analysis or reduce the data rows to 1,048,576.

Opening Power Pivot editor
Opening Power Pivot editor

To try out Power Pivot, go to the Data tab and click on the Manage Data Model command inside the Data Tools commands block. A blank Power Pivot editor will open.

From other sources
From other sources

On the Power Pivot for Excel tool, click on the Get External Data button. There you’ll see a context menu where you need to click on the From Other Sources option.

Text File
Text File

You should see the Table Import Wizard. Scroll down to the Text Files section, select the Text File option, and click Next.

Connect to Flat File
Connect to Flat File

On the Connect to Flat File dialog, click the Browse button to add your CSV file to the Table Import Wizard. If the column delimiter is anything other than a comma, click on the Comma Separator drop-down arrow and choose an appropriate column separator from the list. You can now click Finish on the same dialog to complete the CSV import process.

CSV file data in Power Pivot
CSV file data in Power Pivot

After you close the Table Import Wizard, Excel shall load the dataset of the CSV file to the Power Pivot editor. From there, follow the Save As method to convert the imported CSV file to an XLSX or XLS file.

Convert CSV to Excel Using Excel VBA

You can automate the process of importing a CSV file to an Excel workbook as a worksheet and saving the workbook as an XLSX file using Excel VBA.

Find below the script you can use:

A script to convert CSV to Excel
A script to convert CSV to Excel
Sub ImportAndSplitCSV()

    ' Step 1: Show an input box to import a CSV file from the local directory
    Dim csvFilePath As String
    csvFilePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File")

    If csvFilePath = "False" Then
        MsgBox "Operation canceled. No file selected.", vbExclamation
        Exit Sub
    End If

    ' Step 2: Check if the CSV contains more than 1,048,576 rows of data
    Dim rowCount As Long
    rowCount = WorksheetFunction.CountA(Workbooks.Open(csvFilePath).Worksheets(1).Columns(1))

    If rowCount > 1048576 Then
        ' Step 3: Split the CSV file into two or more worksheets
        Dim wb As Workbook
        Set wb = Workbooks.Open(csvFilePath)
        Dim ws As Worksheet
        Set ws = wb.Sheets(1)

        Dim lastRow As Long
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

        ' Calculate the number of worksheets needed
        Dim numSheetsNeeded As Integer
        numSheetsNeeded = Application.WorksheetFunction.Ceiling(lastRow / 1048576, 1)

        ' Create additional worksheets if needed
        If numSheetsNeeded > 1 Then
            Dim i As Integer
            For i = 2 To numSheetsNeeded
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Data_" & i
            Next i

            ' Copy data to the new worksheets
            Dim startRow As Long
            Dim endRow As Long
            startRow = 1

            For i = 1 To numSheetsNeeded
                endRow = startRow + 1048575
                If endRow > lastRow Then endRow = lastRow
                ws.Rows(startRow & ":" & endRow).Copy Sheets("Data_" & i).Range("A1")
                startRow = endRow + 1
            Next i

            ' Delete the original sheet
            Application.DisplayAlerts = False
            ws.Delete
            Application.DisplayAlerts = True
        End If

        ' Close the workbook without saving changes
        wb.Close SaveChanges:=False
    End If

    ' Step 4: Show an input box to save the imported data as XLSX in a local drive
    Dim savePath As String
    savePath = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")

    If savePath = "False" Then
        MsgBox "Operation canceled. No file selected for saving.", vbExclamation
        Exit Sub
    End If

    ' Save the workbook as XLSX
    ThisWorkbook.SaveAs savePath, FileFormat:=xlOpenXMLWorkbook
    MsgBox "Data successfully imported and saved.", vbInformation

End Sub

The above script will do the following automatically when you execute the VBA macro:

Select CSV dialog
Select CSV dialog
  • Show a dialog box to import a CSV file from the local directories of your PC
  • Display a warning message if the imported CSV contains more than 1,048,576 rows of data, the row limit of an Excel worksheet
  • If the CSV indeed contains more than 1,048,576 rows of data, the VBA script will split the data into two or more worksheets
Save As XLSX
Save As XLSX
  • Finally, the macro will show a dialog box so you can save the CSV as an XLSX file on the PC

To create a VBA macro using the above script, read this article How To Use The VBA Code You Find Online.

Convert CSV to Excel Using Windows PowerShell

Suppose you often use Windows PowerShell to automate repetitive tasks, write scripts for scheduled execution on a PC remotely, or manage Windows Server environments. If you ever need to convert CSV to Excel when doing automatic tasks using PowerShell, you must practice the following method.

Is your PowerShell tool set up for scripting? If you don’t know, do the following to allow scripting:

Enabling scripting
Enabling scripting
  1. Open PowerShell with admin rights.
  2. Copy and paste the following script into the PowerShell terminal and hit Enter:
Set-ExecutionPolicy RemoteSigned
  1. Press Y for Yes.

Then, you must import and install the ImportExcel Module in your PowerShell terminal. Do the following:

Install Nuget
Install Nuget
  1. Copy and paste this script into the PowerShell console and hit Enter:
Install-Module -Name ImportExcel -Force -SkipPublisherCheck
  1. Press Y to get NuGet provider if not installed previously and then run the above script again.
  1. Now, copy and paste the following script to a Notepad file:
# Import the ImportExcel module
Import-Module ImportExcel

# Specify the path to your CSV file
$csvFilePath = "D:\testcsv.csv"

# Specify the path for the output Excel file
$excelFilePath = "d:\csvtoexcel.xlsx"

# Import the CSV file
$data = Import-Csv -Path $csvFilePath

# Export the data to an Excel file
$data | Export-Excel -Path $excelFilePath -AutoSize -BoldTopRow
  1. Enter your CSV file’s location here: "D:\testcsv.csv".
  2. Set a name for the output file and its path here: "d:\csvtoexcel.xlsx".
Converting CSV to Excel in PowerShell
Converting CSV to Excel in PowerShell
  1. Copy and paste the edited script into the PowerShell console and hit Enter.

You’ve successfully converted the target CSV file to Excel. Go to the output directory to check if the converted Excel file is working or not.

Conclusions

If you ever face the challenge of converting a CSV file to Excel, you can overcome that by following any of the above methods.

You can practice methods like Save As, Get Data, and From Text/CSV to convert CSV to Excel if you’ve recently started using Excel.

If you’ve got intermediate experience in Excel and you can go online, try using the methods based on Google Sheets, Wtools.Io, and an Excel add-in.

Finally, if you’re an experienced Excel user with basic knowledge of scripting, you should practice methods, like Power Pivot, PowerShell, and VBA.

Comment below if you liked the methods explained above. Also, mention any methods that you know work better than the above.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃