CODEWITHSUNDEEP
excelcsv
Vestyak
Vestyak

Reputation: 1

Merge CSV files using macro

Any idea how to merge *.csv files from one folder?

I have many *.csv files with same structure (count & heading of columns) and I need to merge their content into one sheet.

It's not so hard, I know. But When I add content from one table, I need to add new column with name of table where I copied this data from.

Any help please?

Thank you!

Upvotes: 0

Views: 11694

Answers (2)

Vestyak
Vestyak

Reputation: 1

This seems like a good aproach how to solve this. It merge content of all csv files and delete headers from 2nd file! :) But still need to solve that problem with adding name of source file.

Option Explicit

Sub ImportCSV()

Dim strSourcePath As String
Dim strDestPath As String
Dim strFile As String
Dim strData As String
Dim x As Variant
Dim Cnt As Long
Dim r As Long
Dim c As Long

Application.ScreenUpdating = False

'Change the path to the source folder accordingly
strSourcePath = "C:\Path\"

If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"

'Change the path to the destination folder accordingly
strDestPath = "C:\Path\"

If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"

strFile = Dir(strSourcePath & "*.csv")

Do While Len(strFile) > 0
    Cnt = Cnt + 1
    If Cnt = 1 Then
       r = 1
   Else
       r = Cells(Rows.Count, "A").End(xlUp).Row + 1
   End If
    Open strSourcePath & strFile For Input As #1
        If Cnt > 1 Then
           Line Input #1, strData
       End If
        Do Until EOF(1)
            Line Input #1, strData
            x = Split(strData, ",")
            For c = 0 To UBound(x)
                Cells(r, c + 1).Value = Trim(x(c))
            Next c
            r = r + 1
        Loop
    Close #1
    Name strSourcePath & strFile As strDestPath & strFile
    strFile = Dir
Loop

Application.ScreenUpdating = True

If Cnt = 0 Then _
    MsgBox "No CSV files were found...", vbExclamation

End Sub

Upvotes: 0

Kevin
Kevin

Reputation: 473

There are a few ways to approach it (ex: SO 39045638)

I personally use a .bat file similar to the below, where loc is the directory for the csv files. This doesn't handle deleting the Aggregate file, though. Nor does it handle repeated headers, so you would need to edit the final csv to remove them.

@ECHO OFF  
Set loc=C:\Test\
Copy %loc%*.csv %loc%\Aggregate.csv

Upvotes: 1

Related Questions