Comma-separated values (CSV) files are the raw forms of data files users tend to save as. It’s common among users to Excel import CSV into an existing sheet to work with it. Importing or opening a CSV file is quite basic using Excel features such as Open, From Text/CSV (Data tab), Get Data (Data tab), Legacy Wizard (Data tab) as well as VBA Macro.
Let’s say we have a CSV file that looks like the below image when we open it in Notepad (Basic software to create, view, or edit text or CSV files). For some reason, we want to import this CSV file into an Excel worksheet.
In this article, we demonstrate Excel features as well as VBA Macro to enable Excel to import CSV into an existing sheet.
Download Excel Workbook
The CSV file we demonstrate Importing with.
Practice Excel File with Methods’ outcome.
5 Easy Ways to Import CSV into Existing Sheet in Excel
First, make it clear that if we open CSV files using Open with > Excel, it’ll open in Excel. However, opening CSV files in Excel won’t convert them into xlsx files.
🔄 In case, we want to view the content of any CSV file, we can Right-click on the CSV file > Select Open with (from Options) > Click on Notepad.
🔄 The Notepad window opens up and the CSV file contents get displayed as shown in the below image.
Follow the latter section to Import CSV into an existing Excel worksheet.
Method 1: Import CSV into Existing Sheet by Browsing from Folder Directory
Excel offers Open Menu in File > Open. We can use the Open feature to import CSV files.
Step 1: Open an existing Excel file or blank Excel worksheet. Then Go to File > Open > Browse.
Step 2: Choose the search file type as Text Files (right side of the File name box). Then browse through the device directory and select the respective CSV file. Click on Open.
Step 3: In a moment, Excel imports the CSV file into an existing worksheet. Furnish the data and you get a similar outcome as depicted in the below picture.
Read More: How to Convert CSV to XLSX (4 Quick Methods)
Method 2: Import CSV Using From Text/CSV Feature
As we mentioned earlier Excel provides multiple features to import different file types. There is a From Text/CSV feature within the Get & Transform Data section in the Data tab.
Step 1: Hover to the Data tab > Select From Text/CSV (from the Get & Transform Data section).
Step 2: Browse through files and select the required CSV file from the file directory. Notice that Excel automatically displays Text or CSV files as you direct it to import only Text or CSV files. Click on Import.
Step 3: A preview of the CSV data appears. You see Excel automatically distinguishes delimiters. Click Load > Load To.
Each time we use the Get Data feature (in this case expect Legacy Wizard) to import CSV files, Excel displays a preview of the about to load data.
Step 4: The Load To option fetches an Import Data dialog box. In the Import Data dialog box, you choose where you want to put your imported data. Mark the Existing worksheet option then click OK.
🔄 Excel takes a moment afterward to load the CSV data in an existing worksheet as shown in the following image.
You can modify the loaded data. Import Data dialog box offers different views (i.e., Table, Pivot Table Report, and Pivot Table). You can choose any of the options to shape your data into.
Read More: Excel VBA to Import CSV File without Opening (3 Suitable Examples)
Method 3: Import CSV Using Get Data Feature
Also, there is an embedded From Text/CSV option within the Get Data feature. We can repeat the Steps of Method 2 to import CSV files.
Step 1: Move to the Data tab > Click on Get Data > From File > From Text/CSV.
Step 2: Excel takes you to the device directory. Select the respective file then Click on Import.
Step 3: Repeat Steps 3 and 4 then load the data in an existing Excel worksheet. Modify the data according to your requirements.
Similar Readings
- How to Convert CSV to XLSX without Opening (5 Easy Methods)
- Excel VBA: Read Text File into String (4 Effective Cases)
- How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)
- Open CSV File with Columns in Excel (3 Easy Ways)
Method 4: Using Legacy Wizard to Import CSV into Existing Sheet
Excel provides the Legacy Wizard options in its Options > Data. Enabling the From Text (Legacy) option adds the Legacy Wizard option in the Get Data feature.
Step 1: In an existing Excel worksheet click on File > Options.
Step 2: The Excel Options window appears. From the window, Select Data > Tick From Text (Legacy) (under Show legacy data import wizards). Click on OK.
Step 3: Now, go to Data > Click on Get Data > Choose the Legacy Wizard option (that was previously unavailable in the options) > Click on From Text (Legacy).
Step 4: Import the desired CSV file from the directory.
Step 5: Excel displays Text Import Wizard (Steps 1 of 3) as depicted in the following image.
🔼 Mark Delimiter under Choose the file type that best describes your data.
🔼 Tick My data has headers (in case you have data headers or designations)
🔼 Click on Next.
Step 6: In Step 2 of 3 Text Import Wizard, Tick Comma as Delimiter. Click on Next.
Step 7: Mark General as the Column data format in the Import Text Wizard Step 3 of 3. Click on Finish.
Step 8: Excel brings up an Import Data dialog box. Mark Existing worksheet as want to be put into data destination. Click on OK.
Step 9: After a while, Excel loads the data. Modify the data according to your requirements. The outcome is the same as shown in the picture below.
Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)
Method 5: VBA Macro to Import CSV into Existing Sheet
Also, VBA Macro can import CSV files from device folders. A couple of lines of Macro code using Aplication.GetOpenFilename statement directs Excel to select and import CSV files from the device directory.
Step 1: Press ALT+F11 or go to the Developer tab > Visual Basic (in the Code section) to open Microsoft Visual Basic window. In the Visual Basic window, Select Insert > Click on Module.
Step 2: Paste the below macro in the Module.
Sub ImportCSVFile()
Dim wrkSheet As Worksheet, mrfFile As String
Set wrkSheet = ActiveWorkbook.Sheets("VBA")
mrfFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
With wrkSheet.QueryTables.Add(Connection:="TEXT;" & mrfFile, Destination:=wrkSheet.Range("B2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub
In the Macro Application.GetOpenFilename statement opens the device directory to select CSV files from. The macro sets the connection as the Text destination to B2. Also, the macro directs to load the comma-delimited file declaring the file type as True.
🔄 The CSV file gets loaded in the given worksheet (i.e., VBA). The macro uses commas as the separation delimiter to distribute the data into columns.
Read More: Excel VBA: Import Comma Delimited Text File (2 Cases)
Conclusion
We demonstrate Open, From Text/CSV (Data tab), Get Data (Data tab), Legacy Wizard (Data tab) features as well as VBA Macro to Excel import CSV into an existing sheet. Hope these above-described methods excel in your instance. Comment, if you have further inquiries or have anything to add.