Source language: Translate to:

Import XML

Post your suggestions for future versions of NeoBook

Moderator: Neosoft Support

Import XML

Postby dpayer » Fri Jan 02, 2009 1:32 pm

I have a project to do several actions based on a database that is in XML format. I will scan hundreds/thousand of XML files in a mail server to determine several attributes of the mail in various folders.

Right now I am working out the parsing of the files based on the Schema used. It is all string processing and parsing of parsed values and then searching for specific content within the parsed--->parsed--->parsed content.

It would be very helpful to have the ability to IMPORT XML into DBPro. I see that we can EXPORT into that format but that doesn't help me.

Here is a sample of an XML element I am dealing with:
Code: Select all
- <ELEMENT ID="239B79A86B2F4C7283DEC41CE98F9272.MAI" UID="1252">
<MESSAGEDATE>2009-01-02T14:32:05</MESSAGEDATE>
<MODIFIEDDATE>975337049</MODIFIEDDATE>
<IMPORTANCE>3</IMPORTANCE>
<FLAGSTATUS>\RECENT</FLAGSTATUS>
<ATTACHMENTS>0</ATTACHMENTS>
<FROM>"Walmart Rewards Center" <reply@generalrealism.com></FROM>
<TO>me@you.com</TO>
<CC />
<SUBJECT>[S] Walmart Rewards Confirmation: #653188</SUBJECT>
<RECEIVED>2009-01-02T14:50:48</RECEIVED>
<READ>0</READ>
<STATE>0</STATE>
<CLASS />
<SIZE>1978</SIZE>
</ELEMENT>


And each XML file may have 100 - 5000 elements. I have many hundreds of XML files to scan on a daily basis.

I am going to continue on my parsing method for this project but the many steps make for slow processing. If you are looking for enhancements to DBPro, here is one I think would be beneficial.

Thanks for listening!

David P

If you can point me to a plugin or a faster process that could be used as a subroutine or a function, I would be interested.
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby Neosoft Support » Fri Jan 02, 2009 8:36 pm

I'll add this to the list of things to consider for the next DBPro update. In the mean time you might try converting the XML data to CSV or importing the XML using SQL. I found several things that might be helpful by searching Google for "XML to CSV" or "SQL import XML".
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby HPW » Sat Jan 03, 2009 2:28 am

You may try newLISP and its xml-parse command.

It has problems with your:

<FROM>"Walmart Rewards Center" <reply@generalrealism.com></FROM>

but when I make this:

<FROM>"Walmart Rewards Center" reply@generalrealism.com</FROM>

Not sure if your string is valid XML.

I tried this newLISP-code:
Code: Select all
(setq xmlstring (read-file "C:/Users/Hans-Peter Wickern/Documents/xml_test.xml"))
(setq xmllist (xml-parse xmlstring (+ 1 2 4)))
(save "C:/Users/Hans-Peter Wickern/Documents/xml_test1.xml" 'xmllist)
(xml-error)


Result:
(set 'xmllist '(
("ELEMENT" "ELEMENT" (("ID" "239B79A86B2F4C7283DEC41CE98F9272.MAI") ("UID" "1252"))
(("ELEMENT" "MESSAGEDATE" (("TEXT" "2009-01-02T14:32:05")))
("ELEMENT" "MODIFIEDDATE" (("TEXT" "975337049")))
("ELEMENT" "IMPORTANCE" (("TEXT" "3")))
("ELEMENT" "FLAGSTATUS" (("TEXT" "\\RECENT")))
("ELEMENT" "ATTACHMENTS" (("TEXT" "0")))
("ELEMENT" "FROM" (("TEXT" "\"Walmart Rewards Center\" reply@generalrealism.com")))
("ELEMENT" "TO" (("TEXT" "me@you.com")))
("ELEMENT" "CC" ())
("ELEMENT" "SUBJECT" (("TEXT" "[S] Walmart Rewards Confirmation: #653188")))
("ELEMENT" "RECEIVED" (("TEXT" "2009-01-02T14:50:48")))
("ELEMENT" "READ" (("TEXT" "0")))
("ELEMENT" "STATE" (("TEXT" "0")))
("ELEMENT" "CLASS" ())
("ELEMENT" "SIZE" (("TEXT" "1978")))))))


This list can be further processed.
Hans-Peter
User avatar
HPW
 
Posts: 2520
Joined: Fri Apr 01, 2005 11:24 pm
Location: Germany

Postby HPW » Sat Jan 03, 2009 2:39 am

Using this you get it even more compact:
Code: Select all
(setq xmlstring (read-file "C:/Users/Hans-Peter Wickern/Documents/xml_test.xml"))
(xml-type-tags nil nil nil nil)
(setq xmllist (xml-parse xmlstring (+ 1 2 8)))
(save "C:/Users/Hans-Peter Wickern/Documents/xml_test1.xml" 'xmllist)
(xml-error)


(set 'xmllist '(
(ELEMENT ((ID "239B79A86B2F4C7283DEC41CE98F9272.MAI") (UID "1252"))
(MESSAGEDATE "2009-01-02T14:32:05")
(MODIFIEDDATE "975337049")
(IMPORTANCE "3")
(FLAGSTATUS "\\RECENT")
(ATTACHMENTS "0")
(FROM "\"Walmart Rewards Center\" reply@generalrealism.com")
(TO "me@you.com")
(CC)
(SUBJECT "[S] Walmart Rewards Confirmation: #653188")
(RECEIVED "2009-01-02T14:50:48")
(READ "0")
(STATE "0")
(CLASS)
(SIZE "1978"))))
Hans-Peter
User avatar
HPW
 
Posts: 2520
Joined: Fri Apr 01, 2005 11:24 pm
Location: Germany

Postby dpayer » Sat Jan 03, 2009 6:47 am

HPW wrote:You may try newLISP and its xml-parse command.

It has problems with your:

<FROM>"Walmart Rewards Center" <reply@generalrealism.com></FROM>

but when I make this:

<FROM>"Walmart Rewards Center" reply@generalrealism.com</FROM>

Not sure if your string is valid XML.


This list can be further processed.


Thank you very much for your time spent to look at this issue. Once again, you have already dealt with something that many of us haven't.

I will look into your plugin and try it out.

RE: valid XML, I made an error in my post. I viewed the XML in a browser and did a copy paste of the content. I have since realized that the browser does its own processing of the content, adding characters before each element and it takes escaped characters and displays them for visual purposes. The file I am working with did in fact properly use the <reply@generalrealism.com> format.

Thank you Hans-Peter for offering your plugins freely to this community.

David P
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

Postby HPW » Sat Jan 03, 2009 7:35 am

Thank you Hans-Peter for offering your plugins freely to this community.


In this case of newLISP you need newLISP itself from the www.newLISP.org web site.
Since it is GPL-software, I had taken the DLL out of the plugin-zip.
And the thanks for newLISP has to go to the creator Lutz Mueller.

For this job you can either use the plugin and the DLL or the standalone-EXE and the neobook run command.
Hans-Peter
User avatar
HPW
 
Posts: 2520
Joined: Fri Apr 01, 2005 11:24 pm
Location: Germany

Postby dpayer » Sat Jan 03, 2009 2:28 pm

Neosoft Support wrote:I'll add this to the list of things to consider for the next DBPro update. In the mean time you might try converting the XML data to CSV or importing the XML using SQL. I found several things that might be helpful by searching Google for "XML to CSV" or "SQL import XML".


Thanks for the suggestion.

I did look at Hans-Peter's NewLisp XML parser and it is something I will use in the future. I also found a free command line converter here:

http://a7soft.com/xml2csv.html

It is fast and gives a format that is readily usable to create a NB array.

I appreciate the input.

David P.
User avatar
dpayer
 
Posts: 1382
Joined: Mon Apr 11, 2005 5:55 am
Location: Iowa - USA

convert xml to 'other'

Postby Capn » Fri Apr 24, 2009 9:36 am

Not sure if this will help in all given situations but here's a little vbscript that might help some...

Code: Select all
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
 ("output.txt", ForAppending, True)

Dim objXMLDoc
Dim NodeList
Dim Node
Dim ChildNode
Dim NodeAttr

Set objXMLDoc = CreateObject("Microsoft.XMLDOM")
objXMLDoc.async = False
objXMLDoc.load("C:\path\filename.xml")

For Each Node In objXMLDoc.documentElement.childNodes
   For Each NodeAttr In Node.Attributes
      objTextFile.WriteLine NodeAttr.Name & ": " & NodeAttr.Value
   Next
   For Each ChildNode In Node.ChildNodes
      objTextFile.WriteLine ChildNode.NodeName & ": " & ChildNode.Text
   Next
objTextFile.WriteLine "-------------"
Next

objTextFile.Close


* Change "output.txt" to the file you want to create
* Change the input file "C:\path\filename.xml" to the file you want to convert...

The script uses Microsloths MSXML DLL to convert your xml input file into a somewhat formatted text file. You should tweak the script to produce your own output format.

jim
Capn
 
Posts: 23
Joined: Thu Dec 13, 2007 11:58 am


Return to NeoBook Suggestions

Who is online

Users browsing this forum: No registered users and 1 guest