Compiling selected data from multiple .csv or .txt files into one .csv or .xls

I'm relatively new to scripting and could use some help deciding my approach to solving this problem.

I'm working on tracking my company's volume license keys for MS Office suites and individual applications (OneNote, Project, Visio). The previous IT guy had created a spreadsheet with info about which licenses were installed on which computers, but after looking at what was actually installed on individual computers I found that his spreadsheet is not accurate. I wrote a logon script that ran Magic Jelly Bean Keyfinder on each computer on the domain, so now I have ~100 .csv and .txt files with real-world data on what software is installed, and I need to get the MS Office key data compiled so I can check totals against our volume licensing agreements.

The file names have the format "ComputerName.csv" (or .txt). I assumed it would be easier to parse this kind of data out of a .csv than a .txt, but I can go either way depending on which solution is better. The .csv's have this header:

"User Name","PC Name","Audit Date","Product Name","Product ID","Key/Serial","Other 1","Other 2","Other 3"

I'm most interested in "PC Name", "Product Name", "Key/Serial", and "Other 1". The first three are self explanatory, and for MS Office products "Other 1" specifies whether it is an OEM media key or open volume license key. Basically, if the data in the "Product Name" field starts with "Microsoft Office" I want to copy those four fields (or the whole line for all I care) into my compilation .csv or Excel spreadsheet.

So to my untrained eye I need to go through all ~100 files (by reading all the file names into an array, then cycling through the file names in the script?), do a string comparison for each "Product Name" field looking for them to start with "Microsoft Office", and copy the relevant data to a new file. My research has turned up a few possibilities including ADO, or a combination of Scripting.FileSystemObject, Readline, and InStr, or Excell.application.

Any guidance or code examples for solving the various parts of this problem would be greatly appreciated.

Sounds like you have a pretty good idea of a basic plan. Personally I would use ADO to get the information out (actually to be really honest I would use Powershell instead of VBScript).

There is 20 different way to write this script.  The first part is simple, getting all of the CSV paths:
Option Explicit
Dim objFSO : Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")
Dim colFiles : Set colFiles = objFSO.GetFolder("C:\CSV_FILE_FOLDER").Files
Dim objFile
For Each objFile in colFiles
    If UCase(objFSO.GetExtensionName(objFile.Path)) = "CSV" Then
        WScript.echo objFile.Path
    End If
Next

There are numerous ways to read a CSV file, but I would try ADO since you should be able to do a Select query of what you are asking for than having to code manual parsing.  Take a look at this link: http://www.rlmueller.net/ReadCSV.htm

Once you figure out what you want to do to parse the CSV, then I would convert that to a Sub or Function and just pass the path where the WScript.Echo is to process every CSV file.  Try to get started and let us know if you have an error or any other question.

@ebGreen - Noticed you're becoming a proponent of PowerShell.  I'm still trying to find time to finish Windows Powershell in Action (Bruce Payette), I'm about 1/2 way thru it.  I'd be curious to see a Powershell example if you have time to throw it together

Well, if I were going to do it at the command line:

gci C:\path\To\Files -Include "*.csv" | %{Import-Csv $_ | %{if ($_."Product Name" -match "^Microsoft Office"){$_ | ft "PC Name", "Product Name", "Key/Serial", "Other 1"}}}

That is an untested one liner that would display the information. If I were doing it in a script:
foreach ($file in (Get-ChildItem C:\Path\To\Files -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office)
  {
   $_ | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\Path\To\NewFile.csv -NoClobber
  }
 }
}

Again untested, but that spits it out to a CSV file.

ebgreen


foreach ($file in (Get-ChildItem C:\Path\To\Files -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office)
  {
   $_ | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\Path\To\NewFile.csv -NoClobber
  }
 }
}

Again untested, but that spits it out to a CSV file.

It seems like I would want to use -Append instead of -NoClobber if I want this to result in a single CSV file with all the appropriate data in it, right?

I definitely like the looks of this powershell script compared to most of the vbscript samples I've seen to do some of the same work. Is there a good powershell web resource you would recommend for a beginner like me?

Yeah, -Append is right. I couldn't remember it off the top of my head. As far as web resources I would suggest www.powershellcommunity.org

Finally getting around to testing this:

foreach ($file in (Get-ChildItem C:\csv -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office)
  {
   $_ | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\csv\MS_Office.csv -Append
  }
 }
}

I'm getting this error when I run it:

PS C:\Documents and Settings\myname> & c:\csv\MS_Office.ps1
Unexpected token 'PC' in expression or statement.
At C:\csv\MS_Office.ps1:7 char:26
+    $_ | Select-Object "PC <<<<  Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\csv\MS_Office.csv -App
end
    + CategoryInfo          : ParserError: (PC:String) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

Is this something simple like it should be single quotes instead of double?

I should probably move this into the Powershell section of the forum by now.

Ooops. I think $_ should probably be $record. That's what you get when you just spew code on the page without testing. :)

Okay. The original error was because I missed a closing double quote on "^Microsoft Office".

Now I'm not getting the error with the code below but I'm also not getting any results.

foreach ($file in (Get-ChildItem C:\csv -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office")
  {
   $record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\csv\MS_Office.csv -Append
  }
 }
}

I'm guessing my comparison isn't working right. What is the leading carat in "^Microsoft Office" for? Should it instead be like "Microsoft Office*" to account for all the different varieties of Office?

try:

"^Microsoft Office.*"


The ^ matches the beginning of a string.

foreach ($file in (Get-ChildItem C:\csv -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office*")
  {
   $record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1" | Export-CSV C:\csv\results\MS_Office.csv -Append
  }
 }
}

Getting this error now:

PS C:\csv> & c:\csv\MS_Office.ps1\
AuthorizationManager check failed.
At line:1 char:2
+ & <<<<  c:\csv\MS_Office.ps1\
    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException
    + FullyQualifiedErrorId : RuntimeException

This looks like Powershell isn't even letting me execute the command to run the script. Sorry, I'm an absolute beginner here.

Edit: OK, I screwed up and put a backslash at the end of the command, so that's what was causing this error. I'm still not getting any results written to a file when I run it.

It looks like Export-CSV doesn't have an -append feature and that was causing the failure. This doesn't cycle through all the files, but it is writing the result to a CSV file:

PS C:\csv> foreach ($record in (import-csv C:\csv\LT24920.csv)) { if ($record."Product Name" -match "Microsoft Office*")
 { $record | select "PC Name","Product Name","Key/Serial","Other 1" | export-csv c:\csv\results\MS_Office.csv -notypeinf
ormation } }

Now my issue is that this creates a CSV file with the appropriate header and one line of information, but since it isn't appending the data to the end of the file I would end up with only one record (from the last file in the directory) after cycling through all the original files. I still need to be able to compile all this info into a single file with the appropriate records from all the original files. Any suggestions?

I should have just sat down and written something istead of trying to wing it in the first place. Basically the way that I have done things like this in the past is to create an arraylist then add each item to it then at the end export the arraylist:

$alResults = New-Object System.Collections.ArrayList
foreach ($file in (Get-ChildItem C:\csv -Include "*.csv"))
{
 foreach ($record in (Import-CSV $file))
 {
  if ($record."Product Name" -match "^Microsoft Office*")
  {
   $alResults.Add($($record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1")) 
  }
 }
}
$alResults | Export-CSV C:\CSV\Results\MS_Office.csv -NoType

When I run that code I end up with an empty results CSV. When I run this:

$alResults = New-Object System.Collections.ArrayList
    foreach ($record in (Import-CSV c:\csv\LT24920.csv)) # $file))
    {
        if ($record."Product Name" -match "Microsoft Office*")
        {
            $alResults.Add($($record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1"))
        }
    }
echo $alResults

I get this output:

PS C:\csv> & C:\csv\MS_Office_v1.ps1
0
1
2

PC Name                       Product Name                  Key/Serial                    Other 1
-------                       ------------                  ----------                    -------
LT24920                       Microsoft Office Standard ... <correct key>  OPEN [Volume License Key] ...
LT24920                       Microsoft Office Visio Pro... <correct key>  Unknown media Key
LT24920                       Microsoft Office Standard ... <correct key>  Office XP or Open License ...

So that part of the code is working but something isn't working either with the <foreach ($file in (Get-ChildItem C:\csv -Include "*.csv")> or <$alResults | Export-CSV C:\CSV\Results\MS_Office.csv -NoType>. Any suggestions?

Edit: Okay, the problem is with <foreach ($file in (Get-ChildItem C:\csv -Include "*.csv")>. I added the Export-CSV part back in and it gives me a results CSV with the header and the three "Microsoft Office*" records from LT24920.csv just like it should.

$alResults = New-Object System.Collections.ArrayList
    foreach ($record in (Import-CSV c:\csv\LT24920.csv)) # $file))
    {
        if ($record."Product Name" -match "Microsoft Office*")
        {
            $alResults.Add($($record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1"))
        }
    }
echo $alResults
$alResults | Export-CSV C:\csv\results\MS_Office.csv -NoType

$alResults = New-Object System.Collections.ArrayList
$files = Get-ChildItem C:\csv -Include *.csv
foreach ($file in $files)
{
    $currentfile = Import-CSV $file
    foreach ($record in $currentfile)
    {
        if ($record."Product Name" -match "Microsoft Office*")
        {
            $alResults.Add($($record | Select-Object "PC Name", "Product Name", "Key/Serial", "Other 1"))
        }
    }
}
$alResults | Export-CSV C:\csv\results\MS_Office.csv -NoType
echo $alResults

I'm getting this error now:

PS C:\csv> & C:\csv\MS_Office_v1.ps1
Import-Csv : Cannot bind argument to parameter 'Path' because it is null.
At C:\csv\MS_Office_v1.ps1:6 char:27
+     $currentfile = Import-CSV <<<<  $file
    + CategoryInfo          : InvalidData: (:) [Import-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ImportCsvCo
   mmand

I'm getting close, what is causing this error?

Hmm...from the error it acts like for part of your iteration, $file is null. Try putting this line:

Write-Host $file

Right above the line that is thowing the error.

Been out with the flu for a few days, but I'm back at work ready to get this script finished now.

ebgreen

Write-Host $file

Unfortunately that didn't help, it just added 1 to the line number of the error. What is Write-Host supposed to do?

Just for my own understanding, I'd love some confirmation on some concepts. In my code:

$files is an implicitly typed array variable that gets filled with all the file names in C:\csv\ ?
"foreach ($file in $files)" means "for each entry (CSV file name) in the array $files" ?

The core of the script works great when I feed it a file name directly instead of the $file variable, and writing the results from that one file to $alResults then exporting that to a MS_Office.csv works fine also. There is something bunging up the iteration through all the CSVs in C:\csv\.

BTW, thanks for your great help so far ebgreen.

Edit: OK, it's always something simple. At the powershell command line when I run Get-ChildItem C:\csv I get a list of all the files as expected. When I run Get-ChildItem C:\csv -Include *.csv I get nothing but a fresh command prompt even though most of the files in the directory are have the .csv extension. What is the stupidly obvious thing that I'm missing here?

Oh, stupidly obvious (or at least stupidly simple) is the order of the day. The loop for iterating through all the existing CSV files needs to be:

foreach ($file in (Get-ChildItem C:\csv\*.* -Include *.csv))

Now it works very well. Thanks for all the help.