1

I have a csv file with 46000 rows and 8 columns.

I would need the number of rows to remain the same (46k); so when adding a new row I take the first one out (oldest - date based), leaving the header so I can allocate the new data to the right columns.

I can't use For-Each or any other loop because it takes ages.

When applying (example)...

Get-Content -Path $path | 
Select-Object -Skip 1 | 
Set-Content -Path $new-path 

...it takes away the header as well.

I could of course re-import the csv assigning the header but that seems ugly. Is there any version of this code that takes away the first data-row but keeps the header standing?

2
  • As for this... "I could of course re-import the csv assigning the header but that seems ugly"... Why do you say this? It's a direct way of dealing with your use case. As for this "Is there any version of this code that takes away the first data-row but keeps the header standing?", sure, but may require more effort, so, why do that? Commented Jan 5, 2021 at 21:10
  • Quick and dirty is to just track the lines with a variable --> $count = 0; gc $path | where {$count++ -ne 1} Commented Jan 5, 2021 at 21:31

3 Answers 3

1

Extending from my comment. As otherwise, you end up doing stuff sort of like this:

Get-Content -Path 'D:\Temp\book1.csv'
# Results
<#
"Site","Dept"
"Main","aaa,bbb,ccc"
"Branch1","ddd,eee,fff"
"Branch2","ggg,hhh,iii"
#>

Get-Content -Path 'D:\Temp\book1.csv' | 
Select-Object -Skip 2
# Results
<#
"Branch1","ddd,eee,fff"
"Branch2","ggg,hhh,iii"
#>

Get-Content -Path 'D:\Temp\book1.csv' | 
Select-Object -Skip 2 | 
ConvertFrom-Csv -Header Site, Dept
# Results
<#
Site    Dept       
----    ----       
Branch1 ddd,eee,fff
Branch2 ggg,hhh,iii
#>

As for using Import-Csv, you can use .Net ArrayList and do something like this:

([System.Collections.ArrayList]$BooksList = (Import-Csv -Path 'D:\Temp\book1.csv'))
# Results
<#
Site    Dept       
----    ----       
Main    aaa,bbb,ccc
Branch1 ddd,eee,fff
Branch2 ggg,hhh,iii
#>

$BooksList.Remove($BooksList[0])
$BooksList
# Results
<#
Site    Dept       
----    ----       
Branch1 ddd,eee,fff
Branch2 ggg,hhh,iii
#>

$BooksList.Remove($BooksList[1])
$BooksList
# Results
<#
Site    Dept       
----    ----       
Branch1 ddd,eee,fff
#>

You can use the same approach to add record rows to the csv

Clear-Host
$BooksList = @()
([System.Collections.ArrayList]$BooksList = (Import-Csv -Path 'D:\Temp\book1.csv'))
# Results
<#
Site    Dept       
----    ----       
Main    aaa,bbb,ccc
Branch1 ddd,eee,fff
Branch2 ggg,hhh,iii
#>

($AddToBooksList = [pscustomobject]@{
                      Site = 'Branch3'
                      Dept = 'jjj,kkk,lll'
                  }
)
# Results
<#
Site    Dept       
----    ----       
Branch3 jjj,kkk,lll
#>


$BooksList.Add($AddToBooksList)
# Results
<#
Site    Dept       
----    ----       
Main    aaa,bbb,ccc
Branch1 ddd,eee,fff
Branch2 ggg,hhh,iii
Branch3 jjj,kkk,lll
#>

$BooksList[3]
# Results
<#
Site    Dept       
----    ----       
Branch3 jjj,kkk,lll
#>
Sign up to request clarification or add additional context in comments.

1 Comment

@sorcrow Please flag this as a anwser (The little checkmark under the votes)
0

If the header doesnt change you could use the script you have and not skip a line and just rewrite the header.

Comments

0

While using Import-Csv and Export-Csv is generally preferable, plain-text processing is required when performance matters.

The file sounds small enough to load into memory as a whole (twice), which speeds up processing:

# Read all lines into an array.
# -ReadCount 0 returns a single array, which is much faster than the
# default one-by-streaming of lines.
$lines = Get-Content -Path $path -ReadCount 0

# Determine the new row to add.
$newLine = '...'

# Write the modified array of lines back to the file.
# Using -Value to pass the array is much faster than using the pipeline.
# Adjust the encoding as needed
Set-Content $path -Encoding utf8 -Value (
  , $lines[0] + $lines[2..($lines.Count-1)] + $newLine
)

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.