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.csv”. Each excel file is the output of another script I use to scan Local Admin User Group (from list of servers) and look for changes. Each file is laid out with the following headings “Computer Name, Administrators, and Status”. What I am looking for the script to do is scan the particular file directory with the list of excel file (using the following naming convention “result_MM-DD_YYYY.csv “ ) for the past 7-10 days (based on the date indicated in the file name). Then compile the data on to the new spreadsheet called, “CompileResults_MM-DD-YYYY.csv”, using date from each source file “result_MM-DD_YYYY.csv “ in the new spreadsheet (example below) to indicate when the person was added or removed from the Local Administrators Group for that server. Thank you for your help
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..
Mike
ASKER
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.csv file is an example of what I looking to do. Just simply collect all the data for the past 7 days or the past 7 reports. Referencing the Date in the file name (i.e. result_MM-DD-YYYY.csv) and then plug in the date on the first row for all those users whom has been removed or added that day. (please reference the attached file named “result_MM-DD-YYYY.csv”) Then added on all the other data with date for each file…..
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
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.....
Open in new window
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..