Brainteaser

[RS87]

Hello everyone,

I would like to start off by saying that this is my very first post and I registered here specifically for this problem :. Here's my situation :

I access a certain website through a web viewer. This website has a lot of numerical data on it that I would like to import into my solution. It would be too much of a hassle to use the GetLayoutObjectAttribute (objectname ; "content") approach because I would have to loop through hundreds of records and scrape all the data off the website for every single one of them.

However, there's a link on the webpage to a csv file (aha !) containing all the necessary data. Clicking on this link automatically brings up the Open or Save dialog. Here's my question : is it possible to script an automatic download (ideally without any user intervention) of this file (probably using said link), save it to my harddrive, then import the data into my solution ? This problem is killing me. I've tried a lot of things but I definitely haven't mastered importing/exporting/web scraping.

If there's anything else you need to know, don't mind asking me. Thanks for your time !

[Raybaudi]

Why not using directly the.

Open URL [ ]

script step, pointing to that Link ? ( w/o the WV )

[RS87]

That's not really an option. With the Open URL command, the .csv file is opened automatically in Excel. My goal is to import the data into my solution without the user having to do anything and, if possible, without the user seeing too much of it.

Any other ideas ? Is my description of the problem sufficiently elaborate ?

[Raybaudi]

Is it possible to know the url of both ?

[RS87]

If you are referring to the URL of the website and the URL of the link to the .csv file itself, then yes.

[Raybaudi]

Yes, that was it

[RS87]

I'm sorry, I only just realized you were asking for the actual URLs. Is that relevant though ? Not to be unkind or anything, but can you explain how that would help you come up with a solution ? If the problem definition is unclear, I can try to explain it differently. Thanks anyway for your input.

[fseipel]

You can download the CSV file to the local file system using a VBSCRIPT in Windows. http://windows-programming.suite101.com/article.cfm/how_to_use_vbscript_to_download_a_web_page

I tend to generate VBSCRIPTS in a field, export them, and open them using a Filemaker script.

If a plug-in is acceptable, 360Works ScriptMaster will do this -- simply combine the GET URL as Text example with the Write File example, then import the resulting file back into FM.

If you don't need to deploy to multiple computers, you might also try cURL; you can use it to download any web page to a file using a DOS command; simply build DOS command in FM, execute, then import the CSV.

[RS87]

That was extremely helpful ! You can't believe how long I've been looking for a solution to that problem. I think I'm gonna learn a lot here. :

[fseipel]

Glad it was helpful, below is a tested sample for SM, just add url and filePath as input parameters. e.g. url = http://www.nba.com/schedules/csvschedule.jsp?teamcode=celtics and filePath = C:UsersUserDocumentsTest11.csv -- I would have uploaded but it's longer than what this site will accept.

Also, I think the free plugin Moo Plug has a command to download a file from the web.

I've had great success with ScriptMaster. You could also just use two SM calls, one to read file in a variable or global field, the second to write it back to a file using the functions already in SM example (get URL as text and write file).

----------------------

URL url = new URL(url);

InputStream input = url.openStream();

try {

StringBuffer sb = new StringBuffer( input.available() );

Reader r = new InputStreamReader( input, "utf-8" );

char[] buff = new char[2048];

int charsRead;

while( (charsRead=r.read( buff )) != -1 ) {

sb.append( buff, 0, charsRead );

}

//This more advanced version allows you to specify the character encoding

OutputStreamWriter writer = new OutputStreamWriter( new FileOutputStream( filePath ), "utf-8" );

writer.write( sb.toString() );

writer.close();

return sb.toString();

} finally {

input.close();

}

Edited October 18, 2009 by Guest