Excel into MySQL

Question

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.

Answer 1

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. ;-)

Answer 2

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.

Answer 3

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.