Merge multiple csv files, ignoring metadata line and handling different quotings

Question

I'm trying to merge CSV files in Powershell. I've read numerous answers here but I'm stuck on this problem.

I have a list of csv files, 2 difficulties :

[A] each file has a metadataline, the headers are in the second line.
[B] each file has the same structure, but sometimes quotes surround the column to escape the content.

Thanks to this question : Merging multiple CSV files into one using PowerShell, I'm able to solve these two problems individually.

However, I'm stuck at combining the solutions.

Partial solution A

Skips every metadata line as well as header for subsequent files

Adapting the answer from kemiller2002:

$sourcefilefolderPath = "C:\CSV_folder"
$destinationfilePath = "C:\appended_files.csv"

$getHeader = $true
Get-ChildItem -Path $sourcefilefolderPath -Filter *.csv -Recurse| foreach {
    $filePath = $_.FullName

    $lines = $lines = Get-Content $filePath
        $linesToWrite = switch($getHeader) {
           $true  {$lines | Select -Skip 1}  # skips only the metadata line
           $false {$lines | Select -Skip 2}  # skips both the metadata line as well as headers
        }
    $getHeader = False
    Add-Content $destination_file $linesToWrite
}

The problem : Import-Csv $destination_file give inconsistent results, as the quoting can be different for each source file.

Partial solution B

handles successfully random quoted columns

Solution provided by stinkyfriend.

Import-Csv seems to import the data gracefully when the column quoting, however different from one column to the other, is consistent for each line of the source file.

I could not combine this solution with the one above.

Get-ChildItem -Path $sourcefilefolderPath -File  -Filter *.csv -Recurse |
  Select-Object -ExpandProperty FullName |
  Import-Csv |
  Export-Csv $destination_file -NoTypeInformation -Append

Thanks a lot for your help !

Solution C

produces blank file on my PC

using suggestion from Mathias R. Jessen

Get-ChildItem -Path $sourcefilefolderPath -File  -Filter *.csv -Recurse | foreach {
    Write-Host $_.FullName |
    Get-Content $_.FullName | Select-Object -Skip 1 | ConvertFrom-Csv |
    Export-Csv $destinationfilePath -NoTypeInformation -Append

--- EDIT ---

RESULT

I could solve the problem by creating appended_files.csv using the first matching source file and then append to it.

$pattern_sourceFile = "*.csv*"

$list_files = Get-ChildItem -Path $sourcefilefolderPath -File -Recurse | Where { 
   $_FullName -match $pattern_sourcefile }

Get-Content $list_files[0].FullName | 
 Select-Object -Skip 1 | # skips metadataline
 ConvertFrom-Csv | Export-Csv $destinationfilePath -NoTypeInformation

$list_files | 
 Select-Object -Skip 1 | # skips $array_files[0]
 foreach { Get-Content $_.FullName | 
           Select-Object -Skip 1 | # skips metadata line
           ConvertFrom-Csv |
           Export-Csv $destinationfilePath -NoTypeInformation -Append }

score 1 · Answer 1 · answered Nov 28 '19 at 08:58

Use ConvertFrom-Csv instead of Import-Csv, this way you can still control how many lines to skip:

Get-Content $file |Select -Skip 1 |ConvertFrom-Csv

So you'll end up with something like:

$sourcefilefolderPath = "C:\CSV_folder"
$destinationfilePath = "C:\appended_files.csv"

Get-ChildItem -Path $sourcefilefolderPath -Filter *.csv -Recurse | foreach {
    Get-Content $_.FullName |Select-Object -Skip 1 |ConvertFrom-Csv |Export-Csv -Path $destinationfilePath -NoTypeInformation -Append
}

Merge multiple csv files, ignoring metadata line and handling different quotings

1 Answers1