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

import multiple csv to separate worksheet vba

Objective

To Import multiple csv file to separate worksheet by vba excel

Approach

In this VBA Excel automation, we shall import data from multiple csv file and save them in a separate worksheet in single consolidated file.We can see the folder structure below to understand how this code works.

Steps

  1. Declare and 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. Loop through each csv file in source data folder
  4. Copy the range from opened csv file, we have taken the range A2:B10 for demonstration purpose
  5. Add a worksheet in newly added workbook
  6. Paste the copied data in newly added worksheet
  7. Close the input csv file
  8. Move to next csv file in input folder and repeat the same process
  9. Once all the csv files are imported, save and close the workbook

 

Code

'Option Explicit


Sub ImportMultipleCsvFile()
Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim InputCsvFile As Variant
Dim SourceDataFolder As String, OutputDataFolder As String
Dim wb As Workbook, LastRow As Integer

SourceDataFolder = "C:\Users\username\Desktop\macro\Input_Data"
OutputDataFolder = "C:\Users\username\Desktop\macro\Output_Data"

'Add consolidated workbook
Set wb = Workbooks.Add

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


'Save each output file in output folder
Range("A1:B10").Copy


'Paste it to newly added workbook
wb.Activate
Worksheets.Add
ActiveSheet.Name = Replace(InputCsvFile, ".csv", "")
ActiveSheet.Paste
Range("A1").Select

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

'Save the consolidated sheet
wb.SaveAs Filename:=OutputDataFolder & "\" & "Consolidated_File", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

wb.Close


End Sub

 

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

import multiple csv to separate worksheet 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 worksheet vba

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

import multiple csv to separate worksheet vba

Above shown consolidated output excel file will have 2 tabs, each tab will have data from source csv file.

 

import multiple csv to separate worksheet 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

Create InputBox in VBA Excel