Import Multiple CSV Files into a Single MS Excel Spreadsheet

The following routine reads multiple (potentially hundreds) of comma-separated-value files (CSV) and writes their content to a single MS Excel worksheet.

Original reference from ExtendOffice.com

Sub ImportCSVsWithReference()
'
' This macro reads in all CSV files in a directory and 
' writes them to a single new worksheet ("Sheet1")
'
' in a new workbook (that is, the workbook in which this 
' Macro is defined).
'
' The data from the separate CSV files are column-listed in' the Sheet1.
'
' Found at this web site: https://www.extendoffice.com/documents/excel/3388-excel-import-multiple-text-csv-xml-files.html
'
' Developed in this Macro 2018-01-08
'
' John Zaleski
'
' -------------------------------------------
' What follows is directly from the web site:
'
' UpdatebyKutoolsforExcel 2015-12-14
    Dim xSht  As Worksheet
    Dim xWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Set xSht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\" & "*.csv")
    Do While xFile <> ""
        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = _
           ActiveSheet.Name
        ActiveSheet.UsedRange.Copy xSht.Range("A" _
           & Rows.Count).End(xlUp).Offset(1)
        xWb.Close False
        xFile = Dir
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Kutools for Excel"
End Sub

Author: johnrzaleski_eqbr0v

John R. Zaleski, PhD, CAP, CPHIMS, is Chief Analytics Officer of Bernoulli, a leader in real-time connected healthcare. Dr. Zaleski brings 21 years of experience in researching and ushering to market devices and products to improve healthcare. He received his PhD from the University of Pennsylvania, with a dissertation that describes a novel approach for modeling and prediction of post-operative respiratory behavior in post-surgical cardiac patients. Dr. Zaleski has a particular expertise in designing, developing, and implementing clinical and non-clinical point-of-care applications for hospital enterprises. Dr. Zaleski is the named inventor or co-inventor on seven issued patents related to medical device interoperability. He is the author of numerous peer-reviewed articles on clinical use of medical device data, information technology and medical devices and wrote three seminal books on integrating medical device data into electronic health records and the use of medical device data for clinical decision making, including the #1 best seller of HIMSS 2015 on connected medical devices.

Leave a Reply

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