A CSV file is where the data is separated by commas. It runs faster than an Excel file. Sometimes, you need to import CSV data into Excel file for transforming the data or to analyze it. In such a case, you need not convert CSV to an Excel file, you can just import it. In this article, we’ll show you 4 handy approaches to importing CSV data into Excel.
The above overview image shows the process to import CSV data into Excel through the Text Import Wizard.
Download Practice Workbook
Download the following workbook to practice by yourself.
What Is CSV Data?
CSV stands for “Comma-Separated Values”. CSV is mainly one type of text file used for storing data. We also use this file to exchange data between different software programs as they are simple, lightweight, and easy to read and write. In this file type, data is separated by commas (default). It is quite user-friendly. We can open CSV files in Excel and edit them.
Even though the name of the format is comma-separated values, you can separate the data by other delimiters such as semicolons, or other punctuation according to your wish. It is user-friendly in the case where a user can’t edit data in the Excel file, but can easily edit in the CSV file. However, CSV files can be opened with Notepad.
4 Handy Approaches to Import CSV Data into Excel
In this above image, we present you CSV data opened in an Excel worksheet. The data contains information on some employees.
1. Import CSV Data by Opening It in Excel
The easiest way to import CSV data into Excel is by opening it in Excel. To open the CSV file, first, go to the File tab.
Next, click Open > Browse. A dialog box will pop out. There select Text Files > Employee Info (your desired CSV file) > Open.
Hence, the CSV data will appear in a new Excel workbook.
- The data will appear in the default Excel data format settings when opening the CSV file in Excel.
- The CSV file will remain in the original .csv format even after opening it in Excel. The file won’t get changed to .xlsx or .xls.
- The limit for the files is 1,048,576 rows and 16,384 columns.
- You can also double-click the desired CSV file in Windows Explorer to open it in Excel. In such a case, Microsoft Excel needs to be set as the default application for CSV files. Otherwise, you must choose Excel as another app for opening the file through the Context Menu.
2. Use Excel Power Query Editor to Import CSV Data
To import CSV files as an external data connection, go to Data > From Text/CSV.
In the Import Data dialog box, browse to the desired CSV file location, select the file and press Import.
In the above image, we can press Load if we don’t want to change anything. However, click Transform Data to make some changes here.
The Power Query Editor will appear. There, in the Home tab, click Split Column > By Delimiter.
A new dialog box will pop out. There, select Comma as the delimiter from the dropdown list. Choose Each occurrence of the delimiter and press OK.
Next, select Use First Row as Headers and click Close & Load.
Finally, you can see the imported CSV data in the above image.
- The imported CSV data in Excel is linked to the original CSV file. So refresh the Excel worksheet to update values after making any changes in the original file
- You can permanently disconnect the link by converting the table into a normal range. To do that, right-click on any cell in the table and the Context Menu will appear. Choose Table > Convert to Range.
3. Import CSV Data into Excel Through Text Import Wizard
To import CSV files through Text Import Wizard, go to Data > Get Data > Legacy Wizards > From Text (Legacy).
The Import Text File dialog box appears. There choose the desired CSV file and press Import.
The Text Import Wizard dialog box will pop out. In step 1, choose Delimited, and check the My data has headers box. Press Next.
In step 2, click the Comma as the delimiter and press Next.
In step 3, choose General data format and click Finish.
Consequently, another dialog box appears. Choose the location where you want to place the imported data. Press OK next.
Hence, you will get to see the imported data.
- In case you don’t see the Legacy Wizards in the Get Data drop-down, go to Excel Options through the File tab > Options. There, select Data and enable From Text (Legacy).
- The limit for importing is 1,048,576 rows and 16,384 columns.
- Choose Treat consecutive delimiters as one in step 2 of the Text Import Wizard to avoid getting empty cells when consecutive delimiters are present in the data.
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
- How to Convert CSV to XLSX Command Line (with Easy Steps)
- Excel VBA: Read a Text File Line by Line (6 Related Examples)
- How to Sort CSV File in Excel (2 Quick Methods)
- How to Merge CSV Files in Excel (2 Easy Ways)
4. Apply Excel VBA for Importing CSV Data
We can also apply VBA code to import CSV data into Excel. First, go to Developer > Visual Basic.
In the pop-out VBA window, select Insert > Module. The Module box will appear. Copy and paste the following code in the box. Press Run afterward.
Sub ImportingCSVFile() Dim wkSheet As Worksheet, rfFile As String Set wkSheet = ActiveWorkbook.Sheets("VBA") rfFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:") With wkSheet.QueryTables.Add(Connection:="TEXT;" & rfFile, Destination:=wkSheet.Range("B2")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With End Sub
Another dialog box appears. Choose the desired CSV file and click Open.
This will import the CSV data into Excel.
How to Save CSV File in Excel
To save any Excel file as a CSV file, go to the File tab and click Save As.
There on the right side, you’ll find different file options in the drop-down list. Choose CSV UTF-8 or just CSV (Comma delimited) and Save it.
How to Import Multiple CSV Files in Excel
You can also open more than one CSV file in Excel. To do that,
Go to the File tab > Click Open > Browse. In the pop-out Open dialog box choose the CSV files and press Open.
How to Import Data into Excel from Another Excel File
To import data from another Excel file, go to the Data tab > Get Data > From File > From Excel Workbook.
The Import Data dialog box will appear. Choose the desired Excel file and press Import. Hence, the Excel file data appears in the active workbook.
Therefore you can import the CSV file by directly opening it in Excel. Or you can import using an external data connection. In this article, we have elaborately presented the possible ways to import CSV data into Excel. Choose any method you prefer and meet your requirements. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!
- How to Open CSV File with Columns in Excel (3 Easy Ways)
- How to Open CSV File in Excel with Columns Automatically (3 Methods)
- Excel VBA: Read Text File into String (4 Effective Cases)
- How to Convert CSV to XLSX without Opening (5 Easy Methods)
- How to Read CSV File in Excel (4 Fastest Ways)
- [Solved:] Excel Is Opening CSV Files in One Column (3 Solutions)