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

Welcome Forums General PowerShell Q&A Skip after an empty row in csv/ignore last n rows after a blank row in csv

Viewing 4 reply threads
  • Author
    Posts
    • #245382
      Participant
      Topics: 1
      Replies: 0
      Points: -7
      Rank: Member

      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.

    • #245817
      Participant
      Topics: 7
      Replies: 2458
      Points: 6,439
      Helping Hand
      Rank: Community MVP

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

      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. 😉

      … but I’m looking for a simplistic one line solution for skipping the bottom few rows as well.

      Why does it have to be a one line solution?

      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.

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

    • #245874
      Participant
      Topics: 8
      Replies: 568
      Points: 2,171
      Helping Hand
      Rank: Community Hero

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

      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.

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

    • #245964
      Participant
      Topics: 15
      Replies: 1776
      Points: 3,218
      Helping Hand
      Rank: Community Hero

      First of all – empty lines are not allowed in CSV files.

      Not sure that is entirely accurate, a basic example:

      Output:

      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:

      Output:

    • #246126
      Participant
      Topics: 7
      Replies: 2458
      Points: 6,439
      Helping Hand
      Rank: Community MVP

      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? 😉

Viewing 4 reply threads
  • You must be logged in to reply to this topic.