Use PowerShell to Add Two Pieces of CSV Data Together

Doctor Scripto

Summary: Microsoft Scripting Guy, Ed Wilson, talks about adding together two pieces of CSV information and making the rows wider, not longer. Hey, Scripting Guy! I have a problem at work. Our company is in the midst of massive acquisitions and mergers. I mean every few weeks our network completely changes, and we have to absorb a new domain or forest into our central Active Directory infrastructure. To make matters worse, we are getting ready for a SOX audit. I mean, like really. Anyway, what I have decided to do is to store certain information into a SQL Express database. The problem, of course, is that I do not know very much about SQL Server. I want to import CSV data into the database. I know how to do that (I do read the Hey, Scripting Guy! blog every day). What I do not know is how to take two WMI classes and turn them into a single line of properties and a single line of data in CSV format. Can you help me? —TU  Hello TU, Microsoft Scripting Guy, Ed Wilson, is here. Well, today, it is sort of soggy outside. It is damp, overcast, and drizzling rain. I figured it would be a good chance to have a “coffee day,” so I got up early, ground some Kona beans, boiled fresh spring water, and dumped the contents into my French press. While the coffee and the hot spring water became acquainted, I gently warmed some organic cream by placing the cream cup in a bowl of the hot water. I also took the opportunity to “prime” my ceramic Scripting Guys mug by pouring the remaining hot water into it. I then snatched the Scripting Wife’s Surface (she is out with friends perusing holiday specials) and decided to catch up on some of the Scripter email. TU, I have written extensively about working with CSV files, but I do not think anyone has asked about making the columns wider. Usually, when appending one CSV file to another, the process involves adding additional rows of the same sort of data.

Adding one CSV to another one

As an example, I am going to query the Win32_ComputerSystem WMI class and the Win32_OperatingSystem WMI class. The first thing I do is initialize all variables by setting their value to $null. This keeps me from getting confused when I run the script more than one time—it ensures that the script starts clean each time it runs. Here is the line of code I use.

$computer = $osInfo = $compOSInfo = $null I am only interested in a few properties from each WMI class. For compatibility reasons, I am using the Get-WmiObject Windows PowerShell cmdlet (although the technique works just as well with the Get-CimInstance cmdlet). I use the Select-Object cmdlet to choose specific properties from each WMI class, and then I use the Convertto-CSV cmdlet to convert the selected information into CSV format. I do not need the type information because I have no intentions of reconstituting the types, and, therefore, I use the –NoTypeInformation switched parameter. Here are the two queries.

$computer = gwmi win32_computersystem |

    select numberOfProcessors, NumberOfLogicalProcessors, HypervisorPresent |

    ConvertTo-Csv -NoTypeInformation  

$osInfo = gwmi win32_operatingsystem |

    select version, caption, serialnumber, osarchitecture |

    ConvertTo-Csv -NoTypeInformation

Create a new column header and a new row of data

Now I need to add the two column headers together. The $computer variable contains two rows of information. The first row is the column headings, and the second row contains the data associated with the column headings. The contents of the $computer variable are shown here.

PS C:> $computer

“numberOfProcessors”,”NumberOfLogicalProcessors”,”HypervisorPresent”

“1”,”8″,”True”  The $osInfo variable contains a different assortment of column headings and associated data. This is shown here.

PS C:> $osInfo

“version”,”caption”,”serialnumber”,”osarchitecture”

“6.2.9200”,”Microsoft Windows 8 Pro”,”00178-10777-21922-AA381″,”64-bit” To create a new column header row, I need to add the first line from each of the $computer and the $osInfo variables. I also need to ensure I have a comma between the two first rows. I store the results in a new variable I call $compOSInfo. Here is the code.

$compOSInfo = “{0},{1}” -f $computer[0],$osInfo[0] Now I need to create the associated data row for the new heading row. To do this, I will need to ensure that I begin on the second line. To do this, I use the “`r`n” special symbol. Once I am on the new row, I add the contents of the second line of the $computer variable with the contents of the second line of the $osInfo variable. Once again, I make sure I have a comma to separate the two blocks of information. The following line of code accomplishes this task.

$compOSInfo += “`r`n{0},{1}” -f $computer[1],$osInfo[1]  The complete Get-OSAndComputerInfo.ps1 script is shown here. Get-OSAndComputerInfo.ps1

$computer = $osInfo = $compOSInfo = $null

 $computer = gwmi win32_computersystem |

    select numberOfProcessors, NumberOfLogicalProcessors, HypervisorPresent |

    ConvertTo-Csv -NoTypeInformation  

$osInfo = gwmi win32_operatingsystem |

    select version, caption, serialnumber, osarchitecture |

    ConvertTo-Csv -NoTypeInformation

$compOSInfo = “{0},{1}” -f $computer[0],$osInfo[0]

$compOSInfo += “`r`n{0},{1}” -f $computer[1],$osInfo[1]

$compOSInfo TU, that is all there is to adding together two CSV pieces of information. Join me tomorrow when I will talk about more cool stuff. I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace. Ed Wilson, Microsoft Scripting Guy  

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Follow