Merge Several Csv Files Into One

To merge several CSV files into one, select Multiple files as your data source. Click + Select File Locate the first file from inside your cloud storage folder and click Done. Here I’ve selected the first CSV file: Florida February, from my Google Drive. 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. CSV is a data format that can be used to store transcripts, orders and other data. Sometimes you need to merge multiple CSV files statistically. Doing so manually is very tedious. This tool can perform fast, bulk merging of multiple CSV files and produce an output as a CSV file.

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

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

'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.

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?

Csv files examples

Note

This example can be found in the source distribution inexamples/merge_multiple_files directory. Referenced CSV files areincluded there as well.

Problem Definition¶

We have multiple CSV files, for example with grant listing, from varioussources and from various years. The files have couple common columns, such asgrant receiver, grant amount, however they might contain more additionalinformation.

Files we have:

  • grants_2008.csv contains receiver, amount, date
  • grants_2009.csv contains id, receiver, amount, contract_number,date
  • grants_2919.csv contains receiver, subject, requested_amount,amount, date

Merge Several Csv Files Into One

Objective¶

Create one CSV file by sequentially merging all input CSV files and using allcolumns.

Based on our source files we want output csv with fields:

  • receiver
  • amount
  • date
  • id
  • contract_number
  • subject
  • requested_amount

In addition, we would like to know where the record comes from, therefore weadd file which will contain original filename.

Solution¶

Import brewery and all other necessary packages:

Merge Multiple Csv Files Into One Pandas

Specify sources:

It is highly recommended to explicitly name fileds contained within sourcefiles and do not rely on source headers. In this case we also make sure, thatthe field (column) names are normalised. That means that if in one filereceiver is labeled just as “receiver” and in another it is “grant receiver”we will get the same field.

You can store sources in an external file, for example as json or yaml andread it in your script.

Now collect all the fields:

Prepare the output stream into merged.csv and specify fields we have foundin sources and want to write into output:

Go through all sources and merge them:

Now you have a sparse CSV files which contains all rows from source CSV filesin one merged.csv.

You can “pretty print” it with:

And you can see the completeness aspect of data quality with simple audit:

Variations¶

You can have a directory with YAML files (one per record/row) as outputinstead of one CSV just by changing data stream target. Seebrewery.ds.YamlDirectoryDataTarget for more information.

Directory merged_grants must exist before running the script.

Or directly into a SQL database. The following will initialize SQL tabletarget stream which will remove all existing records from the table beforeinserting. Note that the table grants must exist in database opendataand must contain columns with names equal to fields specified inall_fields. See brewery.ds.SQLDataTarget for more information.

Merge Multiple Csv Files Into One Excel Workbook

Refer to source streams and source targets in the API documentation for moreinformation about possibilities.

See also

Merge Csv Files Into One

Module brewery.ds
List of varous data sources and data targets.
Function brewery.ds.fieldlist()
All streams use list of brewery.ds.Field objects for field metadata. This function willconvert list of strings into list of instances of Field class.