Using php to upload excel data into MySQL

Hi Guys,

I want to have a form on my site that allows users to upload a list of domain names via an excel spreadsheet.

Here is the format of the excel spreadsheet:

domain name  category  min offer  asking price

I want to insert each row above into a MySQL database. How can this be achieved?

If it is saved in csv, you can use LOAD DATA INFILE

It’s saved as a .xls file. Will this function still work?

No, it won’t. If it’s in xls format, your only chance to import it is if it’s a windows server – then you can use com. On linux, it’s tough luck.

There must be an alternative way to loop through the xls file or something…?

I did a quick search and found this: http://sourceforge.net/docman/display_doc.php?docid=22092&group_id=99160

I had no luck with Word doc format in the past, so I figured xls might be the same. Haven’t tried the reader above, but looks like it might work.

I did something like this.
if memory serves I made a textarea where the user can paste a row or entire excel spread sheet.

To process you explode by
to get rows and then explode by tab to get the elements.

The excel spread sheet had over 2000 entries and possibly 20 fields and went pretty quickly.

If this sounds like a solution I can post the code tomorrow.

I have been looking for something like this, could you share the code please?


$pieces = explode("\
", $excel);
foreach ($pieces as $entries){
echo "$entries<hr>";
//call the function
enter_data ($entries);


//here is the function
function enter_data($entries){

$insert = explode("	", $entries); // explode by tab
echo "<pre>";
print_r($insert);
echo "</pre>";
}

$insert becomes an array and print_r will show you the vars to use

thanks, now i’ve been messing around with that script and have run into a dead end. I have a table with where I want the information to go into a corresponding cell. I also am trying to get each section of text to be its own var so i can input into mysql or output back to the user. I have this so far:

<?php
$text = $_POST["excelInput"];
$cols = explode("	", $text);
//$lines = explode("\
", $text);

//now i out each into an array
$date =$cols[0];
$firstName=$cols[1];
$lastName =$cols[2];
?>

Now I have this table set up:

<table border="1">
<tr>
	<th>Date</th>
	<th>First Name</th>
	<th>Last Name</th>
</tr>
<tr>
	<td><?php echo $date; ?> </td>
	<td><?php echo $firstName; ?></td>
	<td><?php echo $lastName; ?></td>
</tr>
</table>

So, i’ve tried implementing your script with mine to get it to read the $lines = exlode("
", excelInput).

The problem with what I have is that it won’t read the next line(obviously). I’m sure the answer is simple. I also know that i’m going to need a foreach statement in order to loop. any hints?

put the table in the function. and echo out the actual table tag before the function

replace print_r with


echo'<table border="1">
<tr>
	<th>Date</th>
	<th>First Name</th>
	<th>Last Name</th>
</tr>';

// function
echo'
<tr>
	<td>'.$date.' </td>
	<td>'.$firstName.'</td>
	<td>'.$lastName.'</td>
</tr>';
// end of function
echo </table>';


Okay, I tried combining the two scripts. I don’t think i’m pulling the array infomation correctly. It will read $date = insert[0]; but it displays the whole line of text, the other two array declarations are blank.
The code so far:

<html>
<body>

<?php
$excel = $_POST["excelInput"];

$pieces = explode("\
", $excel); //explode by line
foreach ($pieces as $entries)
{
  enter_data ($entries);
}

echo
'<table border="1">
  <tr>
    <th>Date</th>
    <th>First Name</th>
    <th>Last Name</th>
  </tr>';

function enter_data($entries)
{

$insert = explode("    ", $entries); // explode by tab
$date = $insert[0];
$firstName = $insert[1];
$lastName = $insert[2];

echo
'<tr>
  <td>'.$date.'</td>
  <td>'.$firstName.'</td>
  <td>'.$lastName.'</td>
</tr>';
}
echo '</table>';
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <table border="1">
  <tr>
    <td><textarea rows="10" cols="100" wrap="physical" name="excelInput"></textarea></td>
  </tr>
  <tr>
    <td>
      <input type="submit" value="SUBMIT">
    </td>
  </tr>
  </table>
</form>

</body>
</html>

Excel will save files as CSV, which is easily importable.

Excel is a proprietary Microsoft format, fields can contain formatting and formulae as well as values so indentifying the values in the content will require a significant program and first you will need to become familiar with the exact format used for xls files. As it is a proprietary format the only way to learn the format properly is to get a job at Microsoft where you will then have to sign a non-disclosure agreement prohibiting you from using that knowledge to do what you want anyway.

The only other way to do it would be to reverse engineer the format from a few million files with known content. It shouldn’t take you and your team of 50 programmers more than a few years to do that - by which time Microsoft will have changed the format a couple of times - as they do every new Excel release.

What you have to do is to convert it first from a proprietary format into a generic format and the easiest way to do that is to just use Excel to export it into a CSV.

make sure explode(" ", has only one tab and try print_r on $insert. If it echoes out the date and names in one line, there may be a problem with your “tab”.

The code you have looks right and should echo out something for the names even if they are formatted in excel.

Hi,

LOL! This is really informative. :wink:

Thanks.

Okay guys, If found a way to split the information that I wanted into my 3 arrays.
Modified Code:


<html>
<body>
<?php
//pull from form
$excel = $_POST["excelInput"];

//explode by tab
$cols = explode("   ", $excel);

// explode using $cols which has been exploded by tab
$lines = explode("\
", $cols);

//pull array using $cols
$date = $cols[0]; //array pulled by tab
$firstName = $cols[1];
$lastName = $cols[2];
?>

<table border="1">
  <tr>
    <th>Date</th>
    <th>First Name</th>
    <th>Last Name</th>
  </tr>

<?php
$count = 0;
$columns =3;
while ($count < $lines)
{
if($count % $columns == 0)
{
echo '</tr><tr>';
}
echo "<td>$date</td>";
echo "<td>$firstName</td>";
echo "<td>$lastName</td>";
echo '</tr>';

}
$count++;
?>
</table>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <table border="1">
  <tr>
    <td><textarea rows="10" cols="100" wrap="physical" name="excelInput"></textarea></td>
  </tr>
  <tr>
    <td>
      <input type="submit" value="SUBMIT">
    </td>
  </tr>
  </table>
</form>

</body>
</html>

Okay, question here, how can I take that data to display on its own row? I tried doing it myself as you can see above, but i’m sure its wrong. The Input from the form looks like this:
data data data
data data data


<?php
echo '<tr>';
echo "<td>$date</td>";
echo "<td>$firstName</td>";
echo "<td>$lastName</td>";
echo '</tr>';
?>

the only way I see it working is to explode by new line and send each piece into a function to explode by tab which will fill in the table posted above.

Your job would be much easier if you get the user to export the file to CSV. It’ll take them 2 seconds to do it and it’ll save you literally hours.

Not only that, but you can then import that file directly into mysql using LOAD DATA INFILE. Seriously, if you want stupidly heavy server load, go for the XLS type.

Office documents are encrypted in a way that only computers with the correct system dll files can read. That’s why Linux servers won’t have that ability, and windows servers will find it hard.

Excel files don’t have an actual data structure - just look at a *.xls file in notepad, completely gibberish. PHP and MySQL can handle structured data, because it’s simply a case of splitting documents by commas and newlines.

Because of that, other methods of reading files would never be foolproof. It’s also slower to upload a whole file through a textarea (and it causes hastle to the user).

Because of these reasons, you should seriously consider exporting to CSV. That way the user can simply upload it, and with a few lines of simple code you can put it into mysql in a much faster way than if you did it in excel.

arkinstall, you are right, that would be the best way. here is the scenario:
i work for a cable company, once an outage happens, we have a tool that gives us the number of calls that have come in for that particular node. Now this list gives all calls, including calls before the outage. We also have a form where the user logs an outage and I would like to be able to select the calls that fell within that time frame, paste that into the text box and save it along with the other information for said outage. Now the page where the calls come from are structured in a table format with multiple columns and rows. this is why i’m trying to get this to work. the user can select the rows, paste into the textbox and it will arrange itself into multiple arrays so that it can be stored.

The original tool where the calls are does not have a export to csv option. to get it to work they way that you mentioned the user would need to select the info and save it to maybe notepad or excel then save as csv.

If its not possible then its not possible.