CODEWITHSUNDEEP
powershellcsvmerge
Kevin
Kevin

Reputation: 181

Multiple csv files to one csv file - Powershell

I have been reading through some of the previous posts about the concept, but all the solutions i find very different from eachother.

I have multiple csv's devided over seperate folders (all with kind of the same path but the subfolders are different).

I now need to import these csv's, change the headers and export it into one single csv.

I have been trying with this but im getting a very weird error: Import-Csv : Cannot open file "C:\Windows\system32\Book1.csv" Although my path is refering to C:\csv ?

$CSVFolder = 'C:\csv\';                                                               #'
$OutputFile = 'C:\export\test3.csv';

$CSV= @();

Get-ChildItem -Path $CSVFolder -Filter *.csv | ForEach-Object { 
    $CSV += @(Import-Csv -Path $_)
}

$CSV | Export-Csv -Path $OutputFile -NoTypeInformation -Force;

I was thinking of using a datatable for the headers, but its not so clear to me at this point. Also the issue with the error is not clear for me...

Upvotes: 1

Views: 2832

Answers (3)

mklement0
mklement0

Reputation: 440142

As has been noted:

  • Import-Csv -Path $_ should be Import-Csv -Path $_.FullName in your code,

    • (Strictly speaking, it should be Import-Csv -LiteralPath $_.FullName, because strings passed to the -Path parameter are subject to wildcard resolution).

  • because the [System.IO.FileInfo] instances representing files returned by Get-ChildItem are converted to strings when they are passed to the -Path (or -LiteralPath) parameter as a command-line argument (as opposed to via the pipeline), in which case the the mere file name rather than the full path is used, if your Get-ChildItem command targets a directory in Windows PowerShell (see background information below).

    • A mere filename such as Book1.csv is interpreted as relative to the current directory (which happened to be C:\Windows\system32 in your case), so Import-Csv looks for file C:\Windows\system32\Book1.csv rather than the intended C:\csv\Book1.csv.

    • Note that piping Get-ChildItem output to cmdlets is generally not affected by this, because the .PSPath property (which PowerShell adds behind the scenes) containing the full path (including PS provider prefix) binds to the -LiteralPath parameter.
      Note that as of PSv5.1.14393.693, however, this mechanism is broken for Import-Csv, due to a bug.

This is a common pitfall that occurs whenever [System.IO.FileInfo] instances are passed to cmdlets that accept file paths via [string](-array)-typed parameters as arguments.

To be safe: Always use .FullName when you pass objects received from Get-ChildItem to another command as a parameter value (as opposed to via the pipeline) to ensure that the full path is passed.

Optional background information:

This behavior is a pitfall, because it is perfectly reasonable to assume that passing a [System.IO.FileInfo] instance as-is to a command that accepts file paths works, given the object-oriented nature of PowerShell - especially, since it does work reliably when using the pipeline rather than a parameter value.

Unfortunately, the built-in cmdlets that accept file paths (-Path, -LiteralPath parameters) do so as [string]s only (there is no parameter set that accepts [System.IO.FileInfo] instances directly), and it is in the course of [System.IO.FileInfo]-to-string conversion that the problem arises.

There also wouldn't be a problem if the [System.IO.FileInfo] instances consistently evaluated to the files' full paths, which is unfortunately not the case in Windows PowerShell (this has since been fixed in PowerShell Core):

  • Get-ChildItem <directory> outputs [System.IO.FileInfo] instances that evaluate to file names only in a string context.

  • Get-ChildItem <literalFilePathOrWildCardExpr> outputs [System.IO.FileInfo] instances that evaluate to full paths.

In other words: It is only if Get-ChildItem targets a directory (folder) that the objects it returns evaluate to their file names only in a string context.
Targeting a specific file or using a wildcard expression results in full paths, by contrast; with Get-Item, that's always the case.

Upvotes: 1

Esperento57
Esperento57

Reputation: 17492

try this code. This code take all csv file, import them and take only column 1, 2, 3 and change column name to header1, header2, header3, then export all into new csv file

Get-ChildItem "C:\temp2" -Filter "*.csv" | 
    %{Import-Csv $_.FullName -Header header1, header3,header4} | 
                    Export-Csv "c:\temp\result.csv" -NoTypeInformation


#a short version (for no purist)
gci "C:\temp2" -Filter "*.csv" | %{ipcsv $_.FullName -Header header1, header3,header4} | epcsv "c:\temp\result.csv" -NoType

Upvotes: 0

Adil Hindistan
Adil Hindistan

Reputation: 6615

You simply need to 'fullname' property, instead of 'name'.

Ex:

PS /Users/adil/Downloads> gi *csv |select name                                                                  

Name              
----              
tradesdownload.csv


PS /Users/adil/Downloads> gi *csv |select name, fullname                                                                

Name               FullName                                
----               --------                                
tradesdownload.csv /Users/adil/Downloads/tradesdownload.csv

Upvotes: 0

Related Questions