Need a Powershell Parse of some HTML code
I have an HTML similar type file. The file gets generated from an app so its not exactly HTML, but I need all the values exported into an csv file.
The file has a Header section and detail section. I don't mind having all the data into 1 table if makes it easier.
Below I have included sample file to parse and small Powershell script the only thing I can do with Powershell i an still too green. Tried using the HTMLAgilty pack and was unsuccessful.
Here are the column details:
Header Info
codebase, instance_id,guid, id, name, version, registrar, risk_index, force_start, locale_id, template_version, status, score, date, source_object_type,source_
loggedin_userid, patient_id, patientname, allow_void,
--Detail Rows
SKIPPED_HISTORY,PAGE id, flush_concepts, update_date, valid,QUESTION id, Score, name, value
Sample2.html
GetHTMLData.txt
The file has a Header section and detail section. I don't mind having all the data into 1 table if makes it easier.
Below I have included sample file to parse and small Powershell script the only thing I can do with Powershell i an still too green. Tried using the HTMLAgilty pack and was unsuccessful.
Here are the column details:
Header Info
codebase, instance_id,guid, id, name, version, registrar, risk_index, force_start, locale_id, template_version, status, score, date, source_object_type,source_
loggedin_userid, patient_id, patientname, allow_void,
--Detail Rows
SKIPPED_HISTORY,PAGE id, flush_concepts, update_date, valid,QUESTION id, Score, name, value
Sample2.html
GetHTMLData.txt
Last Comment
ASKER
once its xml Content any good tutorials on how to extract data from this variable.
And I also see you are passing an xml file. Could I just rename the file below as xml? Does not seem to have proper tags?
And I also see you are passing an xml file. Could I just rename the file below as xml? Does not seem to have proper tags?
About halfway down this post is a good overview of how navigating XML objects works:
https://blogs.technet.com/b/heyscriptingguy/archive/2013/04/01/working-with-xml.aspx
https://blogs.technet.com/b/heyscriptingguy/archive/2013/04/01/working-with-xml.aspx
@ltorres321
What would you like your CSV file to look like, given this input? I concur with the assertion that you are working with XML data.
What would you like your CSV file to look like, given this input? I concur with the assertion that you are working with XML data.
Another way to get at (parse) the question data would be to apply a regular expression pattern to the contents of the XML file. The following pattern worked for the question data in the file you posted.
<QUESTION id="(\d+)" Score="(\d+)" name="(.*?)" (?:value="){0,1}(.*?)(?:" ){0,1}/>
ASKER
aikimark your line of code where do I incorporate it into my code?
where do I incorporate it into my code?Please answer my prior question about what your output needs to look like?
ASKER
The out needs to in a table Format so I can import into a SQL table
Using my method, it would be fairly simple:
[xml]$xml = Get-Content C:\tmp\test.xml
$xml.RESULTSCACHE | export-csv test.csv
@Jason
I don't think that CSV file is what ltorres321 had in mind.
You posted an elegant PS script, however. It is a bit too simple for the detail I expect is needed.
I don't think that CSV file is what ltorres321 had in mind.
You posted an elegant PS script, however. It is a bit too simple for the detail I expect is needed.
The out needs to in a table Format so I can import into a SQL table
Please post a couple of lines of CSV data from that XML file. Do this editing by hand.
I'll be interested to see what kind of table ltorres321 would prefer to use to import into SQL if it's not CSV... seems pretty obvious, but I could be wrong!
Yes. If you have the data in memory, why persist it to CSV? Just pump it directly into SQL Server.
However, if the import process is SSIS and the data gets validated, merged or split, I can see a reason why the existing SSIS code might be retained.
However, if the import process is SSIS and the data gets validated, merged or split, I can see a reason why the existing SSIS code might be retained.
ASKER
There is Header data and detail data in this file.
Jason Ryberg : as per your code that actually returns the Header data
which is between <RESULTSCACHE and ends at the <SKIPPED_HISTORY value=""/>
There is more data after that that is the Line Detail for this header.
As for CSV it was just to make it easier to get into the database. I was going to use SSIS to move move that data into tables but if Powershell can do it then so be it as well.
Jason Ryberg : as per your code that actually returns the Header data
which is between <RESULTSCACHE and ends at the <SKIPPED_HISTORY value=""/>
There is more data after that that is the Line Detail for this header.
As for CSV it was just to make it easier to get into the database. I was going to use SSIS to move move that data into tables but if Powershell can do it then so be it as well.
ASKER
Jason your code is nice and very clean but how about the other fields?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You'll need to separate RESULTSCACHE data and QUESTION data, so I would use this approach:
[xml] $xml = Get-Content C:\tmp\test.xml
$xml.ResultsCache | export-csv -NoType C:\tmp\Results.csv
$xml.ResultsCache.Page | % { $_.Question } | export-csv -NoType C:\Tmp\Questions.csv
Note that Jason's approach only works starting with PS 3. The equivalent code by naming the properties (as in my code snippet) is
$xml.ResultsCache.Page.Question | export-csv -NoType c:\temp\Questions.csv
ASKER
The data just needs to go into a table table. That's just rows and Columns.
Qlemo: that's impressive. Now how do I include the page number as well in the data.
And is there an ID I can create on the fly so when I put this into a table I can tie the header to the details? Hope that makes sense.
Thanks for the help. I thought this was going to be couple hundred lines of code you did most if it in 3!
Qlemo: that's impressive. Now how do I include the page number as well in the data.
And is there an ID I can create on the fly so when I put this into a table I can tie the header to the details? Hope that makes sense.
Thanks for the help. I thought this was going to be couple hundred lines of code you did most if it in 3!
ASKER
FYI PS 3 is fine I am using that.
As I also Compare data to what you are producinf Qlemo;
I see this strings of data is missing
Page ID
Flush_Concepts
Update_Date
valid
<PAGE id="1" flush_concepts="0" update_date="7/25/2012 8:43:58 AM" valid="1">
As I also Compare data to what you are producinf Qlemo;
I see this strings of data is missing
Page ID
Flush_Concepts
Update_Date
valid
<PAGE id="1" flush_concepts="0" update_date="7/25/2012 8:43:58 AM" valid="1">
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Wow, Excellent I will close this here for now. I will try to get this data into SQL tables. If I cant I will post another question to be fair. Thanks to all for the exceptional HELP! I need every bit of it.
.NET Programming
The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.
137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
Once the XML object is loaded, you can traverse the object similarly to other PS objects!