0

I am trying to organize the column names by retrieving the unique header names of the csv files.

I used the following the code to retrieve the header names, but this script response is not fast when I have large size or millions of csv files in directories & subdirectories.

$files = Get-ChildItem "F:\MY_DATA\ASUSH" -Recurse
foreach ($f in $files) {
if ($f -Like "*.csv") {
  echo $f.FullName
  $Data=Get-Content -Path $f.FullName
  echo $Data[0]
}
}

What is the fastest way to retrieve the csv file header names?

wkl
  • 77,184
  • 16
  • 165
  • 176
sridharnetha
  • 2,104
  • 8
  • 35
  • 69

3 Answers3

3

Get-Content has a -TotalCount parameter that will only read a certain number of lines.

$Data = Get-Content -Path $f.Fullname -TotalCount 1

That should speed things up.

James Parr
  • 46
  • 1
  • 1
    Nice. To complement, if one wants to get the names of the headers as an array: `($Data, $Data | ConvertFrom-Csv).PSObject.Properties.Name`. One might be tempted to use a simple `$Data -split ','` instead, but this would be less robust as CSV fields could be quoted and contain embedded `,`. – zett42 Aug 03 '22 at 20:47
0

Leaving aside that direct use of .NET APIs can also be used to speed up enumeration of files, here's an efficient .NET API solution for reading the first line of each CSV file:

foreach ($f in Get-ChildItem F:\MY_DATA\ASUSH -Filter *.csv -Recurse) {
  $f.FullName
  # Read and output the first line of the file at hand.
  [Linq.Enumerable]::Take(
    [System.IO.File]::ReadLines($f.FullName),
    1
  )
}

Perhaps surprisingly, this is noticeably faster than the more concise, conceptually more direct solution in James Parr's helpful answer.

Even a hybrid approach,
[System.IO.File]::ReadLines($f.FullName) | Select-Object -First 1
performs better in my informal tests (but is slower than the cmdlet-less solution at the top).

All these solutions benefit from reading the file's lines one by one, on demand. That is, processing stops once the first line has been read (unlike your approach, which in essence is (Get-Content -Path $f.FullName)[0], which reads all lines into an array first, then extracts the first array element).

The reason that a Get-Content solution optimized with -TotalCount 1 (aka -First 1 aka -Head 1) is slower than an optimized .NET API solution is likely due to the fact that Get-Content decorates each output line with metadata, as discussed in the bottom section of this answer, which also contains general Get-Content performance tips.

mklement0
  • 382,024
  • 64
  • 607
  • 775
  • Instead of `Select-Object` can you just use the array reference, `[System.IO.File]::ReadLines($f.FullName)[0]` not sure if you'd have to wrap that in an array subexpression. Moreover, there are certainly times when `Select-Object` works more reliably. However, if we're confident in the files... Something like: `@( [System.IO.File]::ReadLines( $File ) )[0]` seemed to work in casual testing. – Steven Aug 03 '22 at 18:13
  • @Steven, for performance it is crucial to avoid index operations here (which don't work on `IEnumerable` instances anyway). `[System.IO.File]::ReadLines()` is a _lazy_ iterator, and you want to make sure that only _one_ element is read on demand. – mklement0 Aug 03 '22 at 18:15
  • To spell it out: your `@(...)[0]` approach negates the benefits of lazy (on-demand) enumeration, by forcing enumeration of _all_ elements, up front, capturing them in an array that must be created to hold them, and then applying `[0]` to get the first element. – mklement0 Aug 03 '22 at 18:18
  • Apologies I didn't see the Linq portion of your example. I was only suggesting it instead of `Select-Object`. In very casual testing the `@(...)[0]` approach seems marginally faster. Taking an average across 10 runs against a rather small CSV file resulted in an average of about 2.5 ms for the array subexpression/index approach and .36 ms for the `Select-Object` approach. Modest as that might be. I'd have to test further to see if that changes with larger files. – Steven Aug 03 '22 at 19:13
  • @Steven, the point is that for predictable performance you want to avoid enumerating _all_ lines, because with a _large_ files `@(...)[0]` is not only slower than `... | Select-Object -First 1`, it also allocates a lot of unnecessary memory. As an aside: in cases where you _do_ need to read all lines into memory at once, `Get-Content -ReadCount 0` is the fastest option. – mklement0 Aug 03 '22 at 19:19
0

Would probably also help if you:

  • Use the -filter parameter to allow File System Provider to only return .csv file.
  • Use FOrEach-Object to begin processing before all files are collected
  • Eliminate the $Data intermediate variable
Get-ChildItem "F:\MY_DATA\ASUSH" *.csv -Recurse | ForEach-Object{
    echo $_.FullName
    echo (Get-Content -Path $_.FullName -TotalCount 1)
}
Keith Miller
  • 702
  • 5
  • 13