1

I have a directory on a server called 'servername'. In that directory, I have subdirectories whose name is a date. In those date directories, I have about 150 .csv file audit logs. I have a partially working script that starts from inside the date directory, enumerates and loops through the .csv's and searches for a string in a column. Im trying to get it to export the row for each match then go on to the next file.

$files = Get-ChildItem '\\servername\volume\dir1\audit\serverbeingaudited\20180525'

ForEach ($file in $files) {
    $Result = If (import-csv $file.FullName | Where {$_.'path/from' -like "*01May18.xlsx*"})
    {
    $result | Export-CSV -Path c:\temp\output.csv -Append}
    }

What I am doing is searching the 'path\from' column for a string - like a file name. The column contains data that is always some form of \folder\folder\folder\filename.xls. I am searching for a specific filename and for all instances of that file name in that column in that file.

My issue is getting that row exported - export.csv is always empty. Id also like to start a directory 'up' and go through each date directory, parse, export, then go on to the next directory and files.

If I break it down to just one file and get it out of the IF it seems to give me a result so I think im getting something wrong in the IF or For-each but apparently thats above my paygrade - cant figure it out....

Thanks in advance for any assistance, RichardX

Improve this question

3 Answers 3

Reset to default
1

The issue is your If block, when you say $Result = If () {$Result | ...} you are saying that the new $Result is equal what's returned from the if statement. Since $Result hasn't been defined yet, this is $Result = If () {$null | ...} which is why you are getting a blank line.

The If block isn't even needed. you filter your csv with Where-Object already, just keep passing those objects down the pipeline to the export.

Since it sounds like you are just running this against all the child folders of the parent, sounds like you could just use the -Recurse parameter of Get-ChildItem

Get-ChildItem '\\servername\volume\dir1\audit\serverbeingaudited\' -Recurse |
    ForEach-Object {
        Import-csv $_.FullName |
            Where-Object {$_.'path/from' -like "*01May18.xlsx*"} 
    } | Export-CSV -Path c:\temp\output.csv

(I used a ForEach-Object loop rather than foreach just demonstrate objects being passed down the pipeline in another way)

Edit: Removed append per Bill_Stewart's suggestion. Will write out all entries for the the recursed folders in the run. Will overwrite on next run.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

It's not necessary to use Export-Csv -Append within the loop (in fact, it can be rather inefficient, depending on how many times it executes). Just pipe the output to Export-Csv at the end (see my answer).
@Bill_Stewart It's a good suggestion, with the caveat that each new run through will then overwrite the file (which is often the desired behavior rather than have duplicate entries). Not sure what the expected behavior is. The file write could also be more efficient by doing it at the end, with the cost of that being if the script fails part way through the previous operations wouldn't be recorded. Many choices with pros/cons.
To prevent appending, move your Export-Csv to the very end of the pipeline (after the ForEach-Object scriptblock, not within it).
@Bill_Stewart That's what I was trying to say with The file write could also be more efficient by doing it at the end, with the cost of that being if the script fails part way through the previous operations wouldn't be recorded. Just hadn't gotten around to updating that yet.
Yes, that's what I was trying to say - now your answer is identical to mine (with the exception of -Recurse and -NoTypeInformation).
1

I don't see a need for appending the CSV file? How about:

Get-ChildItem '\\servername\volume\dir1\audit\serverbeingaudited\20180525' | ForEach-Object {
  Import-Csv $_.FullName | Where-Object { $_.'path/from' -like '*01May18.xlsx*' }
} | Export-Csv 'C:\Temp\Output.csv' -NoTypeInformation
Improve this answer

5 Comments

Thanks Bill - The 'append' is because I wanted each match of the search string from the 100 or so files its going to parse to all be in one file. One thing I didnt account for though, is that the time stamp in these logs are only partial - the log file is named something like audit-15-19-28 and when you open the log file the time stamp is just 19:28.xx so by appending each match I cant figure out what the actual times are. I have an idea how to fix it but we will see. Ill open another question for that if needed.
This will create only one file.
My understanding of export-csv is that it would replace the contents of the file each run through, which would be undesireable. I wanted one file with each match added to the end of the file.
Only if it's within the loop. Note that my code exports only at the end of the pipeline. (Try it.)
Right - didn't notice the ending script block in the code window. Trying it this way now. Stay Tuned...
0

Assuming your CSVs are in the same format and that your search text is not likely to be present in any other columns you could use a Select-String instead of Import-Csv. So instead of converting string to object and back to string again, you can just process as strings. You would need to add an additional line to fake the header row, something like this:

$files = Get-ChildItem '\\servername\volume\dir1\audit\serverbeingaudited\20180525'

$result = @()
$result += Get-Content $files[0] -TotalCount 1 

$result += ($files | Select-String -Pattern '01May18\.xlsx').Line 
$result | Out-File 'c:\temp\output.csv'
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.