1

A website has a link to download an excel file. I need some of those columns in my MySQL database. Is there a relatively painless way to: 1. use phpmyadmin's import tab 2. use a method similar to mashup's import 3. something else . . . 4. scrape the html from the page instead of using the excel file (ugh). edit: I would like to be able to automate the process - someone visits my site and clicks a button to update the information in the database from that file on that other website.

0

You have lots of options. Basically, the overall plan would be to export from the spreadsheet into an .CSV or comma separated values file, and then import from the .CSV file into your database. You should be able to export selected columns only from the spreadsheet, or you could copy the spreadsheet page into another page and delete the unwanted columns before exporting.

On the other side, you should be able to import CSV data into your database, discarding unwanted columns on the fly.

Again, there are a lot of ways to go, but it's pretty straightforward.

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

| improve this answer | |
  • I should have been more specific. See my edit. Thank you! – Ben Love Dec 11 '15 at 13:26
  • Yes, 'automating the process' was a very important detail that you left out. It should be possible to automate the whole process. Like most programs, break it down into parts. 1. download the existing file - there should be a link on that website that you can capture that downloads the spreadsheet that you can embed in a script or php file. 2. import the data into your database - something we learned in pset8. You'll have to figure out the details on your own though. Most of the parts should have examples that you can google, so time to do some research. Good luck. – Cliff B Dec 11 '15 at 18:00
  • Yeah, sorry. Thank you Cliff. And thanks again for all your support to me and everyone else through this course! I'm currently scraping the page with xpath. It's a long slog but I'm getting it. – Ben Love Dec 11 '15 at 21:07
0

php's fopen can take a url for the filename argument. So something like:

     $fname="http://www.website.com/file.csv";
     $handle = fopen($fname,'r');
     while ($csvdata = fgetcsv($handle))
     {
            // build your query with $csvdata elements
     }
     fclose($handle);

should get you started. Fair warning: A lot might depend on your data source. You may encounter many obstacles along your journey. I did a quick repro with some csv files from data.gov and it worked swimmingly.

| improve this answer | |
0

As already stated, you have a ton of options. Depending on how their file is created, you may not be able to actually implement @DinoCoderSaurus 's example because there may be extra information in an xls or xlsm file for example. The first thing that I would do is find out if the file is available as xml or json as either are fairly easy to work with in just about any language.

You could then use curl or ajax to grab the file or its contents, and go from there. If the data is uniquely keyed when you receive it and you maintain that constraint in your database, then you may even be able to skip the explicit filtering of already existing records as you can have duplicates automatically discarded.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .