Exell to Mysql

Warp

Hello every one,

I know there is a way I could keep my database in mysql updated by importing information I collect from excell sheets.

Firstly, I exported the data out of Excel into a comma-separated text file format (CSV).

Now I should be able to import this data into MySQL using the LOAD command.

But I'm blocked, I don't know how to start on this kind of uploading script (php). I hope you can help me... I hope there are some people around who have examples or scripts to help me out!

Thanks in advance for your help.

undertow

I'm not sure what the actual way to do it in SQL is (but you could check the manual) but in phpMyAdmin it's really easy. You just say what the separation characters are (eg, newlines to separate records, commas to separate fields, etc.) and upload the CSV file.

The only problem is Excel will put quotes in strange places (well, I'm sure there's some reason, I just haven't bothered to look into it much) which can make your data inconsistant. If you tell phpMyAdmin to use quotes to enclose the data, the fields that Excel didn't put quotes on will be messed up, and if you tell it not to use quotes, you'll get quotes in your inserted data. I think the last time I did this I actually edited the CSV file by hand to get it the way you want, but I only used it as a one-time thing to enter all my data in an easier manner all at once. Since you want to use Excel CSV to actually maintain the data, it would be a pain to edit the file each time you make a change.

But why don't you just use SQL commands to keep the DB maintained? That's what they're there for :p.

Warp

You've go a point,

But the thing is that i'm building a e-commerce system that will be used by the shopmanager himself.

The moment he receives an exel-sheet (from the manufactors) he has to be able to insert the data into the database.

In this way the site is easy to maintain and the shopmanager is able to updata his productlist at a "very easy" way.

Building the system is not that "very easy" 😉

Greetz,

And thanks a lot

rinjani

Here's some code that I use, but you'll have to figure out the input form slightly.

if (isset($submit) && ($file != '')) {

// need the following variables to run

$table = 'A1';//db table A1_test
$char;//comma, semi_colon, tab etc
$line_terminator; //\n, \r etc

//$connection = mysql_connect("$host", "$login", "$password") OR DIE ("unable to connect");

mysql_select_db ($db);

$line_terminator = stripslashes($line_terminator);
$char = stripslashes($char);

$query = "LOAD DATA LOCAL INFILE '$file' INTO TABLE $table FIELDS TERMINATED BY '$char' ENCLOSED BY '\"' LINES TERMINATED BY '$line_terminator'";

//print $query;

$result = mysql_query($query) or die("Error in query");

//echo mysql_errno().": ".mysql_error()."<br />";

mysql_close($connection);

//print a new page
echo"
Thank you for your upload.";

You need to have a form with the file name, the line terminator (\n - unix, \n\r -pc etc.) and the seperator (colon, comma, semi-colon).

Your php.ini file needs to be set allow uploads. You need a tmp directory that the web server is allowed to upload to. If you look in the mysql manual you'll find that there a couple of other options that are not included in the SQL query I have above (for simplicity).

HTH