Issue
There are certain occasions when you have multiple CSV files that you want to consolidate into a single Excel workbook. I had two of those occasions occur at the same time. The first one happened when I was performing a health check for Exchange and exporting Exchange settings into multiple CSV files. The second occasion transpired when performing eDiscovery exports from SharePoint Online. On both occasions I was left with multiple CSV files. Along with consolidating the CSV files, I wanted to only import certain fields to a new Excel workbook.
The script below also works with .log files if you want to copy logging files from the Exchange server and analyze data directly from log files.
Credit for Code
To find a solution to my problem, I used scripts that others had created, modified their scripts, and consolidated all the content into a solution that resolved my problem. To give credit where credit is due, I used pieces of code from the following locations.
- http://www.powershellmagazine.com/2013/06/28/pstip-using-the-system-windows-forms-folderbrowserdialog-class/
- https://codex.wordpress.org/Custom_Headers
- http://marc.info/?l=perl-win32-users&m=93873787106711&w=2
Solution
Once I worked out all the kinks in the code, the solution was simple.
The first step was to determine the path location where you want to store the script and directory. The default location is under c:\temp\Exchange. You can update the location by changing lines 5, 8, and 10.
The Get-Folder function provides a GUI experience when selecting the folder that stores the CSV files.
Once the location is selected, all files with the file type of .csv are located and put into an array. If you want to find a different file type (such as .log), change line 33.
Lines 36 – 39 remove files stored in filtered and unfiltered folders. This is done every time the script is executed to clear out the filter and unfiltered folders. Any CSV file that is discovered is copied to the unfiltered folder. The filtered down version of the CSV file is stored to the filtered folder.
Lines 56 – 65 loops through each CSV file while generating the full path to each file. An unfiltered version of the CSV file is saved to the unfiltered folder.
The last set of code is where you are going to make the most of your edits. Lines 70, 76, and 82 represent the name of the CSV file.
Lines 71, 77, and 83 are the headers from each CSV file that you want to return to the master spreadsheet.
Lines 72, 78, and 84 save a copy of the edited CSV file to the filtered folder.
The last line of code (line 90) changes the name of the report to today’s date.
The second script (the CSV.ps1 script) is used to edit the excel workbook. The only line of code that needs to be edited in the CSV.ps1 file is line 14.
Results
I have copied several log files from my Exchange 2013 server and saved them to an Export folder. Each CSV file contains several fields, however, I only care about a couple of fields within each CSV file.
Using the EWS spreadsheet, there is a lot of information contained therein.
Within the EWS spreadsheet, I only want to see information stored in the AuthenticationType, IsAuthenticated, Organization, and DateTime fields. I have updated the Excel-Report script to only return those fields.
Once I execute the Excel-Report script, several actions are performed.
A new report file is created
Unedited CSV files are saved to the unfiltered folder
Edited CSV files are saved to the filtered folder
The Report workbook contains all CSV files with only fields entered in the script.
Excel-Report Script
#$Csv = $Content | ConvertFrom-Csv | Select-Object -Property * -ExcludeProperty "Title", "Title1"
#$Csv | Export-Csv C:\Temp\Outfile.csv -NoTypeInformation
$path = "C:\temp\eDiscovery"
$Var = "\"
$timer = get-date -Format M.d.yy-HH.mm
$Name = "Report.xlsx"
$Filtered = "C:\temp\eDiscovery\Filtered\"
$Unfiltered = "C:\temp\eDiscovery\Unfiltered\"
#Create workbook
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add()
$workbook.SaveAs("C:\temp\eDiscovery\$name")
$excel.Quit()
Function Get-Folder($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$foldername = New-Object System.Windows.Forms.FolderBrowserDialog
$foldername.rootfolder = "MyComputer"
if($foldername.ShowDialog() -eq "OK")
{
$folder += $foldername.SelectedPath
}
return $folder
}
$FolderPath = Get-Folder
$CSV = Get-ChildItem $FolderPath -Filter *.csv -Recurse
#Remove old report
cd "$Filtered"
remove-item *.csv
cd "$Unfiltered"
remove-item *.csv
function unique-header ($header)
{
if ($headers -contains $header)
{
$header = """$($header -replace '"')$i"""
unique-header $header
}
Else
{
$Global:headers += $header
$header
}
}
foreach ($CSVs in $CSV){
$Path = $csvs | select directory | ft -HideTableHeaders |Out-String
$File = $CSVs | select name | ft -HideTableHeaders | Out-string
$CSVPath = $Path.Trim()
$CSVFile = $file.trim()
$fullPath = "$CSVPath$Var$CSVFile"
$data = Get-Content $fullPath
Copy-item $fullPath -Destination "C:\temp\eDiscovery\UnFiltered\$CSVFile"
$data = ($data -replace ',,,,,')
$data = ($data -replace ', ')
$Data = ($data -replace 'i:0#.f|membership|')
$data
$data[0] = ($data[0] -split "," | % -begin {$headers = @(); $i=0 } -process { unique-header $_ }) -join ","
#If ($CSVFile -eq "Microfeed.csv") {
#$data | ConvertFrom-Csv | select “Content Type ID”, "Title", "MicroBlogType", "PostAuthor", "DefinitionId", "RootPostID", "RootPostOwnerID", "RootPostUniqueID", "ReplyCount", "Attributes", <#Content,#> "ContentData", <#SearchContent,#> "RefRoot", "RefReply", "PeopleCount", "eMailSubscribers", "ID", "Modified", "Created", “Created By”, “Modified By”, "owshiddenversion", “Workflow Version”, “UI Version”, "Version", "Attachments", “Approval Status”, "Order", “Path”, “Item Type”, “Sort Type”, "Name", “Unique Id”, "ScopeId", "Level", “Is Current Version”, “Item Child Count”, “Folder Child Count”, “Last Modified Date” | export-csv "$Filtered$CSVFile" -NoTypeInformation
#C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
# }
If ($CSVFile -like '*calendar*') {
$data | ConvertFrom-Csv | select "Title","Modified","Modified By","Created","Created By","Attachments","Path","URL Path","File Type","File Name","Start Time","End Time","Description","Event Cancelled","Attendees","TimeZone","Item Type","Last Modified Date","Type" | export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Custom*') {
$data | ConvertFrom-Csv | select "Title", "Person or Group", *"custom number column"*, *"custom text column"*, "Created By","Created","Modified By","Modified","Attachments","File Name","File Type","Item Type","Order","Type","Path","URL Path","Last Modified Date","Is Current Version" |export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*tasks*') {
$data | ConvertFrom-Csv | select "Order","Task Name","Created By","Created","Modified By","Modified","Attachments","Title","URL Path","Path","Item Type","Assigned To","Due Date","Start Date","Completed","Description","File Name","File Type","Parent ID","Last Modified Date","Predecessors","Previously Assigned To","Priority","Related Items","Task Status","Type"|export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Discussion*') {
$data | ConvertFrom-Csv | select "ID", "Level", "Name", "Order", "Parent Folder Id", "Parent Item Editor", "Parent Item ID", "Replies", "Title", "Body", "Created", "Created By", "Discussion Subject", "Discussion Title", "Subject", "Modified", "Modified By", "Last Modified Date", "Last Reply By", "Last Updated", "Path", "Question", "Is Answered", "Is Current Version", "Is Featured Discussion", "Approval Status", "Approver Comments", "Attachments", "Folder Child Count","Best Response Id", "Body Was Expanded", "Client Id", "Compliance Flags", "Compliance Tag", "Compliance Tag Modified Time", "Content Type", "Content Type ID", "Copy Source", "Correct Body To Show", "Edit", "Edit Menu Table End", "Edit Menu Table Start", "Effective Permissions Mask", "E-Mail Messages", "E-Mail Sender", "Encoded Absolute URL", "File Name", "File Type", "Full Body", "GUID", "Has Copy Destinations", "HTML File Type", "ID of the User who has the Set the Compliance Tag", "Indentation", "Indentation Level", "Instance ID", "Is Root Post", "Item Type", "Less Link", "Limited Body", "Message ID", "More Link", "NoExecute", "Originator Id", "owshiddenversion", "Post", "Posted By", "Posting Information", "ProgId", "Property Bag", "Quoted Text Was Expanded", "References", "Reply", "Restricted", "ScopeId", "Select", "Server Relative URL", "Shortest Thread-Index", "Shortest Thread-Index Id", "Shortest Thread-Index Id Lookup", "Sort Type", "Thread Index", "Thread Topic", "Threading", "Threading Controls", "Toggle Quoted Text", "Total File Count", "Total File Stream Size", "Total Size", "Trimmed Body", "Type", "UI Version", "Unique Id", "URL Path", "Version", "Workflow Instance ID", "Workflow Version","App Created By", "App Modified By" | export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CsvFile -like '*Announce*') {
$data | ConvertFrom-Csv | select "ID", "Level", "Item Child Count", "Order", "Body", "Title", "Created", "Created By", "Last Modified Date", "Modified", "Modified By", "Name", "NoExecute", "Is Current Version", "Item Type", "Path", "App Created By", "App Modified By", "Approval Status", "Approver Comments", "Attachments", "Client Id", "Compliance Flags", "Compliance Tag", "Compliance Tag Modified Time", "Content Type", "Content Type ID", "Copy Source", "Edit", "Edit Menu Table End", "Edit Menu Table Start", "Effective Permissions Mask", "Encoded Absolute URL", "Expires", "File Name", "File Type", "Folder Child Count", "GUID", "Has Copy Destinations", "HTML File Type", "ID of the User who has the Set the Compliance Tag", "Instance ID", "Originator Id", "owshiddenversion", "ProgId", "Property Bag", "Restricted", "ScopeId", "Select", "Server Relative URL", "Sort Type", "Total File Count", "Total File Stream Size", "Total Size", "Type", "UI Version", "Unique Id", "URL Path", "Version", "Workflow Instance ID", "Workflow Version" | export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Categories*') {
$data | ConvertFrom-Csv | select "ID", "Order", "Level", "Name", "Title", "Created By","Created","Modified By","Modified", "Last Modified Date", "Attachments", "Approval Status", "Approver Comments", "URL Path", "Child Item", "Child Folder" |export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Promoted*') {
$data | ConvertFrom-Csv | select "ID", "Order", "Level", "Name", "Title", "Created By", "Created", "Modified By", "Modified", "Link Location", "Last Modified Date", "Path", "Launch Behavior", "Background Image Cluster Vertical Start", "Background Image Location", "Is Current Version", "Item Child Count", "Item Type" |export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Survey*') {
$data | ConvertFrom-Csv | select "ID", "Name", "Order", "Title", "Created By", "Created", "Modified By", "Modified", "Path", "Folder Child Count", "Item Child Count", "Item Type", "Is Current Version", "App Created By", "App Modified By", "Approval Status", "Client Id", "Completed", "Compliance Flags", "Compliance Tag", "Compliance Tag Modified Time", "Content Type", "Content Type ID", "Copy Source", "Edit Menu Table End", "Edit Menu Table Start", "Effective Permissions Mask", "Encoded Absolute URL", "File Name", "File Type", "Has Copy Destinations", "HTML File Type", "ID of the User who has the Set the Compliance Tag", "Instance ID", "Last Modified Date", "Level", "NoExecute", "Originator Id", "owshiddenversion", "ProgId", "Property Bag", "Restricted", "ScopeId", "Server Relative URL", "Sort Type", "Total File Count", "Total File Stream Size", "Total Size", "Type", "UI Version", "Unique Id", "URL Path" |export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*Issue*') {
$data | ConvertFrom-Csv | select "ID", "Level", "Order", "Name", "title", "Comments", "Description", "Assigned To", "Category", "Created By", "Created", "Modified By", "Modified", "Last Modified Date", "Due Date", "Current", "Folder Child Count", "Is Current Version", "Path", "Priority", "Add Related Issue", "App Created By", "App Modified By", "Approval Status", "Approver Comments", "Attachments", "Client Id", "Compliance Flags", "Compliance Tag", "Compliance Tag Modified Time", "Content Type", "Content Type ID", "Copy Source", "Edit", "Edit Menu Table End", "Edit Menu Table Start", "Effective Permissions Mask", "Encoded Absolute URL", "File Name", "File Type", "GUID", "Has Copy Destinations", "HTML File Type", "ID of the User who has the Set the Compliance Tag", "Instance ID", "Issue ID", "Issue Status", "Item Child Count", "Item Type", "NoExecute", "Originator Id", "owshiddenversion", "ProgId", "Property Bag", "Related ID", "Related Issues", "Remove Related ID", "Restricted", "ScopeId", "Select", "Server Relative URL", "Sort Type", "Total File Count", "Total File Stream Size", "Total Size", "Type", "UI Version", "Unique Id", "URL Path", "Version" |export-csv "$Filtered$CSVFile" -NoTypeInformation
C:\temp\eDiscovery\CSV.ps1 -Path "$Filtered$CSVFile"
}
ElseIf ($CSVFile -like '*') {
Copy-item $CSVFile -Destination "C:\temp\eDiscovery\"
}
<# If ($CSVFile -like '*blog*') { $data | ConvertFrom-Csv | select "Created By", "Modified By", Modified, Created, "URL Path", Subject, Attachments, "Approval Status",Path, Name, "Is Current Version", Replies, "Folder Child Count", "Last Modified Date", Body, "Last Updated", "Discussion Title", "Parent Item Editor", "Parent Item ID", "Last Reply By", Question, "Is Answered", "Is Featured Discussion" | export-csv C:\temp\eDiscovery\$CSVFile -NoTypeInformation cd C:\temp\eDiscovery C:\temp\eDiscovery\CSV.ps1 -Path C:\temp\eDiscovery\$CSVFile } If ($CSVFile -like '*list*') { $data | ConvertFrom-Csv | select ID, “Content Type ID”, Title, Modified, Created, “Created By”, owshiddenversion, “Workflow Version”, “UI Version UI Version”, Version, Attachments, “Approval Status”, Order, GUID, “URL Path”, Path, “Item Type”, “Sort Type:", Name, “Unique Id”, ScopeId, “Is Current Version”, “Item Child Count”, “Folder Child Count”, NoExecute, "$Resources:core,Content_Version;", "Last Modified Date" | Export-Csv C:\Temp\Outfile.csv -NoTypeInformation cd C:\temp\eDiscovery C:\Temp\eDiscovery\CSV.ps1 -Path C:\Temp\eDiscovery\$CSVFilec } #>
}
Rename-Item "C:\temp\eDiscovery\$Name" -NewName "$timer$Name"
CSV Script
param (
[ValidatePattern('\.csv$')]
[ValidateScript({
Test-Path $_
})]
[string[]]$Path
)
$FullPath = $Path | ForEach-Object {
(Resolve-Path $_).ProviderPath
}
$Report = "C:\temp\Exchange\Report.xlsx"
$Rest = $FullPath
write-host $rest
$Excel = New-Object -ComObject Excel.Application
$Excel.DisplayAlerts = $FALSE
$Book = $Excel.Workbooks.Open($Report)
foreach ($Item in $Rest) {
$Next = $Excel.Workbooks.Open($Item)
$Next.ActiveSheet.Move($Book.ActiveSheet)
}
$Excel.Visible = $False
$Book.Save()
$Book.Close()