locked
loads blank data on second file combine RRS feed

  • Question

  •  

     I'm trying to merge to like files. The script runs, but the data from 2nd file in the merged.csv contains all blanks.

     Looks like it writes to merged file because I open it and it has a total record count of the2 but no data from 2nd file. Can I get a Count of each file prior to doing the combine. Then a total in merged file for validation.

    function Merge-CSVFiles {            
    [cmdletbinding()]            
    param(            
        [string[]]$CSVFiles,            
        [string]$OutputFile = "c:\merged.csv"            
    )            
    $Output = @();            
    foreach($CSV in $CSVFiles) {            
        if(Test-Path $CSV) {            
                        
            $FileName = [System.IO.Path]::GetFileName($CSV)  
            $temp = Import-CSV -Path $CSV          
            #$temp = Import-CSV -Path $CSV | select *, @{Expression={$FileName};Label="FileName"}            
            $Output += $temp            
                
        } else {            
            Write-Warning "$CSV : No such file found"            
        }            
                
    }            
    $Output | Export-Csv -Path $OutputFile -NoTypeInformation            
    Write-Output "$OutputFile successfully created"            
                
    }         
    Merge-CSVFiles -CSVFiles '\\serv1\det.csv','\\serv2\det.csv' -OutputFile C:\compress_in\combine_det.csv

     Thanks.

     

    Friday, November 16, 2018 5:53 PM

Answers

  • No headers just catenate into one file.

    Get-Content '\\serv1\det.csv','\\serv2\det.csv' | Out-File C:\compress_in\combine_det.csv


    \_(ツ)_/

    • Marked as answer by hart60 Sunday, November 18, 2018 1:42 PM
    Sunday, November 18, 2018 12:28 AM

All replies

  • You cannot use an array to store the files.  Use "Export-Csv -Append" to concatenate Csv files.

    function Merge-CSVFiles {            
        [cmdletbinding()]            
        param(            
            [string[]]$CSVFiles,            
            [string]$OutputFile = 'c:\merged.csv'            
        )            
        foreach($CSV in $CSVFiles) {            
            if(Test-Path $CSV) {            
                Import-CSV -Path $CSV | Export-Csv $OutputFile -Append -NoTypeInformation          
            } else {            
                Write-Warning "$CSV : No such file found"            
            }               
        }
    }


    \_(ツ)_/


    • Edited by jrv Friday, November 16, 2018 6:35 PM
    Friday, November 16, 2018 6:34 PM
  • You didn't show the csv files.  It works for me if both csv files have the same headers.  The headers of the first csv seem to determine what gets exported.

    • Edited by JS2010 Saturday, November 17, 2018 2:52 PM
    Saturday, November 17, 2018 2:52 PM
  • It is obvious with a little bit of thinking that you cannot combine dissimilar Csv files.

    It should also be obvious that you cannot extract text from a file and then try to use Export-Csv to export the text.

    The user's code is a way to do this but it will be slow and likely fail if the files are too large. The pipeline prevents this.

    Yes - if the headers are different only files that match the first files header will be exported.

    If we modify the code to use a "begin" block to capture the fist files header then we can test each file for a match and skip mismatched files with a message.


    \_(ツ)_/



    • Edited by jrv Saturday, November 17, 2018 3:04 PM
    Saturday, November 17, 2018 2:57 PM
  • This will do a simple check:

    function Merge-CSVFiles {            
        [cmdletbinding()]            
        param(            
            [string[]]$CSVFiles,            
            [string]$OutputFile = 'c:\merged.csv'            
        )
        begin{
            # get header from first file
            $firstheader = Get-Content $CSVFiles[0] -TotalCount 1
        }
        Process{
            foreach($CSV in $CSVFiles) {            
                if(Test-Path $CSV) {
                    $header = Get-Content $CSV -TotalCount 1
                    if($firstheader -eq $header){
                        Import-CSV -Path $CSV | Export-Csv $OutputFile -Append -NoTypeInformation
                    }else{
                        Write-Host "Header does not match $CSV" 
                    }
                } else {            
                    Write-Warning "$CSV : No such file found"            
                }
            }
        }
    }


    \_(ツ)_/


    • Edited by jrv Saturday, November 17, 2018 3:15 PM
    Saturday, November 17, 2018 3:14 PM
  • The files don't have headers. They contain same number of fields, just need the fasts way
    to combine them into a single file. The files are over 200,000 lines each,

     Thanks
    Sunday, November 18, 2018 12:25 AM
  • No headers just catenate into one file.

    Get-Content '\\serv1\det.csv','\\serv2\det.csv' | Out-File C:\compress_in\combine_det.csv


    \_(ツ)_/

    • Marked as answer by hart60 Sunday, November 18, 2018 1:42 PM
    Sunday, November 18, 2018 12:28 AM
  •  Yep that works.

     Thanks again.
    Sunday, November 18, 2018 1:42 PM
  • Note that out-file encodes in "unicode" while set-content encodes in "ansi".  But I've only found that to matter with Infoblox so far.

    Sunday, November 18, 2018 3:00 PM