[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

Scraping specific data within an XML document

Working on scraping specific governing data from xml. Currently got it working somewhat exporting certain data tags to a csv file. However, it errors out stating:.

The input line is too long.
The syntax of the command is incorrect.

Open in new window


Here is the script:
@echo off
setlocal enabledelayedexpansion
set inFile="C:\temp\CFR-2016-title40-vol17.xml"
set outFile="C:\temp\output.csv"
set req_tags=SECTNO SUBJECT P E
set outLine=
echo SECTNO,SUBJECT,P,E > %outFile% 
for %%a in (%inFile%) do (
  for %%c in (%req_tags%) do (
    set search_tag=%%c
    for /f "tokens=2 delims=><  " %%b in ('type "%%a" ^|findstr /i !search_tag!' ) do (
      if [%%b] NEQ [] (
        rem we don't want to match /BSN
        if [%%b] NEQ [/BSN] (
          set outline=!outline!%%b,
        )
      )
    )
  )
)
rem output the values
rem remove trailing ,
set outline=%outline:~0,-1%
echo %outline%>>"%outFile%"
endlocal

Open in new window


Attached is the XML file I am trying to grab data from.
CFR-2016-title40-vol17.xml
0
ntr2def
Asked:
ntr2def
  • 10
  • 9
1 Solution
 
ntr2defAuthor Commented:
So I Attempted to do this in PowerShell:

PS C:\temp> [xml]$xmlDocument = get-content c:\temp\CFR-2016-title40-vol17.xml
PS C:\temp> $XmlDocument.cfrdoc.title.chapter.subchap.part.section

Open in new window


The output became:
SECTNO     SUBJECT                                              P
------     -------                                              -
§ 64.1  Definitions.                                         {The following definitions apply to this part. Excep...
§ 64.2  Applicability.                                       {P, (1) The unit is subject to an emission limitatio...
§ 64.3  Monitoring design criteria.                          {P, (1) The owner or operator shall design the monit...
§ 64.4  Submittal requirements.                              {(a) The owner or operator shall submit to the permi...
§ 64.5  Deadlines for submittals.                            {P, (1) On or after April 20, 1998, the owner or ope...
§ 64.6  Approval of monitoring.                              {(a) Based on an application that includes the infor...
§ 64.7  Operation of approved monitoring.                    {P, P, P, P...}
§ 64.8  Quality improvement plan (QIP) requirements.         {(a) Based on the results of a determination made un...
§ 64.9  Reporting and recordkeeping requirements.            {P, (2) A report for monitoring under this part shal...
§ 64.10 Savings provisions.                                  {(a) Nothing in this part shall:, P, (2) Restrict or...
§ 70.1  Program overview.                                    {P, (b) All sources subject to these regulations sha...
§ 70.2  Definitions.                                         {The following definitions apply to part 70. Except ...
§ 70.3  Applicability.                                       {P, (1) Any major source;, (2) Any source, including...
§ 70.4  State program submittals and transition.             {P, P, (1) A complete program description describing...
§ 70.5  Permit applications.                                 {P, P, P, (iii) For purposes of permit renewal, a ti...
§ 70.6  Permit content.                                      {P, (1) Emissions limitations and standards, includi...
§ 70.7  Permit issuance, renewal, reopenings, and revisions. {P, (i) The permitting authority has received a comp...
§ 70.8  Permit review by EPA and affected States.            {P, (2) The Administrator may waive the requirements...
§ 70.9  Fee determination and certification.                 {P, P, (i) Preparing generally applicable regulation...
§ 70.10 Federal oversight and sanctions.                     {P, (i) At any time the Administrator may apply any ...
§ 70.11 Requirements for enforcement authority.              {All programs to be approved under this part must co...

Open in new window


However, when I try export that data to CSV i now get an error:

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At line:1 char:58
+ ... subchap.part.section | export-csv .\testoutput.csv -notypeinformation
+                            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCo
   mmand

Open in new window

0
 
aikimarkCommented:
Please double check your Powershell script.  I don't see the same results when I run it against the xml file you posted.
0
 
ntr2defAuthor Commented:
How are you exporting the information? I use:

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ntr2defAuthor Commented:
$XmlDocument.cfrdoc.title.chapter.subchap.part.section | export-csv .\testoutput.csv -notypeinformation

Open in new window

0
 
aikimarkCommented:
When I use the $XmlDocument.cfrdoc.title.chapter.subchap.part.section variable, it doesn't display the thing that you showed in your earlier comment.
0
 
ntr2defAuthor Commented:
what do you get? and are you exporting to csv?
0
 
aikimarkCommented:
I'm not there yet.  What do you need the CSV to look like?  There are lots of paragraphs in a section, Do you want one CSV line per paragraph?  Do you want to concatenate the paragraphs into a single column/field in the CSV?
0
 
aikimarkCommented:
CSV can not easily represent hierarchical or encapsulated data
0
 
ntr2defAuthor Commented:
It would be nice to concatenate the paragraph info into on cell but it sounds like I'll be doing some macro work on top of the powershell script?
0
 
aikimarkCommented:
If you concatenate the paragraphs, how should they be delimited?  Since CSV lines end with a CRLF, you will be challenged to concatenate the paragraphs into a single block of text without sacrificing readability or breaking the CSV
0
 
ntr2defAuthor Commented:
Well now that you put it that way I think one paragraph per cell would be best and the manipulation of the data could be done after of the fact. It's being able to export that data as cleanly as possible which I am have trouble with.
0
 
aikimarkCommented:
How are your PS skills?
0
 
ntr2defAuthor Commented:
I would like to think I'm rather decent, I have just not worked a ton with XML files.
0
 
aikimarkCommented:
Please test this:
function get-sectiondata($parmSection){
    $o=@()
    $parmsection | %{
        $sectno = $_.sectno
        $subject = $_.subject
        $_.P | %{
            try{
            if ($_.gettype().name -eq "String"){
                #($sectno, $subject, $_) | ft -autosize
                $newobj= new-object pscustomobject -property @{'sectno'=$sectno;'subject'=$subject;'P'=$_}
                $o += $newobj
                }
                }
            catch{}
            }
        }

return $o
}

[xml]$xmlDocument = get-content c:\temp\CFR-2016-title40-vol17.xml
$outputArray=@()
$sectno=""
$subject=""
$XmlDocument.cfrdoc.title.chapter.subchap.part |
    %{
      if (($_ | gm -MemberType property -name subpart) -ne $null){
          $_.subpart | %{
                $outputArray+=get-sectiondata($_.section)
                }
          }
        
      if (($_ | gm -MemberType property -name section) -ne $null){
          $_ | %{
                $outputArray+=get-sectiondata($_.section)
                }
          }
      }
    
$outputArray | Export-Csv -NoTypeInformation -Path .\testoutput.csv

Open in new window

1
 
ntr2defAuthor Commented:
I have to say the output is different then what I envisioned but it was done very well. I rather like the output much better, its easy to read and follow.
0
 
aikimarkCommented:
I skipped the E elements.  You will need to tweak the PS script if you need those.
0
 
ntr2defAuthor Commented:
no worries. You gave me a path that I can follow and tweak to my liking. I again thanks for your help on this.
0
 
aikimarkCommented:
I just spotted and corrected a line.  I was updating $o and have changed it to $outputArray in the posted code
0
 
aikimarkCommented:
I encountered some Null values during my testing and added another check for null to make it more resilient.
function get-sectiondata($parmSection){

    $o=@()
    $parmsection | %{
        $sectno = $_.sectno
        $subject = $_.subject
        $_.P | %{
            try{
                if ($_.gettype().name -eq "String"){
                    #($sectno, $subject, $_) | ft -autosize
                    $newobj= new-object pscustomobject -property @{'sectno'=$sectno;'subject'=$subject;'P'=$_}
                    $o += $newobj
                    }
                }
            catch{}
            }
        }

return $o
}

[xml]$xmlDocument = get-content c:\test\CFR-2016-title40-vol17.xml
$outputArray=@()
$sectno=""
$subject=""
$XmlDocument.cfrdoc.title.chapter.subchap.part |
    %{
      if (($_ | gm -MemberType property -name subpart) -ne $null){
        #break
          $_.subpart | %{
                $r=get-sectiondata($_.section)
                if ($r -ne $null){
                    $outputArray+=$r   #get-sectiondata($_.section)
                }
                }
          }
        
      if (($_ | gm -MemberType property -name section) -ne $null){
          $_ | %{
                $r=get-sectiondata($_.section)
                if ($r -ne $null){
                    $outputArray+=$r   #get-sectiondata($_.section)
                }                
                }
          }
      }
    
$outputArray | Export-Csv -NoTypeInformation -Path .\CFR-2016-title40-vol17.csv

Open in new window

1

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now