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?

In this case, to import all csv files from a folder to a single sheet, you can use the following VBA code. Enable a blank worksheet and press Alt + F11 keys to open Microsoft Visual Basic for Applications window. Import pandas as pd writer = pd.ExcelWriter ('yourfile.xlsx', engine='xlsxwriter') df = pd.readcsv ('originalfile.csv') df.toexcel (writer, sheetname='sheetname') writer.save Since you have multiple csv files, you would probably want to read all csv files and store them as a df in a dict. Then write each df to Excel with a new sheet name.

Files

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

Combine workbooks into one macro

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 Csv Files Into One Excel Workbook Python

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?

Objective

To import multiple csv file to single worksheet by vba excel.

Approach

In this VBA Excel automation, we shall import data from multiple csv file and save them in a single excel file. Data from all the csv files will be saved in single worksheet.We can see the folder structure below to understand how this code works.

Combine Csv Files Into One Excel WorkbookCombine Csv Files Into One Excel WorkbookFiles

Steps

  1. Initialize the path of input and output folder in the 2 variables
  2. Add a new workbook, all the data from multiple csv files will be imported in this workbook
  3. Add the column header for 2 columns in cell A1 and B1
  4. Loop through each csv file in source data folder
  5. Copy the range from opened csv file, we have taken the range A2:B10 for demonstration purpose
  6. Paste the copied data in last blank row in a newly added workbook
  7. Close the input csv file
  8. Move to next csv file in input folder
  9. Once all the csv files are imported, save and close the workbook

Code

We have used 2 folders, one for output files (output_data) and another one for input files (input_data).

Folder input_data has 2 input csv files, [test_1.csv and test_2.csv]. Both the csv files have 4 records.

When we execute the VBA code, 1 consolidated output excel file will be created in output_data folder.

A consolidated output excel file will be created which will have total 8 records; as it imported 4 records from each of the input text files.

Notes

  1. We can make the code more dynamic by selecting the source and destination by dialog box
  2. To import comma delimited data; we use “comma:=True”

Merge Csv Files Into One Excel Sheet

Reference

How To Combine Excel Workbooks

https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.opentext
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function

Combine Csv Files Into One Excel Workbook Free

Post you may like

Coments are closed

Most Viewed Posts

  • Retroarch On Rg350
  • Hbo Max Prime Video
  • Browser Tor Download Free
  • Tor Browser Iphone Download

Scroll to top