This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.
Course Of The Month
8 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need A Powershell Script
Posted on 2013-01-04
Hello Experts,
I am looking for simple PowerShell script to scan a network directory and pull a list of excel files (using the following naming convention “result_MM-DD_YYYY.csv “ ) and compile the data in to new workbook called “CompileResults_MM-DD-YYYY
Example of Source output Excel file called “result_MM-DD_YYYY.csv”
Computer Name Administrators Status
ComputerName1 Group1 Removed
ComputerName1 Domain Admins Removed
ComputerName1 Backup Admins Added
ComputerName1 Enterprise Admins Added
ComputerName2 Enterprise Admins Removed
ComputerName2 Group2 Removed
ComputerName2 Domain Admins Added
ComputerName2 Backup Admins Added
ComputerName2 Enterprise Admins Removed
ComputerName2 User1 Added
ComputerName3 Group2 Added
ComputerName3 Domain Admins Added
ComputerName3 Backup Admins Added
ComputerName4 Enterprise Admins Removed
ComputerName4 Group0 Added
ComputerName4 Enterprise Admins Added
Example of output file called “CompileResults_MM-DD-YYYY
Date Computer Name Administrators Status
1/1/2013 ComputerName1 Group1 Removed
1/1/2013 ComputerName1 Domain Admins Removed
1/1/2013 ComputerName1 Backup Admins Added
1/1/2013 ComputerName1 Enterprise Admins Added
1/1/2013 ComputerName2 Enterprise Admins Removed
1/1/2013 ComputerName2 Group2 Removed
1/1/2013 ComputerName2 Domain Admins Added
1/1/2013 ComputerName2 Backup Admins Added
1/1/2013 ComputerName2 Enterprise Admins Removed
1/1/2013 ComputerName2 User6 Added
1/1/2013 ComputerName3 Group2 Added
1/1/2013 ComputerName3 Domain Admins Added
1/1/2013 ComputerName3 Backup Admins Added
1/1/2013 ComputerName4 Enterprise Admins Removed
1/1/2013 ComputerName4 Group0 Added
1/1/2013 ComputerName4 Enterprise Admins Added
1/2/2013 ComputerName1 Group1 Removed
1/2/2013 ComputerName1 Domain Admins Removed
1/2/2013 ComputerName1 Backup Admins Added
1/2/2013 ComputerName1 Enterprise Admins Added
1/2/2013 ComputerName2 Enterprise Admins Removed
1/2/2013 ComputerName2 Group2 Removed
1/2/2013 ComputerName2 Domain Admins Added
1/2/2013 ComputerName2 Backup Admins Added
1/2/2013 ComputerName2 Enterprise Admins Removed
1/2/2013 ComputerName2 User1 Added
1/3/2013 ComputerName1 Group2 Added
1/3/2013 ComputerName1 Domain Admins Added
1/3/2013 ComputerName1 Backup Admins Added
1/3/2013 ComputerName2 Enterprise Admins Removed
1/3/2013 ComputerName2 Group0 Added
1/3/2013 ComputerName2 Enterprise Admins Added
result-MM-DD-YYYY.csv
CompileResults-MM-DD-YYYY.csv
I am looking for simple PowerShell script to scan a network directory and pull a list of excel files (using the following naming convention “result_MM-DD_YYYY.csv “ ) and compile the data in to new workbook called “CompileResults_MM-DD-YYYY
Example of Source output Excel file called “result_MM-DD_YYYY.csv”
Computer Name Administrators Status
ComputerName1 Group1 Removed
ComputerName1 Domain Admins Removed
ComputerName1 Backup Admins Added
ComputerName1 Enterprise Admins Added
ComputerName2 Enterprise Admins Removed
ComputerName2 Group2 Removed
ComputerName2 Domain Admins Added
ComputerName2 Backup Admins Added
ComputerName2 Enterprise Admins Removed
ComputerName2 User1 Added
ComputerName3 Group2 Added
ComputerName3 Domain Admins Added
ComputerName3 Backup Admins Added
ComputerName4 Enterprise Admins Removed
ComputerName4 Group0 Added
ComputerName4 Enterprise Admins Added
Example of output file called “CompileResults_MM-DD-YYYY
Date Computer Name Administrators Status
1/1/2013 ComputerName1 Group1 Removed
1/1/2013 ComputerName1 Domain Admins Removed
1/1/2013 ComputerName1 Backup Admins Added
1/1/2013 ComputerName1 Enterprise Admins Added
1/1/2013 ComputerName2 Enterprise Admins Removed
1/1/2013 ComputerName2 Group2 Removed
1/1/2013 ComputerName2 Domain Admins Added
1/1/2013 ComputerName2 Backup Admins Added
1/1/2013 ComputerName2 Enterprise Admins Removed
1/1/2013 ComputerName2 User6 Added
1/1/2013 ComputerName3 Group2 Added
1/1/2013 ComputerName3 Domain Admins Added
1/1/2013 ComputerName3 Backup Admins Added
1/1/2013 ComputerName4 Enterprise Admins Removed
1/1/2013 ComputerName4 Group0 Added
1/1/2013 ComputerName4 Enterprise Admins Added
1/2/2013 ComputerName1 Group1 Removed
1/2/2013 ComputerName1 Domain Admins Removed
1/2/2013 ComputerName1 Backup Admins Added
1/2/2013 ComputerName1 Enterprise Admins Added
1/2/2013 ComputerName2 Enterprise Admins Removed
1/2/2013 ComputerName2 Group2 Removed
1/2/2013 ComputerName2 Domain Admins Added
1/2/2013 ComputerName2 Backup Admins Added
1/2/2013 ComputerName2 Enterprise Admins Removed
1/2/2013 ComputerName2 User1 Added
1/3/2013 ComputerName1 Group2 Added
1/3/2013 ComputerName1 Domain Admins Added
1/3/2013 ComputerName1 Backup Admins Added
1/3/2013 ComputerName2 Enterprise Admins Removed
1/3/2013 ComputerName2 Group0 Added
1/3/2013 ComputerName2 Enterprise Admins Added
result-MM-DD-YYYY.csv
CompileResults-MM-DD-YYYY.csv
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2
7 Comments
Active 2 days ago
Try this..
If you are looking for file creation date or modified date then replace
$Date = $_.Name -Replace("^result-","") -Replace(".csv$","")
With $Date = $_.LastWriteTime or $Date = $_.CreationTime based on your requirement..
[Array]$Result = $null
GCI C:\Users\Pinky\Desktop\Test | % {
$Date = $_.Name -Replace("^result-","") -Replace(".csv$","")
Import-Csv $_.FullName | % {
$Result += New-Object Psobject -Property @{`
Date = $Date
"Computer Name" = $_."Computer Name"
Administrators = $_.Administrators
Status = $_.Status}
}
}
$Result | Export-Csv “CompileResults_$(Get-Date -f MM-dd-yyyy).csv” -NoTypeInformation
If you are looking for file creation date or modified date then replace
$Date = $_.Name -Replace("^result-","") -Replace(".csv$","")
With $Date = $_.LastWriteTime or $Date = $_.CreationTime based on your requirement..
Active 3 days ago
The result file (i.e. result_MM-DD_YYYY.csv) is a daily file created from a different script I run that uses the naming convention “result_MM-DD_YYYY.csv”. There is one excel file for each day of the week. (i.e. result_01-01-2013.csv, result_01-02-2013.csv, result-01-03-2013.csv, result_01-04-2013.csv) in file directory “C:\PS\results\
The CompileResults_MM-DD-YYYY.
P.S. My original post Indicated file name formats using Date “result_MM-DD_YYYY.csv” and should have been “result_MM-DD-YYYY.csv
I hope not been fudging up with too much info!! If I am, let me know… sorry :)
CompileResults-MM-DD-YYYY.csv
The CompileResults_MM-DD-YYYY.
P.S. My original post Indicated file name formats using Date “result_MM-DD_YYYY.csv” and should have been “result_MM-DD-YYYY.csv
I hope not been fudging up with too much info!! If I am, let me know… sorry :)
CompileResults-MM-DD-YYYY.csv
Active 3 days ago
Subsun, the script works perfect... thanks you .... I do have one additional question: when attempting to give a date range ( on the following string " $Date = $_.LastWriteTime or $Date = $_.CreationTime " how do I complete the string to look back 7 days or 30 days.. I guess that where a little confused.....
[Array]$Result = $null
GCI "V:\MikesPowershellFolder\Scan" | % {
$Date = $_.LastWriteTime or $Date = $_.CreationTime
Import-Csv $_.FullName | % {
$Result += New-Object Psobject -Property @{`
Date = $Date
"ComputerName" = $_."ComputerName"
"Administrators" = $_."Administrators"
Status = $_.Status}
}
}
$Result | Export-Csv “C:\users\amstoots\CompileResults_$(Get-Date -f MM-dd-yyyy).csv” -NoTypeInformation
Active 2 days ago
If I am not wrong you need to get .csv files created since last 7 days.. If yes..
Following command will give you last 7 days files based on modified date or created date..
Either
$Date = $_.LastWriteTime
or
$Date = $_.CreationTime
not both.. :-)
Following command will give you last 7 days files based on modified date or created date..
GCI "V:\MikesPowershellFolder\Scan" | Where {$_.LastWriteTime -gt $(Get-Date).AddDays(-7)}
or
GCI "V:\MikesPowershellFolder\Scan" | Where {$_.CreationTime -gt $(Get-Date).AddDays(-7)}
Either
$Date = $_.LastWriteTime
or
$Date = $_.CreationTime
not both.. :-)
Featured Post
In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate. Datacenter migrations happen in four phases -
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of while and for loops in Python.
while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows: while <condition>: do something repeate: The break statement m…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 23 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.