Loading Excel

rahulpatel

I have half figured this out but the last bit is stumping me.

What I've been asked to do is sort some data in an Excel spreadsheet. I find it much easier to write SQLs in PHP/MySQL rather than VBA so I prefer to do it this way. In a folder there are 20 spreadsheets with one being added per week. I want to list the spreadsheets in a page and have the user click the desired file. That is OK so far. I can also load the data into MySQL using LOAD DATA INFILE but those two things are seperate. I would like the user to click the file listed on the page and it to be loaded 'instantly' into the database (instead of asking to open it).

<?php
 if ($handle = opendir('.')) {
   while (false !== ($file = readdir($handle)))
      {
     //     if ($file != "." && $file != "..")
     if ($file != "." && $file != ".." && $file != "..")
	  {
          	$thelist .= '<a href="'.$file.'">'.$file.'</a><br />';
          }
       }
  closedir($handle);
  }
  ?>
<P>List of files:</p><br  />
<P><?=$thelist?></p>
<?php
// Make a MySQL Connection
mysql_connect("localhost", "tag141", "penarth141") or die(mysql_error());
mysql_select_db("excel") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query ("LOAD DATA INFILE '".$file"' [B]<- This is the troublesome part[/B]
INTO TABLE excel
FIELDS TERMINATED BY ',')
 or die(mysql_error());

echo "Data inserted!";

?>

Any help greatly appreciated or directions to tutorials.

big_nerd

From what I can tell you are trying to import an Excel file by specifying CSV standards (Comma Separated Values).

If it is truely an XLS file, that is a binary format and this method won't work for you.

You can either save the "excel" files as CSV format - which is plain text, no formatting at all.

If this is an XLSX (Office 2007) that is an XML format and there is a PHP class for that.

If you like SQL as an option, you could use Access as well, however that isn't intended for multi-user, it does support the importing of Excel data.

rahulpatel

Sorry, a touch misleading. I am trying to import a CSV file not XLS. My mistake.

I still can't figure out how to alter that part of the file so when the link is clicked, it inserts the data.

johanafm

Change your links so they point to your script, not to the actual files

"<a href="insertFileInDb.php?file=$file">$file</a>

Then have your script first verify that the file exists, and then do your LOAD DATA.

amax

Hi big nerd. You said - "If this is an XLSX (Office 2007) that is an XML format and there is a PHP class for that.".
What is the name of the class used to do that, sounds cool, wouldnt mind reading up on it?

Ashley_Sheridan

You could parse the CSV data directly from the file and put it into an array to work on with fgetcsv:

http://php.net/manual/en/function.fgetcsv.php

amax

Thanks Ashley, that is one handy function I never knew about