September 17, 2012 at 1:24 pm
Windows Server 2008 R2
I'm totally new to PowerShell but was trying to use it to solve an issue with data files that come with the last row being a) short and b) containing the row count for the file. For example, a file with 77,571 rows with 34 columns per row ends with a row (row 77,572) that contains just the text 'Total Exported:77571' plus row end chars.
I can get PowerShell to give me the content of the last row using:
Get-Content filepath.txt | Select-Object -last 1
However, I haven't figured out how to write that to a new file... Oh, and I'd like to remove that row from the file when I'm done. I can use a script that will parse all files in a directory.
Anyone have time to help with this? Many Thanks!
September 19, 2012 at 7:19 am
Fly Girl (9/17/2012)
Windows Server 2008 R2I'm totally new to PowerShell but was trying to use it to solve an issue with data files that come with the last row being a) short and b) containing the row count for the file. For example, a file with 77,571 rows with 34 columns per row ends with a row (row 77,572) that contains just the text 'Total Exported:77571' plus row end chars.
I can get PowerShell to give me the content of the last row using:
Get-Content filepath.txt | Select-Object -last 1
However, I haven't figured out how to write that to a new file... Oh, and I'd like to remove that row from the file when I'm done. I can use a script that will parse all files in a directory.
Anyone have time to help with this? Many Thanks!
You could try something like this:
PS C:\> $rows = Get-Content C:\data.txt | Select-Object -Last 1
PS C:\> Get-Content C:\data.txt | Select-Object -First $rows[0] | Out-File -FilePath C:\data_lines.txt -Encoding ASCII
A couple things you should know:
- Get-Content reads the entire file, then filters the output. This means the code above reads the entire file line-by-line just to get the last row, then reads the entire file again to exclude the last row while piping it to a new file. That's three passes over the data, two reads and a write, not very efficient.
- You'll get an ASCII file out the other side of this process. Change the -Encoding option if your input file is in Unicode or some other encoding.
For a file with only 77K rows this may be fine but this technique may not perform well enough for you in case you try it on files with millions of rows. In that case you have options.
You could setup a foreach loop to read through the keeping two lines in memory at all times and only writing out the previous line if there is a newer line coming, i.e. write out the second to last line, but never the last line in the file. This would allow you to validate the file immediately after writing the new file with only the data lines, i.e. one read through the file.
For the most efficiency you would seek to the end of the file, seek in reverse byte-by-byte to find the beginning of the last line, store its value, then remove it without having to read through every byte in the entire file even once. C++ is very good at doing work like this. You can likely do the same with PowerShell, but chances are you'll need to resort to coding directly against some of the .NET objects that help us work with files at a low level. This would amount to not having to read the entire file even once, nor would you write the majority of the file to a new location, but it has the downside of being destructive on your incoming file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 7:40 am
Thanks, opc.3. I'm sure there are better ways of doing this but reality is usually less perfect. What I came up with is the following:
$fileEntries = [IO.Directory]::GetFiles("E:\datafiles");
foreach($fileName in $fileEntries)
{
$d = $filename.EndsWith(".txt")
IF($d)
{
$RowCount = Get-Content $FileName | Select-Object -last 1
Add-Content "E:\DataFiles\SXRowCount.txt" ($fileName + '|' + $RowCount)
}
}
It is inefficient to use Get-Content, but my files are relatively small. Apparently you used to be able to download a DOS translator for tail as part of the Windows Server toolkit but I don't find that anymore for 2008.
Despite a brief delay, I end up with a file, SXRowCount.txt that has the file name and the last lines of all the files in the target directory. With this I can verify whether I've read in the correct number of rows and/or if the file completed normally.
September 19, 2012 at 7:54 am
Happy you got something going. I didn't mention tail.exe because it's not technically a PowerShell solution, but if you're open to using an external exe here is a sourceforge project I have used in the past (not in PowerShell, but when needing a peak in large files from the command line) to get head.exe and tail.exe ports for Windows:
http://unxutils.sourceforge.net
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 24, 2012 at 10:19 am
I dont know if I understand vey well,but if you want to get the last line and output to a new file :
(Get-Content c:\Test\lines.txt )[-1] | Out-File c:\test\NewLine.txt
Then same wat if you want to get the last 2
(Get-Content c:\Test\lines.txt )[-1..-2] | Out-File c:\test\NewLine.txt
or the first 3 (it is adressing to an array and in PowerShell is zero-based, so needs to use 0 to 2)
(Get-Content c:\Test\lines.txt )[0..2] | Out-File c:\test\NewLine.txt
or we can use the -totalcount parameter in thecase of the first 3:
Get-Content c:\Test\lines.txt -totalcount 3 | Out-File c:\test\NewLine.txt
September 24, 2012 at 10:24 am
Nice, that is cleaner. I'll update the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply