Using PowerShell to open large csv files and exporting specific columns

I'm using the command below to get the first 1,000 rows of data:

Get-Content -First 1000 'C:\Users\Inspiron\Desktop\base.csv' | Out-File 'C:\Users\Inspiron\Desktop\sample.csv'

However, how can I adapt this command to obtain data ranges. For example, to extract the interval between rows 700 through 900.

Another thing, how can I export only a few variables. For example, my database has 120 columns but I want to save only the variables year (1st column), date of birth (4th column) and state of origin (100th column).

Solution

There are a few ways to go about this. Building Mathews helpful answer:

$InCsv  = 'C:\Users\Inspiron\Desktop\base.csv'
$OutCsv = 'C:\Users\Inspiron\Desktop\sample.csv'
$Props  = "year","date of birth","state of origin"

$InCsv = Import-Csv $InCsv
$rangeselect = $InCsv[700..900] # You could use variables here too...

$rangeselect | 
Select-Object $Props |
Export-Csv -Path $OutCsv -NoTypeInformation

This takes the additional step of selecting the properties you want and re-exporting them to a new CSV file.

Note: It's unlikely, but if you are working with very large files this approach may have memory issues. It reads the entire file into memory upfront, storing it in the $csv variable. This could also happen if the system is memory bound, but that's infrequent.

Technically you don't need to assign the $rangeselect variable you can use the range operator ".." directly on the Import-Csv command like:

(Import-Csv $InCsv)[700..900] | 
Select-Object $Props |
Export-Csv -Path $OutCsv -NoTypeInformation

Here, the (..) completes reading all the CSV data first before referencing, so it should work about the same.

If you want to build on the initial sample. Which has the advantage of only reading the first 1000 lines, most likely bypassing any memory constraints:

$InCsv  = 'C:\Users\Inspiron\Desktop\base.csv'
$OutCsv = 'C:\Users\Inspiron\Desktop\sample.csv'
$Props  = "year","date of birth","state of origin"
$Skip   = 700
$First  = 200

Get-Content -First 1000 $InCsv | 
ConvertFrom-Csv |
Select-Object -Skip $Skip -First $First -Property $Props |
Export-Csv -Path $OutCsv -NoTypeInformation

This is effectively a one-liner with a few convenience variables. It takes advantage of the parameters in Select-Object. Note it too only returns the properties you ask for, and so will output a new CSV file with only that data.

You can also combine these approaches, again because Select-Object allows for -Skip, -First and also -Last parameters for some rudimentary initial filtering. That might look something like:

$InCsv  = 'C:\Users\Inspiron\Desktop\base.csv'
$OutCsv = 'C:\Users\Inspiron\Desktop\sample.csv'
$Props  = "year","date of birth","state of origin"
$Skip   = 699
$First  = 200

Import-CSv $InCsv |
Select-Object -Skip 700 -First 200 -Property $Props |
Export-Csv -Path $OutCsv -NoTypeInformation

In this example you may have to play with the boundaries. But, it's still effectively a one-liner and has the potential to get what you're looking for.

Note: Select-Object can tell the command on the left side of the pipe to stop sending data. However, I'm not sure every cmdlet reacts properly, hence performance may vary compared to the Get-Content approach. That may only be important if you're deal with larger files, otherwise I'd go with whatever approach is considered more readable and/or maintainable...