Merge Several Csv Files Into One

 admin
Learning has never been so easy!

Type the following command and hit ENTER to merge files. Copy.csv merge.csv. The result will be the newly created merge.csv file with merged data across all CSV files within the directory. Simply replace.csv with.txt to merge text files instead of CSV files. In this article (and video) I will show you how to quickly & easily combine and merge multiple CSV files into one CSV file for free on a Mac. Follow along, and start combining! If you have multiple CSV or Excel files that you need combined, follow this simple process to get them combined in a flash, using the built in “terminal” on your Mac.

Music: Twenty One Pilots – Stressed Out (Instrumental)(Courtesy of Fx Beatz).No copyright infringement intended.Another quick tutorial video here!This vi. Oct 18, 2020 Merge multiple Excel files into one sheet. Step 1: You put all Excel or CSV files into a folder like this. Remember that the directory cannot contain any other file types. Step 2: Open the Excel software and click in this order. Step 3: You click the Browse. And select the folder containing the. How to Combine Multiple CSV Files Into One Browse to the folder with the CSV files. Hold down Shift, then right-click the folder and choose Copy as path. Open the Windows Command prompt. Type cd, press Space, right-click and select Paste, then press Enter. Type copy.csv combined-csv-files.csv.

I regularly make use of the export-csv command in Powershell and in particular when using Exchange Management Shell. Sometimes I will want to run 10+ different commands against a client resulting in 10 csv files. I couldn't see a simple way to quickly turn those csv files into sheets of a single workbook so I came up with a solution for personal use. Why not share?

This approach will allow you to select a number of CSV files. It will then import each file into your currently-open excel workbook and name each sheet in accordance with the filename of the csv.

3 Steps total

Step 1: Create a new macro

Open a new Excel sheet and navigate to 'View' and then 'Macros'. You can create a new macro by pressing 'record Macro' or by clicking 'View Macros', entering a name and clicking 'Create'. Once the macro exists, click 'Edit' under the same 'View Macros' page.

Step 2: Replace the content of the macro with the following code

Sub MergeCSVs()
Dim intChoice As Integer
Dim strPath As String
Dim i As Integer

'allow the user to select multiple files and restrict view to csv by default
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
'CSV Files Only', '*.csv')
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
'All Files', '*.*')
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True

'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show

Csv files download

'determine what choice the user made
If intChoice <> 0 Then
'get the file paths selected by the user
For i = 1 To Application.FileDialog(msoFileDialogOpen _
).SelectedItems.Count
strPath = Application.FileDialog(msoFileDialogOpen _
).SelectedItems(i)

'obtain the filename without the folder path
strFileName = Right(strPath, Len(strPath) - InStrRev(strPath, '))

'create new sheet with the correct name
newsheetname = Left(strFileName, Len(strFileName) - 4)

'import data from csv
Sheets.Add.Name = newsheetname
With ActiveSheet.QueryTables.Add(Connection:= _
'TEXT;' & strPath _
, Destination:=Range('$A$1'))
.Name = newsheetname
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i

End If
End Sub

Merge Several Csv Files Into One

Step 3: Run the Macro

Now you can run the macro whenever you want to import multiple CSV files. Just select the ones you want and they'll be added as sheets, named according to the filename.

Perhaps there's already a tool out there or an option built into Excel but I couldn't find it. Hopefully this is helpful. I personally have it saved to my PERSONAL.XLSB file so that it's always available (guide for that in references).

Note that the vast majority of items under 'ActiveSheet.QueryTables.Add' probably don't need to be there. This is just how my 'record macro' formatted it.

References

  • Copy your macros to a Personal Macro Workbook

10 Comments

  • Tabasco
    ArnieRimmer May 22, 2017 at 08:55pm

    If all the files are identical in format and structure, I just create a new folder and copy them all into the new folder.

    Issue the command “copy *.csv merge.txt”

    This creates one merged file containing all data from the csv files. Then you can import it into Excel or whatever you need to do.

    I do this routinely with output logs from electrical (Hi-Pot) testers.

    I had to laugh at myself at going through this long convoluted process to merge them all and then just thought 'dumb ass' as I remembered the CLI merge via copy function.

  • Ghost Chili
    Edwin_Eekelaers May 22, 2017 at 09:22pm

    Isn't it way easier to do it in Powershell. A bit of Import-csv & then write it to an excell sheet? Don't like too many macro's these days.

  • Mace
    bbigford May 23, 2017 at 12:11am

    Nice write up. Though, I do prefer the PowerShell route as Edwin had already pointed out.

  • Tabasco
    richardhall7 May 23, 2017 at 11:23am

    Or just run my powershell...
    Which combines all CSV's in a folder, and imports into a single sheet in Excel.
    https://community.spiceworks.com/scripts/show/3962-powershell-bulk-csv-to-excel-import

  • Poblano
    madphilosopher May 23, 2017 at 09:25pm

    Ten word minimum? The answer is a single word: cat

  • Cayenne
    Mooney May 25, 2017 at 12:37pm

    Yeah, if it's suitable to have the output in a single SHEET then powershell (or a simple 'copy c:tempexports*.csv c:tempexportcombined.csv') will do the trick. That was never really suitable for my needs as I need them to be in separate sheets. Right now, for example, I'm migrating 4 clients away from a shared mail server. I need a record of a lot of details (public folders, permisisons, statistics, mailboxes, groups, etc.) - one excel book per client works best for me.

  • Poblano
    Richard.JH May 26, 2017 at 09:08am

    This can be done so easily in CSVFix https://bitbucket.org/neilb/csvfix - I highly recommend for anyone manually merging, splitting and modifying csv documents... Combine CSVFix with Winautomation for best results..

  • Pimiento
    andrewlauer Jun 21, 2018 at 12:14pm

    Created an account just to say thanks for posting! I don't usually find solutions to my problems with such simple instructions and that work the first time. This is great!

  • Pimiento
    spicehead-90cns May 30, 2020 at 04:25am

    Thanks a ton! Tbh, I have made an account for posting this out.

  • Pimiento
    spicehead-mv4cq Mar 3, 2021 at 05:07pm

    The code appears to be malformed when i paste it in. has anyone redone this?

Learning has never been so easy!

I regularly make use of the export-csv command in Powershell and in particular when using Exchange Management Shell. Sometimes I will want to run 10+ different commands against a client resulting in 10 csv files. I couldn't see a simple way to quickly turn those csv files into sheets of a single workbook so I came up with a solution for personal use. Why not share?

This approach will allow you to select a number of CSV files. It will then import each file into your currently-open excel workbook and name each sheet in accordance with the filename of the csv.

3 Steps total

Step 1: Create a new macro

Merge Multiple Csv Files Into One Powershell

Csv

Open a new Excel sheet and navigate to 'View' and then 'Macros'. You can create a new macro by pressing 'record Macro' or by clicking 'View Macros', entering a name and clicking 'Create'. Once the macro exists, click 'Edit' under the same 'View Macros' page.

Merge csv files in excel

Step 2: Replace the content of the macro with the following code

Merge multiple csv files into one excel workbook

Sub MergeCSVs()
Dim intChoice As Integer
Dim strPath As String
Dim i As Integer

'allow the user to select multiple files and restrict view to csv by default
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
'CSV Files Only', '*.csv')
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
'All Files', '*.*')
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = True

'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show

'determine what choice the user made
If intChoice <> 0 Then
'get the file paths selected by the user
For i = 1 To Application.FileDialog(msoFileDialogOpen _
).SelectedItems.Count
strPath = Application.FileDialog(msoFileDialogOpen _
).SelectedItems(i)

'obtain the filename without the folder path
strFileName = Right(strPath, Len(strPath) - InStrRev(strPath, '))

'create new sheet with the correct name
newsheetname = Left(strFileName, Len(strFileName) - 4)

'import data from csv
Sheets.Add.Name = newsheetname
With ActiveSheet.QueryTables.Add(Connection:= _
'TEXT;' & strPath _
, Destination:=Range('$A$1'))
.Name = newsheetname
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i

End If
End Sub

Step 3: Run the Macro

Now you can run the macro whenever you want to import multiple CSV files. Just select the ones you want and they'll be added as sheets, named according to the filename.

Perhaps there's already a tool out there or an option built into Excel but I couldn't find it. Hopefully this is helpful. I personally have it saved to my PERSONAL.XLSB file so that it's always available (guide for that in references).

Note that the vast majority of items under 'ActiveSheet.QueryTables.Add' probably don't need to be there. This is just how my 'record macro' formatted it.

Merge Multiple Csv Files Into One Mac

References

  • Copy your macros to a Personal Macro Workbook

10 Comments

  • Tabasco
    ArnieRimmer May 22, 2017 at 08:55pm

    If all the files are identical in format and structure, I just create a new folder and copy them all into the new folder.

    Issue the command “copy *.csv merge.txt”

    This creates one merged file containing all data from the csv files. Then you can import it into Excel or whatever you need to do.

    I do this routinely with output logs from electrical (Hi-Pot) testers.

    I had to laugh at myself at going through this long convoluted process to merge them all and then just thought 'dumb ass' as I remembered the CLI merge via copy function.

  • Ghost Chili
    Edwin_Eekelaers May 22, 2017 at 09:22pm

    Isn't it way easier to do it in Powershell. A bit of Import-csv & then write it to an excell sheet? Don't like too many macro's these days.

  • Mace
    bbigford May 23, 2017 at 12:11am

    Nice write up. Though, I do prefer the PowerShell route as Edwin had already pointed out.

  • Tabasco
    richardhall7 May 23, 2017 at 11:23am

    Or just run my powershell...
    Which combines all CSV's in a folder, and imports into a single sheet in Excel.
    https://community.spiceworks.com/scripts/show/3962-powershell-bulk-csv-to-excel-import

  • Poblano
    madphilosopher May 23, 2017 at 09:25pm

    Ten word minimum? The answer is a single word: cat

  • Cayenne
    Mooney May 25, 2017 at 12:37pm

    Yeah, if it's suitable to have the output in a single SHEET then powershell (or a simple 'copy c:tempexports*.csv c:tempexportcombined.csv') will do the trick. That was never really suitable for my needs as I need them to be in separate sheets. Right now, for example, I'm migrating 4 clients away from a shared mail server. I need a record of a lot of details (public folders, permisisons, statistics, mailboxes, groups, etc.) - one excel book per client works best for me.

  • Poblano
    Richard.JH May 26, 2017 at 09:08am

    This can be done so easily in CSVFix https://bitbucket.org/neilb/csvfix - I highly recommend for anyone manually merging, splitting and modifying csv documents... Combine CSVFix with Winautomation for best results..

  • Pimiento
    andrewlauer Jun 21, 2018 at 12:14pm

    Created an account just to say thanks for posting! I don't usually find solutions to my problems with such simple instructions and that work the first time. This is great!

  • Pimiento
    spicehead-90cns May 30, 2020 at 04:25am

    Thanks a ton! Tbh, I have made an account for posting this out.

  • Pimiento
    spicehead-mv4cq Mar 3, 2021 at 05:07pm

    The code appears to be malformed when i paste it in. has anyone redone this?