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.
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.
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.
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.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 _
strPath = Application.FileDialog(msoFileDialogOpen _
'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 _
.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
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.
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.
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.
Nice write up. Though, I do prefer the PowerShell route as Edwin had already pointed out.
Or just run my powershell...
Which combines all CSV's in a folder, and imports into a single sheet in Excel.
Ten word minimum? The answer is a single word: cat
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.
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..
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!
Thanks a ton! Tbh, I have made an account for posting this out.
The code appears to be malformed when i paste it in. has anyone redone this?
To import multiple csv file to single worksheet by vba excel.
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.
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.
Post you may like