Skip after an empty row in csv/ignore last n rows after a blank row in csv

PowerShell Help
1

Hi there,

 

Powershell newbie here. Working on an automation task where I’m only allowed to use powershell since it’s a tightly controlled microsoft environment. And even in that I’m limited to using Powershell 4. I’m aware that the answer is quite simple in powershell5 but I don’t have that luxury.

I have a csv file with top 5 and bottom 5 rows that aren’t desired since they are not part of the data. My code is -

Import-Csv C:\Data\XFC.csv -Header "Date", "Received", "Answered", "Answer time", "Transferred", "Agency", "Answered (S)", "Answer time (S)", "Disconnected", "Total call time"| select-object -Skip 5 -last| Export-csv C:\Data\XFC_processed.csv -NoTypeInformation

I get the following error -

Select-Object : Missing an argument for parameter 'Last'. Specify a parameter of type 'System.Int32' and try again.

I understand the error but I just can’t figure out the correct syntax from microsoft documentation.

I know how to skip the first 5 rows but I’m looking for a simplistic one line solution for skipping the bottom few rows as well. There is a blank row before the bottom 5 rows (which show the summary stats for the dataset) but the actual dataset itself doesn’t have any blank rows as the data is autogenerated data from call logs. So another solution to this could be ignoring everything that comes after that blank row.

Appreciate your help. Thank you.

2

First of all - empty lines are not allowed in CSV files. You might remove them in advance to get reasonable results. If you import the data from a CSV file (maybe remove empty lines) and save it in a variable you can determine the amount of objects with the method “.count”. Now you can calculate the amount of objects you need for your task. :wink:

Why does it have to be a one line solution?

As mentioned above you might prepare the data you work with in advance to get reasonable and predictable results.

3

The error is telling you to provide an INT to the parameter -Last such as -Last 5. Yours is cut off

-Last |

I agree with Olaf that if you capture the import you will have the count property to easily grab the desired results. However, assuming you’re not getting an import error due to the blank lines, this slow, ugly one liner should achieve the result.

Import-Csv C:\Data\XFC.csv -Header "Date", "Received", "Answered", "Answer time", "Transferred", "Agency", "Answered (S)", "Answer time (S)", "Disconnected", "Total call time"| select-object -Skip 5 | sort-object -descending | select-object -Skip 5 | Sort-Object -Descending | Export-csv C:\Data\XFC_processed.csv -NoTypeInformation

I would definitely recommend cleaning up the formatting for readability though.

$headers = @(
    "Date",
    "Received",
    "Answered",
    "Answer time",
    "Transferred",
    "Agency",
    "Answered (S)",
    "Answer time (S)",
    "Disconnected",
    "Total call time"
)
Import-Csv C:\Data\XFC.csv -Header $headers | 
    select-object -Skip 5 | sort-object -descending |
        select-object -Skip 5 | Sort-Object -Descending | Export-csv C:\Data\XFC_processed.csv -NoTypeInformation
4
First of all – empty lines are not allowed in CSV files.

Not sure that is entirely accurate, a basic example:

$csv = @"
FirstName,LastName,Hobby
John,Smith,spelunking
Susie,Franks,bingo
,,
Chris,Columbo,sailing

"@ | ConvertFrom-Csv

$csv

$csv | Export-CSV C:\Scripts\temp.csv -NoTypeInformation

Import-Csv C:\Scripts\temp.csv

Output:

PS C:\Users\rasim> $csv

FirstName LastName Hobby     
--------- -------- -----     
John      Smith    spelunking
Susie     Franks   bingo     
                             
Chris     Columbo  sailing   

PS C:\Users\rasim> Import-Csv C:\Scripts\temp.csv

FirstName LastName Hobby     
--------- -------- -----     
John      Smith    spelunking
Susie     Franks   bingo     
                             
Chris     Columbo  sailing

I’ve generated some very non-standard csv formats for applications that had different datasets and headers all in one file. Based on this blog, you can do something like this:

[Collections.Generic.List[Object]]$csv = @"
FirstName,LastName,Hobby
John,Smith,spelunking
Susie,Franks,bingo
,,
Chris,Columbo,sailing

"@ | ConvertFrom-Csv

#Find the blank line and remove it (although the -1 could be removed assuming the entire row is blank)
#Note this is checking just first name assuming it's mandatory and the rest of the row is null
$index = $csv.FindIndex( {!$args[0].FirstName} ) -1

#Get the lines from the start to the defined index
$csv[0..$index]

Output:

FirstName LastName Hobby     
--------- -------- -----     
John      Smith    spelunking
Susie     Franks   bingo
5
Not sure that is entirely accurate, a basic example:

OK. They may be allowed in the standard but they very likely produce errors sometimes very hard to find. So I recommended to remove them in advance. Could you agree with that? :wink: