locked
Combining Multiple CSV Files with Powershell RRS feed

  • Question

  • Hi,

    I have a number of CSV files that I want to combine into a single CSV file. They all have the same number of rows and one of the files has 2 columns.(i.e. a timestamp row and a numerical value that is associated with each timestamp) and all of the rest have a single column (a numerical value that is associated with the relevant timestamp in the first column of the first row but for a different system). I need the output file to have multiple columns e.g. if there is a single two-column file, as described, and 4 single-column files, the output file would have six columns with each of the single columns from the 4 single-column files being placed progressively to the right of the two-column file to allow information for the relevant timestamp for each of the 4 additional systems (as well as the original) to be viewed and ultimately graphed.

    I have been trawling through the web and Powershell utilities such as import-csv, export-csv, get-content etc etc to figure out how I can do this but without success. I need the final output file that results from the combination to be in a format such that when I use if as the input to import-csv, the first row will be in a format such that each component of the first row will be a heading for the imported CSV file ... but I think this should happen by default anyway. Any ideas on this?

    Thanks,

    alan_the_geek.

    Wednesday, December 7, 2011 11:47 AM

Answers

  • # Convert any csv file into an in-memory 2-D array
    $csvFile = 'test.csv'
    $csv = @(Import-Csv $csvFile)
    $fields = @($csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty name)
    $array = New-Object 'object[,]' $csv.Count,$fields.Count
    for ($i=0; $i -lt $csv.Count; $i++) {
        $j = 0
        foreach ($field in $fields) {
            $array[$i,$j] = $csv[$i].$field
            $j ++
            } # end foreach $field
        } # end for $i
    
    "Your CSV file has $($array.GetUpperBound(0)+1) records"
    "Your CSV file has $($array.GetUpperBound(1)+1) fields"
    $array
    


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Wednesday, December 7, 2011 5:57 PM
  • We are making this far more complicated than necessary:  As mj pointed out, all we need to do is treat them as text files.  The following example takes two valid csv files, one with 'FirstName' and 'LastName', and the second with 'City' columns.  Each has the same number of rows, and the rows correspond across csv files. 

     

    $csv1 = @(gc users.csv)
    $csv2 = @(gc cities.csv)
    $csv3 = @()
    for ($i=0; $i -lt $csv1.Count; $i++) {
        $csv3 += $csv1[$i] + ',' + $csv2[$i]
        }
    
    $csv3
    

     


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Thursday, December 8, 2011 9:34 AM

All replies

  • Hard to give an answer without knowing a little more about what the data looks like.  You say these are CSV files.  Do they have column headings, and will they be unique across all the file?

    If not, you're going to need to figure out how you're going to designate what the column headings will be in the output csv.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, December 7, 2011 11:57 AM
  • Hi,

    Yes, these files each have column headings which will be unique across the file. Each file was actually created with export-csv.

    Thanks,

    alan_the_geek.

    Wednesday, December 7, 2011 12:00 PM
  • I'd load them into a 2D array first, and then iterate through that to build the output.

     


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, December 7, 2011 12:12 PM
  • This is going to be a collating operation, not a concatentaion.
    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, December 7, 2011 12:38 PM
  • That's not the type of combination needed.
     
    On 12/7/2011 6:17 AM, Bigteddy wrote:
    > Command prompt:
    > copy file1.csv + file2.csv file3.csv
    > ...will concatinate file1 and file2 to form file3.
    >
     
    Wednesday, December 7, 2011 1:29 PM
  • Hi,

    Yes I was thinking that a 2D array might be a way to do it, just thought that there might be a slicker way to do it with some of the csv tools, get-content etc. I'll take a look at the 2D array approach now, will need to create an array where both dimensions are initially of an indeterminate size but will see how it goes, would imagine that this might be a common-ish requirement so I'll post back here how it goes..

     

    Thanks,

    alan_the_geek.

    Wednesday, December 7, 2011 2:11 PM
  • I wouldn't use import-csv.  Just do a get-content and split it.

    Add them to a 2D array.  Your primary index will be line number.  Your secondary index will be the split elements, so that $array[0] will be the split elements from the first lines (header row)of each file.  $array[1] will be the split elements from the second line (first data row), lather, riinse, repeat.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, December 7, 2011 2:49 PM
  • That sounds awfully complicated, mj, when my code 'works', but it doesn't.  Can you see any reason why it won't assign 'John', 'Smith', 'Jill', 'Jackson' to my array?  It prints the values out correctly, but won't assign.

     


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Wednesday, December 7, 2011 3:06 PM
  • Bad syntax.

    $array[$i,$j] means "the $i and $j elements of $array.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "
    Wednesday, December 7, 2011 3:14 PM
  • Parsing CSV file content in general involves reading quoted data
    (usually found when the data field includes the delimiter character), so
    I find it best to leverage on the CSV parsing already coded in Import-CSV
     
    On 12/7/2011 8:49 AM, mjolinor wrote:
    > I wouldn't use import-csv. Just do a get-content and split it.
     > ...
     >
     
    Wednesday, December 7, 2011 3:15 PM
  • I suspect that it is your syntax for accessing two-dimensional array
    elements.
     
    See
     
     On 12/7/2011 9:06 AM, Bigteddy wrote:
    > That sounds awfully complicated, mj, when my code 'works', but it
    > doesn't. Can you see any reason why it won't assign 'John', 'Smith',
    > 'Jill', 'Jackson' to my array? It prints the values out correctly, but
    > won't assign.
    >
     
     
    Wednesday, December 7, 2011 3:18 PM
  • You initialized the array as a PowerShell array
    $array = @()
     
    The webpage initialized the array as a .NET array
    $array2 = New-Object 'object[,]' 10,20
     
    You probably just want to keeps yours as a PowerShell array and use the
    $array1[2][1]
    pattern from the webpage.
     
    Something like
    $array[$i][$j] = $a[$i].$field
     
    On 12/7/2011 9:24 AM, Bigteddy wrote:
    > I was just looking at that very page when you posted this. What's the
    > difference between:
    > $array2[4,8] = 'Hello'
    > (from said web reference), and my:
    > $array[$i,$j] = $a[$i].$field
    >
     
    Wednesday, December 7, 2011 3:33 PM
  • Thanks for your help guys... unfortuntely 2D arrays not exactly my strongpoint so can't add much at this stage but seems to me we're tantalisingly close :-) Thanks, alan_the_geek.
    Wednesday, December 7, 2011 3:35 PM
  • # Convert any csv file into an in-memory 2-D array
    $csvFile = 'test.csv'
    $csv = @(Import-Csv $csvFile)
    $fields = @($csv | Get-Member -MemberType NoteProperty | Select-Object -ExpandProperty name)
    $array = New-Object 'object[,]' $csv.Count,$fields.Count
    for ($i=0; $i -lt $csv.Count; $i++) {
        $j = 0
        foreach ($field in $fields) {
            $array[$i,$j] = $csv[$i].$field
            $j ++
            } # end foreach $field
        } # end for $i
    
    "Your CSV file has $($array.GetUpperBound(0)+1) records"
    "Your CSV file has $($array.GetUpperBound(1)+1) fields"
    $array
    


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Wednesday, December 7, 2011 5:57 PM
  • Looking good, thanks!! I've played around with that last script and it seems to do what it says on the tin ... now I need to figure out:

    (i) How to add the information from subsequent files (i.e. other than test.csv) into the 2D array

    and

    (ii) How to ultimately export the final 2D array to the multi-column CSV file...

    Thoughts?

     

    alan_the_geek.

    Wednesday, December 7, 2011 6:21 PM
  • I've thought about that part a bit.  Adding data of similar length (adding columns) would involve creating a new 2D array with different Y dimensions.

    In other words, if you have a 2x2 array, and you want to add a column of data to create a 2x3 array (2 records, 3 fields), you would have to create a new array with these dimensions and copy the two arrays into the new one manually by looping.

    I haven't thought about getting the data out again, except for the fact that the array does not contain the column headings, so these will have to be re-created somehow.


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Wednesday, December 7, 2011 6:55 PM
  • Hi,

    OK, looks like the first task is creating the new multi-dimensional array containing information from all of the files, so I'll have a go at that ... by the way. adding a column will increase the array from an nx2 to an nx3 array, rather than a 2X2 to a 2x3 - correct?

    Thanks,

    alan_the_geek

    Wednesday, December 7, 2011 8:18 PM
  • The way I'm thinking about this array, the first dimension are all the records, and the second dimension are the fields.  So instead of thinking in x and y, think column and row.  2x3 means to me 2 rows, 3 columns.

    So if you have a 2x2 array, and you add a column, you will have a 2x3 array.  If you added a row instead, you would have a 3x2 array.


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Thursday, December 8, 2011 5:54 AM
  • We are making this far more complicated than necessary:  As mj pointed out, all we need to do is treat them as text files.  The following example takes two valid csv files, one with 'FirstName' and 'LastName', and the second with 'City' columns.  Each has the same number of rows, and the rows correspond across csv files. 

     

    $csv1 = @(gc users.csv)
    $csv2 = @(gc cities.csv)
    $csv3 = @()
    for ($i=0; $i -lt $csv1.Count; $i++) {
        $csv3 += $csv1[$i] + ',' + $csv2[$i]
        }
    
    $csv3
    

     


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    Thursday, December 8, 2011 9:34 AM
  • Thanks Teddy. Unfortunately I've got dragged into something else that has taken up my time for the last couple of days and will do so for the early days of next week also. I will get time to take a look at your solution during the coming week... but it certainly looks good!!

    Thanks,

    alan_the_geek.

    Saturday, December 10, 2011 2:16 PM
  • Yes, I'm sure my 'text file' solution will work.  You can concatinate (across) as many csv files as you like all in the same line

    + $csv3[$i] + csv4[$i] + ...

    Then all you have to do is write the output to file:

    $finalCsv | out-file ...


    ([string](0..9|%{[char][int](32+("39826578846355658268").substring(($_*2),2))})).replace(' ','')
    • Proposed as answer by jh hj Sunday, April 13, 2014 9:15 PM
    Saturday, December 10, 2011 2:20 PM
  • You can also use Add-Member to build a csv row (PSObject) incrementally.

    # Add PSObject members (noteproperty) from src to dest
    #
    function Add-CsvMember($rowSrc, $rowDest)
    {
        $headers = $rowSrc | Get-Member -MemberType NoteProperty | Select Name;
    
        foreach ($header in $headers) {
            $name = $header.Name;
            $value = $rowSrc.$name;
            $rowDest | Add-Member NoteProperty $name $value;
        }
    }
    
    # Add columns from two imported csv files
    #
    function Add-Csv($csv1, $csv2)
    {
        $maxRows = [Math]::Max($csv1.Count, $csv2.Count);
    
        for ($i = 0; $i -lt $maxRows; $i++) {
            $csvRow = New-Object PSObject;
            if ($i -lt $csv1.Count) { Add-CsvMember $csv1[$i] $csvRow }
            if ($i -lt $csv2.Count) { Add-CsvMember $csv2[$i] $csvRow }
            $csvRow;
        }
    }
    
    $csv = New-Object PSObject;
    
    $tmp = Import-Csv csv1.csv;
    $csv = Add-Csv $csv $tmp;
    
    $tmp = Import-Csv csv2.csv;
    $csv = Add-Csv $csv $tmp;
    

    Monday, December 12, 2011 12:11 PM
  • This  works for my needs. However, I am having issues getting the $csv3 data to export-csv. The output looks god on the screen but when I open the csv exported file there is only Length data.  Any ideas, I need the final output in a new csv file. thanks again
    Tuesday, May 24, 2016 10:11 PM
  • This  works for my needs. However, I am having issues getting the $csv3 data to export-csv. The output looks god on the screen but when I open the csv exported file there is only Length data.  Any ideas, I need the final output in a new csv file. thanks again

    This thread was closed 5 years ago.  Please start a new thread with a current example.  In 5 years PowerShell has completely changed.


    \_(ツ)_/

    Tuesday, May 24, 2016 10:35 PM