Merge Functions‎ > ‎

CSVs to 1 Sheet

Part 1 - Imported Data is Stacked


PROBLEM:
"I have multiple CSV files in the same folder.  How can i merge all the CSV files into a single sheet?
Even better would be an added column so the source CSV file name is included in the final data"

SPECIFICATIONS:
  1. CSVs in same folder, any filename will do or can be specifically filtered
  2. Source CSV name added to imported data in column A
  3. The two key parts you may need to edit are marked in red

CODE

Option Explicit

Sub ImportCSVsWithReference()
'Author:    Jerry Beaucaire
'Date:      10/16/2010
'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in column A listing the CSV filenames

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath   As String:      fPath = "C:\2010\Import\"    'path to CSV files, include the final \
Dim fCSV    As String

If MsgBox("Clear the existing MasterCSV sheet before importing?", vbYesNo, "Clear?") _
    = vbYes Then wsMstr.UsedRange.Clear

Application.ScreenUpdating = False  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert col A and add CSV name
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
      'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Range("A" & Rows.Count).End(xlUp).Offset(1)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
    Loop
 
Application.ScreenUpdating = True
End Sub



Part 2 - Imported Data is side by side imported into adjacent columns

PROBLEM:
"How can i merge all the CSV files in a folder into a single sheet? The data should go in adjacent columns, it's usually the same few columns.
Even better would be an added row at the top so the source CSV file name is included in the final data."

SPECIFICATIONS:
  1. CSVs in same folder, any filename will do or can be specifically filtered
  2. Source CSV name added to imported data in Row 1, data in row 2 at next available column
  3. The two key parts you may need to edit are marked in red

CODE

Option Explicit

Sub ImportCSVsWithReference()
'Author:    Jerry Beaucaire
'Date:      11/3/2011
'Summary:   Import all CSV files from a folder into a single sheet
'           adding a field in row 1 for listing the CSV filenames

Dim wbCSV   As Workbook
Dim wsMstr  As Worksheet:   Set wsMstr = ThisWorkbook.Sheets("MasterCSV")
Dim fPath   As String:      fPath = "C:\2010\Import\"    'path to CSV files, include the final \
Dim fCSV    As String
Dim NextCol As Long

If MsgBox("Clear the existing MasterCSV sheet before importing?", _
    vbYesNo, "Clear?") = vbYes Then
        wsMstr.UsedRange.Clear
        NextCol = 1
Else
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
End If

Application.ScreenUpdating = False  'speed up macro

fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    Do While Len(fCSV) > 0
      'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
      'insert row 1 and add CSV name
        Rows(1).Insert xlShiftDown
        Range("A1") = ActiveSheet.Name
      'copy date into master sheet and close source file
        ActiveSheet.UsedRange.Copy wsMstr.Cells(1, NextCol)
        wbCSV.Close False
      'ready next CSV
        fCSV = Dir
        NextCol = wsMstr.Cells(3, Columns.Count).End(xlToLeft).Column + 1
    Loop
 
Application.ScreenUpdating = True
End Sub


Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!


Comments