2 CSV files - combine into one CSV

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

2 CSV files - combine into one CSV

Post by juneb1022 » Tue Oct 01, 2013 3:54 am

I've got 2 csv files, each with columns. In would like to import row by row from one of the csv files into the 2nd csv file. Im just not quite sure how to accomplish this. Any guidance is appreciated, thanks.

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Tue Oct 01, 2013 7:14 am

Just add them together.
$csv3=$csv1+$csv2

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Tue Oct 01, 2013 10:02 am

Yes I tried that - I get back this below when runing export-csv $csv3.

#TYPE System.String
Length
148

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Tue Oct 01, 2013 10:33 am

you must start with two legal csv files.

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Tue Oct 01, 2013 11:19 am

Here is a full example - copy it and try it exactly as it is.
PowerShell Code
Double-click the code block to select all.
PS > cat data.csv
"Status","ComputerName"
"Up","omega"
"Up","w8test"
"Up","omega2"

PS > $csv1=import-csv data.csv
PS > $csv2=import-csv data.csv
PS > $csv4=$csv1+$csv2
PS > $csv4

Status                                                      ComputerName
------                                                      ------------
Up                                                          omega
Up                                                          w8test
Up                                                          omega2
Up                                                          omega
Up                                                          w8test
Up                                                          omega2


PS > $csv4|Export-Csv csv4.csv
PS > import-csv csv4.csv

Status                                                      ComputerName
------                                                      ------------
Up                                                          omega
Up                                                          w8test
Up                                                          omega2
Up                                                          omega
Up                                                          w8test
Up                                                          omega2


PS >

User avatar
juneb1022
Posts: 34
Joined: Mon Jun 10, 2013 10:14 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 » Wed Oct 02, 2013 6:29 am

Ive tried the basic import with valid csv files. What happens is that the column of $csv2 gets added to the column of $csv1. The rest of the columns in $csv2 are not imported.

I think I need to do this row by row, ive tried running a get-member on import-csv and export-csv, but i get an error back that says there is a missing parameter.

Here is the code.....

# Assign variables
$InventoryCSVa = "D:\code_testing_inventorya.csv"
$RemoteServerSessions = "D:\RemoteSrvSessions.txt"
$InventoryCSVb = "D:\code_testing_inventoryb.csv"
$instancenum = Get-Date -UFormat "%Y%m%d"
$FinalInventory = "D:\FinalInventory.csv"

# Retrieve most recent list from AD
$servers = Get-QADComputer -searchroot 'sitename' |`
select-object name,description,managedby | sort Name | Export-Csv $InventoryCSVa -NoTypeInformation

# Import CSV file and extract just the server names
Import-Csv $InventoryCSVa | select Name | Export-Csv -Path $RemoteServerSessions -NoTypeInformation -Force -Encoding ascii

#File Cleanup for remote connections
( get-content $RemoteServerSessions ) | foreach {$_ -replace "`"",""} | Set-Content $RemoteServerSessions

# Create remote sessions to all servers
$sessions = get-content -Path 'D:\RemoteSrvSessions.txt' | new-pssession

# Block of code for WMI
$Block = {

$OperatingSystem = gwmi win32_operatingsystem
$ComputerSystem = gwmi win32_computersystem
$TimeZone = gwmi Win32_TimeZone
$NIC = gwmi win32_NetworkAdapterConfiguration | where {$_.IPEnabled -eq "True"} | select IPAddress


$Properties=@{

Name = $OperatingSystem.CSName
OperatingSystem = $OperatingSystem.Caption
ServicePack= $OperatingSystem.CSDVersion
PhysicalMemory = $ComputerSystem.TotalPhysicalMemory
NumberOfProcessors = $ComputerSystem.NumberOfProcessors
LastBootTIme = $OperatingSystem.ConvertToDateTime($OperatingSystem.LastBootupTime)
IPAddress = $NIC.IPAddress
TimeZone = $TimeZone.Caption

}

New-Object PsObject -Property $Properties

}

Invoke-Command -ScriptBlock $block -Session $sessions | Select 'Name','OperatingSystem', 'ServicePack','PhysicalMemory','NumberOfProcessors','LastBootTime','TimeZone','IPAddress' | sort Name | Export-Csv $InventoryCSVb -NoTypeInformation

get-pssession | Remove-PSSession

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 7:03 am

The two CSV files do not match.

Believe me. If you have good CSV files this works. It is how PowerShell is designed.

From what is happening it appears that you are using text files and not CSV files.

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 7:35 am

You cannot port objects across a remote session and have them work correctly. You must pull back the text of the CSV and convert it back into a CSV object. You are doing this the very much hard way.

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 7:44 am

This looks more like what you are trying to do:
PowerShell Code
Double-click the code block to select all.
# Retrieve most recent list from AD
$sessions=Get-QADComputer -searchroot 'sitename'|
    ForEach-Object{
        new-pssession -ComputerName $_.Name
}

# Block of code for WMI
$Block = { 
    $OperatingSystem = gwmi win32_operatingsystem 
    $ComputerSystem = gwmi win32_computersystem 
    $TimeZone = gwmi Win32_TimeZone 
    $NIC = gwmi win32_NetworkAdapterConfiguration | where {$_.IPEnabled -eq "True"} | select IPAddress

    $Properties=@{ 
        Name = $OperatingSystem.CSName 
        OperatingSystem = $OperatingSystem.Caption
        ServicePack= $OperatingSystem.CSDVersion 
        PhysicalMemory = $ComputerSystem.TotalPhysicalMemory
        NumberOfProcessors = $ComputerSystem.NumberOfProcessors
        LastBootTIme = $OperatingSystem.ConvertToDateTime($OperatingSystem.LastBootupTime) 
        IPAddress = $NIC.IPAddress 
        TimeZone = $TimeZone.Caption 
    }

    New-Object PsObject -Property $Properties

}

Invoke-Command -ScriptBlock $block -Session $sessions | 
    Export-Csv $InventoryCSVb -NoTypeInformation 

$sessions | Remove-PSSession
This has nothing to do with adding CVS files.

User avatar
jvierra
Posts: 13793
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: 2 CSV files - combine into one CSV

Post by jvierra » Wed Oct 02, 2013 7:51 am

I just ran the code I posted. Once for each site.

I then ran this and added the files:
PowerShell Code
Double-click the code block to select all.
$csvA=Import-Csv inventoryA.csv
$csvB=Import-Csv inventoryB.csv
$all=$csvA+$csvB
$all

Locked