Import multiple csv file to single worksheet by vba excel – debugvba.com

Import multiple csv to one worksheet excel file vba

Objective

To import multiple csv file to single worksheet by vba excel.

Approach

In this VBA Excel automation, we shall import data from multiple csv file and save them in a single excel file. Data from all the csv files will be saved in single worksheet.We can see the folder structure below to understand how this code works.

Steps

  1. Initialize the path of input and output folder in the 2 variables
  2. Add a new workbook, all the data from multiple csv files will be imported in this workbook
  3. Add the column header for 2 columns in cell A1 and B1
  4. Loop through each csv file in source data folder
  5. Copy the range from opened csv file, we have taken the range A2:B10 for demonstration purpose
  6. Paste the copied data in last blank row in a newly added workbook
  7. Close the input csv file
  8. Move to next csv file in input folder
  9. Once all the csv files are imported, save and close the workbook

 

Code

Option Explicit


Sub ImportMultipleCsvFiles()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim InputCsvFile As Variant
Dim InputFolder As String, OutputFolder As String
Dim wb As Workbook
Dim LastRow As Integer

InputFolder = "C:\Users\username\Desktop\input_Data"
OutputFolder = "C:\Users\username\Desktop\Output_Data"

'Add consolidated workbook, data from text files will be pasted in it
Set wb = Workbooks.Add
Range("A1").Value = "Country"
Range("B1").Value = "ISD_Code"


'Loop through each text file in source folder
InputCsvFile = Dir(InputFolder & "\*.csv")
While InputCsvFile <> ""
Workbooks.OpenText Filename:=InputFolder & "\" & InputCsvFile, DataType:=xlDelimited, Comma:=True


'Save each output file in output folder
Range("A2:B10").Select
Selection.Copy

'Paste it to newly added workbook
wb.Activate
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LastRow + 1).Select
ActiveSheet.Paste

'Close the opened input file
Workbooks(InputCsvFile).Close
InputCsvFile = Dir
Wend

'Save the consolidated sheet
wb.Sheets("sheet1").Range("A1").Select
wb.SaveAs Filename:=OutputFolder & "\" & "Consolidated_File", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
wb.Close


End Sub

 

We have used 2 folders, one for output files (output_data) and another one for input files (input_data).

Import multiple csv to separate excel file vba

Folder input_data has 2 input csv files, [test_1.csv and test_2.csv]. Both the csv files have 4 records.

Import multiple csv to separate excel file vba

When we execute the VBA code, 1 consolidated output excel file will be created in output_data folder.

 

Import multiple csv to one worksheet excel file vba

 

A consolidated output excel file will be created which will have total 8 records; as it imported 4 records from each of the input text files.

 

Import multiple csv to one worksheet excel file vba

 

 

Notes

  1. We can make the code more dynamic by selecting the source and destination by dialog box
  2. To import comma delimited data; we use “comma:=True”

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.opentext
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function

Post you may like

Import multiple csv file to separate worksheet by vba excel