Retrieve single row of data from multiple .dat files and compile

Hello, I’ve scoured the internet for an answer but can’t get there.

I have 30+ data files in one folder with 000’s of rows of data. Using Powershell ISE - I need to extract line ten from each file and compile these into one csv file. If possible, I’d also like to append the source filename to the start of each row.

I can achieve this with one file but not multiple. I believe I need to use a combination of Get-Content/ForEach etc but just cant seem to get the correct mix.

Can anyone help?
MP

MP,

Welcome to the forum. :wave:t4:

Please share what you have so far. I’m pretty sure we can help you further. :wink:

Hi Olaf, thank you.

To complete this on one file - I do the following, which works fine.

Get-Content "C:\Users\pitters\Folder\*.dat" | Select-Object -Skip 9 -First 1 > Output.xls

I’ve found code that can loop through files looking for strings but I need a specific line of data.

MP

MP,

there are some information missing. How do you select the 30+ data files? With …

Get-Content "C:\Users\pitters\Folder\*.dat" 

… you actually get the content of all *.dat files in this folder.

To retreive some files in PowerShell we use

You can pipe this to a loop and process each single file with

Inside this loop you can use the code you already have. You just have to replace the filename with full path of the current pipeline object $_.FullName
Now you can pipe this to a CSV file with

Please read the help for all these cmdlets completely including the examples to learn how to use them.

And BT: When you post code or error messages please use the preformatted text button ( </> ). Place the cursor on an empty line, click the button and paste your code.

Thanks in advance

1 Like

If your files are big, getting all of the content is very inefficient. You can use -TotalCount and array index notation to grab just the 9th line:

(Get-Content .\myMassiveFile.txt -TotalCount 9)[-1]
1 Like

Thanks, I appreciate you’re coaching me to learn, however I’m a complete beginner and this is a one-off piece of work.

So far I have:

Get-ChildItem -Path C:\Users\mpitman\Folder\* -Include *.dat
    ForEach-Object {

Following this, I’m ready to enter my previous code, less the “> Output.xls” i.e.

Get-Content "C:\Users\pitters\Folder\*.dat" | Select-Object -Skip 9 -First 1

The bit about

You just have to replace the filename with full path of the current pipeline object $_.FullName

I’m a little lost here…

Finally, I’m ready to export:

Export-Csv -Path C:\Users\pitters\Folder\output.csv
}

Any closer?
MP

# Get line 10 of each file then append filename to start of line
Get-ChildItem -Path C:\Users\mpitman\Folder -Filter *.dat | ForEach-Object {
    $line = (Get-Content -Path $_.FullName -TotalCount 10)[-1]
    $line -replace '^',"$($_.Name) "
} | Add-Content -Path C:\Users\pitters\Folder\output.csv
1 Like

Many thanks - that’s close enough for what I need . VMA