2 CSV files - combine into one CSV

Ask your 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.
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
juneb1022
Posts: 34
Last visit: Fri Sep 30, 2016 8:08 am

2 CSV files - combine into one CSV

Post by juneb1022 »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

Just add them together.
$csv3=$csv1+$csv2
User avatar
juneb1022
Posts: 34
Last visit: Fri Sep 30, 2016 8:08 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 »

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

#TYPE System.String
Length
148
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

you must start with two legal csv files.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

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
Last visit: Fri Sep 30, 2016 8:08 am

Re: 2 CSV files - combine into one CSV

Post by juneb1022 »

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
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

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.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: 2 CSV files - combine into one CSV

Post by jvierra »

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
This topic is 10 years and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked