none
How to read data from infopath form and export into csv file

    Question

  • Hi All,

    I have a form library where more than 1000 infopath form available. Could see that every infopath form is having data with repeating table .

    Now my client need all the form wise data into the excel file , for their internal purpose. 

    I can't use any visual studio solutions as per their policy.so need some solution with powershell /Workflow/any other solution with client object model

     Please provide me any suggestions/links.

    Thanks in advance.


    Subhash



    • Edited by Kris_sp2013 Thursday, December 29, 2016 4:36 AM
    Thursday, December 29, 2016 4:34 AM

Answers

  • Hi Lisa

    Thanks for the Reply. There were around 1000 infopath forms data needs to be exported into excel and each infopath table having repeating table with multiple information. 

    With below script I'm able to export all the infopath forms data into excel. But not sure how to get attachment from every infopath form.

    can someone guide me to get the attachement with powershell?

     

    $Path = "D:\support\Infopath"
    $Files = Get-ChildItem $Path | where {$_.extension -eq ".xml"}
    $outputReport = "D:\support\Infopath\Results.csv"
    $UserDataCollection = @()
    foreach($file in $files)
    {
    write-host $file.fullname   
     $xml = New-Object XML
        $xml.PreserveWhitespace = $true
        $xml.Load($file.fullname)

    $count = $xml.myFields.group1.group2.Count

    write-host count: $count

    if($count -lt 1)
    {
    write-host $file.fullname
    write-host $xml.myFields.Name
    write-host $xml.myFields.Justification
    write-host I value: $count
    write-host $xml.myFields.group1.group2.ItemDesc
    write-host $xml.myFields.group1.group2.Cost
    #Array  to export Data
    $Output =New-Object -TypeName PSObject -Property @{
            FilePath = $file.fullname
            Name = $xml.myFields.Name
            Title = $xml.myFields.Justification
            RepTab_count =$count
            ItemDesc = $xml.myFields.group1.group2.ItemDesc
            Cost = $xml.myFields.group1.group2.Cost
            } | Select-Object FilePath,Name,Title,RepTab_count,ItemDesc,Cost

     $UserDataCollection += $Output 
    }

    else
    {
    for ($i=0;$i -lt $count; $i++) 
    {
    write-host $file.fullname
    write-host $xml.myFields.Name
    write-host $xml.myFields.Justification
    write-host I value: $i
    write-host $xml.myFields.group1.group2[$i].ItemDesc
    write-host $xml.myFields.group1.group2[$i].Cost
    #Array  to export Data

    $Output =New-Object -TypeName PSObject -Property @{
            FilePath = $file.fullname
            Name = $xml.myFields.Name
            Title = $xml.myFields.Justification
            RepTab_count = $i
            ItemDesc = $xml.myFields.group1.group2[$i].ItemDesc
            Cost = $xml.myFields.group1.group2[$i].Cost
            } | Select-Object FilePath,Name,Title,RepTab_count,ItemDesc,Cost

     $UserDataCollection += $Output 
    }
    }
    }
     $UserDataCollection | Export-Csv -LiteralPath $OutputReport -NoTypeInformation

    • Edited by Kris_sp2013 Saturday, December 31, 2016 10:14 AM
    • Marked as answer by Kris_sp2013 Monday, February 6, 2017 8:14 AM
    Friday, December 30, 2016 3:19 PM

All replies

  • Hi Kris_sp2013,

    From your description, you want to export InfoPath form with repeating table to csv file.

    You can export them to excel, then from Excel you can save as CSV.

    Refer to the following article about how to send data from an entire repeating table control on an InfoPath form to an Excel workbook:

    https://infopath.wordpress.com/2013/01/14/submit-infopath-repeating-table-excel-2010-sharepoint-2010-codeless/

    http://www.bizsupportonline.net/infopath2010/videos/send-infopath-repeating-table-excel-workbook.htm

    Best Regards,

    Lisa Chen


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, December 30, 2016 8:55 AM
    Moderator
  • Hi Lisa

    Thanks for the Reply. There were around 1000 infopath forms data needs to be exported into excel and each infopath table having repeating table with multiple information. 

    With below script I'm able to export all the infopath forms data into excel. But not sure how to get attachment from every infopath form.

    can someone guide me to get the attachement with powershell?

     

    $Path = "D:\support\Infopath"
    $Files = Get-ChildItem $Path | where {$_.extension -eq ".xml"}
    $outputReport = "D:\support\Infopath\Results.csv"
    $UserDataCollection = @()
    foreach($file in $files)
    {
    write-host $file.fullname   
     $xml = New-Object XML
        $xml.PreserveWhitespace = $true
        $xml.Load($file.fullname)

    $count = $xml.myFields.group1.group2.Count

    write-host count: $count

    if($count -lt 1)
    {
    write-host $file.fullname
    write-host $xml.myFields.Name
    write-host $xml.myFields.Justification
    write-host I value: $count
    write-host $xml.myFields.group1.group2.ItemDesc
    write-host $xml.myFields.group1.group2.Cost
    #Array  to export Data
    $Output =New-Object -TypeName PSObject -Property @{
            FilePath = $file.fullname
            Name = $xml.myFields.Name
            Title = $xml.myFields.Justification
            RepTab_count =$count
            ItemDesc = $xml.myFields.group1.group2.ItemDesc
            Cost = $xml.myFields.group1.group2.Cost
            } | Select-Object FilePath,Name,Title,RepTab_count,ItemDesc,Cost

     $UserDataCollection += $Output 
    }

    else
    {
    for ($i=0;$i -lt $count; $i++) 
    {
    write-host $file.fullname
    write-host $xml.myFields.Name
    write-host $xml.myFields.Justification
    write-host I value: $i
    write-host $xml.myFields.group1.group2[$i].ItemDesc
    write-host $xml.myFields.group1.group2[$i].Cost
    #Array  to export Data

    $Output =New-Object -TypeName PSObject -Property @{
            FilePath = $file.fullname
            Name = $xml.myFields.Name
            Title = $xml.myFields.Justification
            RepTab_count = $i
            ItemDesc = $xml.myFields.group1.group2[$i].ItemDesc
            Cost = $xml.myFields.group1.group2[$i].Cost
            } | Select-Object FilePath,Name,Title,RepTab_count,ItemDesc,Cost

     $UserDataCollection += $Output 
    }
    }
    }
     $UserDataCollection | Export-Csv -LiteralPath $OutputReport -NoTypeInformation

    • Edited by Kris_sp2013 Saturday, December 31, 2016 10:14 AM
    • Marked as answer by Kris_sp2013 Monday, February 6, 2017 8:14 AM
    Friday, December 30, 2016 3:19 PM