convert a batch of xml to a csv file

Welcome Forums General PowerShell Q&A convert a batch of xml to a csv file

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 week, 4 days ago.

  • Author
    Posts
  • #113380

    Participant
    Points: 0
    Rank: Member

    I only need Powershell for this task and only have experience with coding in Stata – help would be much appreciated!
    I have a large number of xml files where each one is a single record from a household survey, so it has variable names and responses from one person.
    I need to convert those files to a csv file. From reading other posts I understand that I need to loop over the files, but I don't know how to tell Powershell that I want all responses to be exported.
    I've tried something like this:
    `$items = Get-ChildItem C:\Users\fs59\Desktop\temp\* -Include *.xml
    foreach ($item in $items) {
    $xml = [XML](Get-Content $item)
    $xml | Export-Csv -Path C:\Users\fs59\Desktop\temp\myoutput.csv -NoTypeInformation -Append
    } `
    This runs without an error but the output does not contain the survey responses.
    The survey has a lot of 'routing' so if people give a certain answer, they are routed to a relevant follow-up question.

    UPDATE:

    UPDATE: Hi, I have managed to send the results to csv but the output is one long column. Is there a way of having the output in several rows, one per each xml file?

    # Get all XML files
    $items = Get-ChildItem C:\Users\user\Desktop\test\* -Include *.xml
    
    # Loop over them and append them to the document
    foreach ($item in $items) {
        $inputFile = [XML](Get-Content $item) #load xml document    
    
    #export xml as csv
    $inputFile.TT_client_survey_2018_v1.ChildNodes | Export-Csv -Path    C:\Users\user\Desktop\test\myoutput12.csv -NoTypeInformation -Append 
    }
  • #113489

    Keymaster
    Points: 1
    Rank: Member

    Xml is a hierarchical data format; CSV is a flat-file. So this is always going to be problematic for you, as it's like trying to dump an entire multi-table database into one file. Also, PowerShell isn't a magical file format converting machine (I wish!), so if you want to use it, you're going to have to go through a lot more work. Potentially a LOT LOT more work if the XML isn't extremely simplistic. You'll have to manually enumerate the properties you want, construct a single output object, and pipe THAT to Export-CSV. Otherwise Export-CSV just won't know what to do, which is why you're getting what you are.

    Frankly, loading up SQL Server Express (which is free, get the one that includes the admin tools) and using SQL Server Integration Services might be easier. It's actually designed for this, although you're still going to have to tell it how to map the XML to a flat file.

  • #113597

    Participant
    Points: 0
    Rank: Member

    Thank you very much @Don! I have ended up using one of the commercial file converters – not ideal as I like doing things myself (and don't give up easily) but it does the job well and fast. Best, F

You must be logged in to reply to this topic.