How to parse large (6 gb) xml file in python and save some of the elements in a list or table?

Viewed 69

I'm trying to parse a xml file (6 gb) using now powershell and save the elements in a txt file. I want to save into a txt file the concatenation of the First name, middle name and surname, in order to have a full name if it is available and the date, of a specific date type.

    <Person id="44855" action="chg" date="26-Aug-2022">
    <Gender>Male</Gender>
    <ActiveStatus>Active</ActiveStatus>
    <Deceased>No</Deceased>
    <NameDetails>
     <Name NameType="Primary Name">
       <NameValue>
          <FirstName>*****</FirstName>
          <Surname>******</Surname>
       </NameValue>
     </Name>
     <Name NameType="Low Quality AKA">
      <NameValue>
        <FirstName>****</FirstName>
      </NameValue>
     </Name>
     <Name NameType="Spelling Variation">
      <NameValue>
       <FirstName>*****</FirstName>
       <Surname>****</Surname>
      </NameValue>
     </Name>
    </NameDetails>
<DateDetails>
  <Date DateType ="Inactive as of">
   <Datevalue Day = "12" Month = "Oct" Year = "2019">
  </Date>
  <Date DateType = "Date of Birth">
   <Datevalue Day = "1" Month = "Jan" Year = "1980">
  </Date>

I've tried to parse it using xml.etree.ElementTree parse but i got memory error, i've tried pandas read_xml, but got memory error. I don't have permission to install lxml, so i cant use lxml.etree. This is my first time uploading a question, and im not sure how to do it correctly, but feel free to ask any question, I really need help here.

The code i have so far is this (it was reused from a similar question on thi plataform)

using assembly System.Xml
using assembly System.Xml.Linq 

$Filename = "C:\Users\c096830\Desktop\PFA2_202208262200_D.xml"

$reader = [System.Xml.XmlReader]::Create($Filename)
Write-Host "O script está a executar..." 
while($reader.EOF -eq $False)
{
    if($reader.Name -ne "Person")
    {
       $reader.ReadToFollowing("Person")
    }
    if($reader.EOF -eq $False)
    {
       $xPerson = [System.Xml.Linq.XElement]::ReadFrom($reader)
       $Names = $xPerson.Descendants("Name")
       $Dates = $xPerson.Descendants("DateDetails")
       $activestatus = $xPerson.Descendants("ActiveStatus").Value
       if($activestatus -eq 'Inactive')
       {
        foreach($name in $Names)
        {
              $nameType = $name.Attribute("NameType").Value
              if($nameType -ne "Primary Name")
              {   $namevalue = $name.Elements("NameValue")
                  foreach($name in $namevalue)
                  {
                      $firstName = $name.Descendants("FirstName").Value
                      $middleName = $name.Descendants("MiddleName").Value
                      $surName = $name.Descendants("Surname").Value
                      $fullname = $firstname + " " +  $middleName+ " " + $surName                    
                  }
               }else{
                  $firstName = $name.Descendants("FirstName").Value
                  $middleName = $name.Descendants("MiddleName").Value
                  $surName = $name.Descendants("Surname").Value
                  $fullname = $firstname + " " +  $middleName+ " " + $surName 

               }
              
         } 
          foreach($date in $Dates)
          {
            if($date.Attributes("DateType").Value -like "Inactive as of")
               
               {
                $datevalue = $date.Descendants("DateValue")
                $day = $datevalue.Attribute("Day").Value
                $month = $datevalue.Attribute("Month").Value
                $year = $datevalue.Attribute("Year").Value
                $fulldate = $day + "/" + $month + "/"+"year"
                $namedate = $fullname, $fulldate|
                Out-File -FilePath C:\Users\... -Append
               }
           }
       }

    } 
}
 
2 Answers

I think at this size it is the easiest to write a custom XML-processor, xml.entree seems really inefficient for larger files.
Here is an article that shows how this could work.

Try following power shell script

using assembly System.Xml
using assembly System.Xml.Linq 

$Filename = "C:\temp\test.xml"

$reader = [System.Xml.XmlReader]::Create($Filename)
Write-Host "O script está a executar..." 
while($reader.EOF -eq $False)
{
    if($reader.Name -ne "Person")
    {
       $reader.ReadToFollowing("Person")
    }
    if($reader.EOF -eq $False)
    {
       $xPerson = [System.Xml.Linq.XElement]::ReadFrom($reader)
       $Names = $xPerson.Descendants("Name")
       $Dates = $xPerson.Descendants("Date")
       $activestatus = $xPerson.Descendants("ActiveStatus").Value
       if($activestatus -eq 'Active')
#       if($activestatus -eq 'Inactive')
       {
        foreach($name in $Names)
        {
              $nameType = $name.Attribute("NameType").Value
              if($nameType -ne "Primary Name")
              {   $namevalue = $name.Elements("NameValue")
                  foreach($name in $namevalue)
                  {
                      $firstName = $name.Descendants("FirstName").Value
                      $middleName = $name.Descendants("MiddleName").Value
                      $surName = $name.Descendants("Surname").Value
                      $fullname = $firstname + " " +  $middleName+ " " + $surName                    
                  }
               }
               else
               {
                  $firstName = $name.Descendants("FirstName").Value
                  $middleName = $name.Descendants("MiddleName").Value
                  $surName = $name.Descendants("Surname").Value
                  $fullname = $firstname + " " +  $middleName+ " " + $surName 

               }
              
         } 
          foreach($date in $Dates)
          {
Write-Host "date"
            if($date.Attributes("DateType").Value -like "Inactive as of")
               
               {
                $datevalue = $date.Descendants("Datevalue")
                $day = $datevalue.Attribute("Day").Value
                $month = $datevalue.Attribute("Month").Value
                $year = $datevalue.Attribute("Year").Value
                $fulldate = $month + " " + $day + " " + $year
Write-Host "fulldate = " $fulldate
                $dateTime = [System.DateTime]::Parse($fulldate)
                #Out-File -FilePath C:\temp\test.txt -Append
                Write-Host "Date = "  $dateTime.ToString("MM-dd-yyyy") 
               }
           }
       }

    } 
}
Related