2

So, I know I can read in a csv file using import-csv like so:

$test = import-csv BPUSAUV20FARS-1000.csv

I found another stack overflow question which gave me some code to decipher column names, like so:

$columns = $test[0].psobject.properties.name

I found a reddit post that helped me find a way to extract multiple columns using select-object like so:

$properties = @('Index', 'Year', 'Day', 'Time', 'Line', 'Beam', 'Pos TPU', 'Depth TPU', 'Status')

$test |Select-Object $properties

But the output from the above command likes like this:

Index     : 1
Year      : EM2040-0073-1000-20200224-222235
Day       : 25
Time      : Accept
Line      : 0.648
Beam      : 24-FEB-2020:22:22:34.98
Pos TPU   : 4.617
Depth TPU : 1124834.70
Status    : 10247261.01

Index     : 2
Year      : EM2040-0073-1000-20200224-222235
Day       : 26
Time      : Accept
Line      : 0.749
Beam      : 24-FEB-2020:22:22:34.98
Pos TPU   : 4.617
Depth TPU : 1124834.73
Status    : 10247261.76

Index     : 3
Year      : EM2040-0073-1000-20200224-222235
Day       : 27
Time      : Accept
Line      : 0.624
Beam      : 24-FEB-2020:22:22:34.98
Pos TPU   : 4.617
Depth TPU : 1124834.76
Status    : 10247263.05

And what I need is this:

1,EM2040-0073-1000-20200224-222235,25,Accept,0.648,24-FEB-2020:22:22:34.98,4.617,1124834.70,10247261.01

I also need to be able to perform these actions on a few hundred files with several million lines each. The smallest file is about 2.4 million lines.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sam Alleman
  • 141
  • 7
  • If you want to actual data as it is in the CSV file, why not use `Get-Content`? – Thom A Mar 16 '20 at 16:13
  • 1
    Your code is fine. There is simply too much data to show in the console window as table. Either write the fata to csv with `Export-Csv` and open in Excel, or display in a grid view with `Out-GridView` – Theo Mar 16 '20 at 16:19
  • 2
    Pipe to `|ConvertTo-Csv` – Mathias R. Jessen Mar 16 '20 at 17:32
  • Get-Content is VERY slow... and I need to rearrange the columns and delete some columns. – Sam Alleman Mar 16 '20 at 18:10
  • You say it is a huge csv, but aparently `Import-Csv` does work (although it may take its time). What are the headers in the current file? Do they coincide with the headers you want to extract? – Theo Mar 16 '20 at 18:37
  • Hopefully you have a machine with a large availability of memory and CPU. ;) – thepip3r Mar 16 '20 at 21:21

1 Answers1

0

As for...

I also need to be able to perform these actions on a few hundred files with several million lines each. The smallest file is about 2.4 million lines.

... dealing with large files in general --- (too long for just a comment)

As per MSDN [IO.File]::OpenText and as noted in another Q&A

The Get-Content cmdlet does not perform as well as a StreamReader when dealing with very large files. You can read a file line by line using a StreamReader like this:

$path = 'C:\A-Very-Large-File.txt'
$r = [IO.File]::OpenText($path)
while ($r.Peek() -ge 0) {
    $line = $r.ReadLine()
    # Process $line here...
}
$r.Dispose()
Some performance comparisons:

Measure-Command {Get-Content .\512MB.txt > $null}

Total Seconds: 49.4742533

Measure-Command {
    $r = [IO.File]::OpenText('512MB.txt')
    while ($r.Peek() -ge 0) {
        $r.ReadLine() > $null
    }
    $r.Dispose()
}

Total Seconds: 27.666803
postanote
  • 15,138
  • 2
  • 14
  • 25