Need some help with format Output to .csv

This topic contains 4 replies, has 3 voices, and was last updated by  Leo Bounds 4 months ago.

  • Author
    Posts
  • #73006

    Leo Bounds
    Participant

    I'm getting some brain strain working on this so I appreciate any help here if you can.

    What I am trying to do is pull some data out of an .xml file and export to a .csv. That part is working. The problem is that each name pulled from the .xml creates a new line entry in the .csv.

    My goal is to have it create one line entry with all the data on one line. Like this Program1, program2, program3, etc.
    Instead of:
    Program1
    program2
    program3

    The reason is I need to keep adding new one line entries with this information from multiple computers.

    code that works and creates multiple lines

    # get files to process each .xml file held into $filenames
    $fileNames = Get-ChildItem -Path C:\test1 -recurse -Include compat*.xml
    
    # For each loop to process each .xml file held in $FileNames
    ForEach ($name in $FileNames.Name)
    
    {
    [XML]$results = get-content $name
    
    # gets program names that are available from compatreport, then adds lines to .csv
    $results.compatreport.programs.program.name | add-content C:\test1\AddedContent.csv
    }
    

    If I can get it to start creating one single line with the data then I will want to add $computerName and $Date right in front of the main data of the program names.

    The complete purpose is to have a report script that will run on each system and add a new line entry to the .CSV with:
    Computer Name, Date, Program Names

    Any assistance pointing me in the right direction would be much appreciated.
    Thank you

  • #73015

    Don Jones
    Keymaster

    That is unfortunately not how PowerShell thinks about CSV files.

    Add-Content will always add new lines.

    For PowerShell, each row of a CSV is an object, and its columns are the properties of that object. Add-Content – none of the -Content commands – will add text to the _end_ of a line. They're simply not designed to "construct" a CSV in the way you're doing it. PowerShell, for better or for worse, isn't a great text-manipulation shell, which is what you need here.

    You should accumulate all of your output for a single "line" into a variable, and then output it to a text file all at once. Without doing a great deal of low-level .NET coding, that's probably the easiest way to accomplish what you're trying.

    I'll also point out that what you're doing really isn't what CSV files are designed for. You have a one-to-many relationship; that is, each computer may have multiple programs. CSV files are "flat" files, meaning they cannot represent relationships like that. The output you're proposing would be very nonstandard, and difficult to manipulate. That's why it's going to be hard to do what you want – it isn't what CSV files are supposed to do.

    REALLY, you want each computer/date/program to be a SINGLE LINE in the CSV file. Each computer would therefore have multiple lines in the file, one per program. It would then be super easy to do what you're asking – and super easy to read those back in, filter for a specific computer's programs, and so on. This is called _normalization_ in the database world, and pretty much all the tools you have at your disposal are designed to work that way – so life would be much more enjoyable ;).

    What do you think?

    • #73081

      Leo Bounds
      Participant

      Thanks for the detailed response Don. I'm rethinking it and I may want to try some other options. It's not as important to have the ability to sort the data as it is to make sure I have collected reliable output into a file.

  • #73019

    Curtis Smith
    Participant

    Don is, of course, very accurate in his response and I agree with him completely; however, you should be able to get the results you are asking for by using the join operator. I don't think it's the right thing to do as Don has explained, but it can be done.

    IE.

    # get files to process each .xml file held into $filenames
    $fileNames = Get-ChildItem -Path C:\test1 -recurse -Include compat*.xml
    
    # For each loop to process each .xml file held in $FileNames
    ForEach ($name in $FileNames.Name)
    
    {
    [XML]$results = get-content $name
    
    # gets program names that are available from compatreport, then adds lines to .csv
    ($results.compatreport.programs.program.name -join ",") | add-content C:\test1\AddedContent.csv
    }
    
    • #73084

      Leo Bounds
      Participant

      Thank you Curtis for posting the code modification. I will have a look at it. I still may try something different as was suggested but what you shared was helpful. I'm still learning as I go with PowerShell when the need to use it comes up.

You must be logged in to reply to this topic.