topic Re: open URL as text, import text as data table in Discussions

open URL as text, import text as data table

scottmpatt — Wed, 12 Jun 2013 15:55:37 GMT

Hello all, I am very new to scripting in JMP and I had some questions about my code.

Here's what I have. There is a data table I created called yield_production_5292013.jmp. In this data table I have a cloumn with a bunch of separate URL's that get sent to a website which in turn gives me an xml file with lots of data that JMP opens as text. What I would like to do is open each url in jmp then have a script to automatically import this text file as a new table.(this is compared to manually selecting file>import as data)

I know this seems odd to open a file as text then import it as a new table, however if I open the url output as a data table from the get go, the resulting table is unusable.

I have already written the script that I use after this text is imported as a data table, and it searches the appropriate column and row for the two data points that I need and copies them into the correct location in a new file.

Here is what I have so far, I know it isnt much but at least this opens up one url as a text file.

dt = data table ("yield_production_5292013");

i=1; // i have 1780 rows of urls to be opened, however i want to get the code running for a single line before I expand it

dt = current data table();

open(column("URL"));

What happens after I run this script is a file pops up called v4.xml

I want my code to then import this text as data.

Anybody know how to help????

Thanks for the help!

Re: open URL as text, import text as data table

scottmpatt — Wed, 12 Jun 2013 16:43:10 GMT

IMPORTANT EDIT!!!!!!!!

when I run the script that I posted, it doesnt open the XML output from the website as a texxt document, it actually copies and pastes that output as a new script file!!!!! If I were to continue manually I would still have to go file>import as data, however I dont want to do this because I would be stuck at my computer for days!

Is there anyone who can help me automate this process?

thanks!

Re: open URL as text, import text as data table

ms — Wed, 12 Jun 2013 20:04:03 GMT

The script editor and and the "open as text" window look the same.

I tried to open an xml file on a web server using your code but instead of open as text or table within imp my default xml editor was launched. Maybe that's beacause I use JMP on a Mac.

What happens if you try this?: open(column("URL")[i], html table(1));

If "pretending" it's a html-page in this way, the XML code opens in a a data table, at least for me.

But if you get the xml in a script window it's quite straight forward to fetch the content as a string and extract the pieces you need without going via a data table.

To get the text as a variable, try: xml = Window( "v4.xml" )[1] << get text;

Have a look at jsl's XML functions. It is possible to extract any value form a string of valid XML using them.

Re: open URL as text, import text as data table

scottmpatt — Wed, 12 Jun 2013 23:11:01 GMT

Thank you very much! So the first trick you showed had a null effect, however the get text function you showed did indeed copy and paste the output of that XML file into my log.

Time for my next question. Is there anyway to parse the data to get specific text from the log window? The script that I wrote to parse the text for specific data references the data table that JMP creates after i choose file->import as data. The entire goal of this would be for me to write a script that could automatically go through the URL opening process, paste the text into the log like it does now, search the log for a specfic item (its located in the same place everytime I open a new url) and paste that into a new jmp table.

Again, thank you very much @MS you have helped me chip away at this problem!

Re: open URL as text, import text as data table

ms — Thu, 13 Jun 2013 00:51:11 GMT

Don't bother with the log. With the command

xml = Window( "v4.xml" )[1] << get text;

the text is stored in the variable xml (can have any name) that is ready to use.

There are several ways to dig out specific items from strings. As it apparently is xml code the Parse XML function would be powerful. Or regex if the item (or surrounding text) always has a some unique pattern.

Hard to give specific tips without an example.

Re: open URL as text, import text as data table

scottmpatt — Thu, 13 Jun 2013 18:35:05 GMT

All I needed to do was specify what format for jmp to open my file!! dt = data table ("yield_production_5292013"); i=1; dt = current data table(); open(column("URL"), text); so by putting ", text" in the open function I was able to open the info into a data table!!!!! such a small change for such a big impact.