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?

Merge Several Csv Files Into One

Merge multiple files into one file. Online, free, no-installation cloud file merger for merging JPG, PDF, PNG, DOC, XLS, CSV, TEXT and other format files. How to merge CSV files. Click inside the file drop area to upload first CSV file or drag & drop one CSV file. Likewise, upload second CSV file or drag & drop it. Check 'Merge into one sheet' option if merging CSV files into one sheet. Click the 'MERGE NOW' button to start merging files. Instantly download, view or send merged file. 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. Python's map (function, iterable) sends to the function (the pd.readcsv ) the iterable (our list) which is every csv element in filepaths). Panda's readcsv function reads in each CSV file as normal. Panda's concat brings all these under one df variable.

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.

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

MergeSeveral

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

Sample Csv Files

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

Merge Csv Files Into One

'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, '))

Combine Csv Files Into One

'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

Merge Multiple Csv Files Into One Online

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 Several Csv Files Into One File

References

  • Copy your macros to a Personal Macro Workbook

10 Comments

Csv Files Examples

  • 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?

Coments are closed

Most Viewed Posts

  • Powerful Sigils
  • Hart Of Dixie Amazon Prime
  • Opera Tor Browser
  • Discord Bit

Scroll to top