Suggestion for excel to mySQL to webpage

ozrock

Hi,

I would like some suggestions as to what would be the best way to take data inserted into an Excel file on an XP/7 box up to Linux mySQL db then out into areas of web pages.

It is product data such as prodID, image, description, price etc.

The data will need to show each product in one row across the page leading with image.

Each page will be one catagory, say fridghes, lawn mowers etc.

Are there already scripts that will do this or will I have to get it customed made?

Many thanks

ozrock

dagon

save as csv, the you can either use mysql import (also available in the widely used phpMyAdmin) or php fgetcsv

Saviola

try this :

<?
/********************************************************************************************/
/* Code at [url]http://legend.ws/blog/tips-tricks/csv-php-mysql-import/[/url]
/* Edit the entries below to reflect the appropriate values
/********************************************************************************************/
$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "E:\Webcache\AA ONLINE\test1\english1.csv";
/********************************************************************************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************************************************************************/
$addauto = 0;
/********************************************************************************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************************************************************************/
$save = 1;
$outputfile = "output.sql";
/********************************************************************************************/
if(!file_exists($csvfile)) {
  echo "File not found. Make sure you specified the correct path.\n";
  exit;
}

$file = fopen($csvfile,"r");
if(!$file) {
  echo "Error opening data file.\n";
  exit;
}
$size = filesize($csvfile);
if(!$size) {
  echo "File is empty.\n";
  exit;
}
$csvcontent = fread($file,$size);
fclose($file);
$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());

@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();
foreach(split($lineseparator,$csvcontent) as $line) {

  $lines++;
  $line = trim($line," \t");
  $line = str_replace("\r","",$line);
 /************************************************************************************************************
  This line escapes the special character. remove it if entries are already escaped in the csv file
************************************************************************************************************/
  $line = str_replace("'","\'",$line);
  /***********************************************************************************************************/
  $linearray = explode($fieldseparator,$line);
  $linemysql = implode("','",$linearray);
  if($addauto)
    $query = "insert into $databasetable values('','$linemysql');";
  else
   $query = "insert into $databasetable values('$linemysql');";

  $queries .= $query . "\n";
  @mysql_query($query);
}
@mysql_close($con);

if($save) {
  if(!is_writable($outputfile)) {
       echo "File is not writable, check permissions.\n";
  } else {
       $file2 = fopen($outputfile,"w");

    if(!$file2) {
      echo "Error writing to the output file.\n";
    } else {
       fwrite($file2,$queries);
       fclose($file2);
    }
   }
}
echo "Found a total of $lines records in this csv file.\n";
?>

bradgrafelman

Note that this:

$csvfile = "E:\Webcache\AA ONLINE\test1\english1.csv";

will cause problems, since that string is evaluated as:

E:\Webcache\AA ONLINE   est1\english1.csv

which is why I'd recommend using forward slashes for all paths.

EDIT: Actually, Saviola, why are you reading in the entire file using fread() (which could instead be more easily/efficiently done with [man]file_get_contents/man in the first place) rather than using [man]fgetcsv/man?

You also make the assumption that all commas in a CSV denote a column separation, when in fact they do not.

Saviola

bradgrafelman;10956270 wrote:
Note that this:
$csvfile = "E:\Webcache\AA ONLINE\test1\english1.csv"; 
will cause problems, since that string is evaluated as:
E:\Webcache\AA ONLINE   est1\english1.csv
which is why I'd recommend using forward slashes for all paths.

Yes, is my mistake, it will be forward slashes.

bradgrafelman;10956270 wrote:
EDIT: Actually, Saviola, why are you reading in the entire file using fread() (which could instead be more easily/efficiently done with [man]file_get_contents/man in the first place) rather than using [man]fgetcsv/man?

Advantage of fread() is that you can read just a said amount of bytes from a line - you don't have to read the full file at once. file_get_contents() can be faster because it's a single call to read the file. But it can also be slower - because it reads the entire file into memory at one time. If you're reading 5MB files, file_get_contents() will take something more than 5MB of RAM. Stack a few of these up and you'll be using a LOT of RAM - maybe too much and the system can start paging.
fread(), only gets small amounts of data at a time. And yes, for large files it can take longer than file_get_contents(), but for small files I don't think you'll notice any difference.

About fgetcsv(), i don't like at all to try to define the "optional" max length of the line..

If you use fgetcsv to read pipe-delimited data files contained data similar to this:

RECNUM|TEXT|COMMENT
1|hi!|some comment
2|"error!|another comment
3|where does this go?|yet another comment
4|the end!"|last comment

<?php
$row = fgetcsv( $fi, $length, '|' );
?>

This causes a problem on record 2: the quote immediately after the pipe causes the file to be read up to the following quote --in this case, in record 4. Everything in between was stored in a single element of $row.
Another Note that fgetcsv, at least in PHP 5.3 or previous, will NOT work with UTF-16 encoded files.

bradgrafelman;10956270 wrote:
You also make the assumption that all commas in a CSV denote a column separation, when in fact they do not.

You can esay change this ... just have to change $fieldseparator

bradgrafelman

Saviola wrote:
Advantage of fread() is that you can read just a said amount of bytes from a line - you don't have to read the full file at once.

You can, yes, but that's not what you're doing. In the code you posted above, you're fread()'ing the entire file. And besides, for a CSV, why would you want to read a fixed number of bytes rather than entire lines at a time?

Since you're reading the entire file in your code above, I referenced [man]file_get_contents/man for this reason:

PHP Manual wrote:
file_get_contents() is the preferred way to read the contents of a file into a string. It will use memory mapping techniques if supported by your OS to enhance performance.

Saviola wrote:
About fgetcsv(), i don't like at all to try to define the "optional" max length of the line..

Again, you'd be doing the same thing with fread() anyway, so why not?

Saviola wrote:
This causes a problem on record 2

The problem isn't with fgetcsv(), because normal CSV formats denote a double quote as a way to explicitly enclose a given column's data where it might otherwise be ambiguous (e.g. if the data itself contained the delimiter character, you'd want to surround it with quotes).

So if you're expecting to read data like that, then yeah, you can't use fgetcsv() because it's not a proper CSV format.

Saviola wrote:
Another Note that fgetcsv, at least in PHP 5.3 or previous, will NOT work with UTF-16 encoded files.

Considering that PHP 5 as a whole isn't unformly Unicode-friendly (that's what PHP 6 is for, supposedly), I don't really fault fgetcsv() for that shortcoming.

Saviola wrote:
You can esay change this ... just have to change $fieldseparator

That's not what I meant. If you have properly formed CSV data, like so:

name,point,comment
John Smith,"38.222,43.666","Hi, friends!"

your code would give me 5 columns, whereas fgetcsv() would correctly give me 3.

Saviola

OK, I agree with most of your comments and advice.
This piece of code that i posted is just a piece of old code that I use for certain simulations with data I've seen from some forum and I kept in a clean version. I am far from that opinion is the optimal and most appropriate option for implementation. There are many things that can be improved if the situation requires. But often having to import information entered by users and subsequently exported from the database and then properly formed CSV concept goes to the afterlife.
Therefore in cases in which I've done that now I always use exported data from me or I require a sample to be prepared for import.
I do not want to enter into polemics what is best in this case, because I've worked enough with this functionality and I can't know all the advantages and disadvantages.
The code realized with fgetcsv() should be something like this :

<?php
$fp         = fopen("english.csv", "r");

$length         = 4096; /// have to be optional length ...
$fildDelineate  = ','; /// or "|" ... declare what you need
$databasetable  = 'tableName';
$counter        = 1; // to omission first row if it is table headers

while( !feof($fp) ) {

  if( !$line = fgetcsv($fp, $length, $fildDelineate, '') && $counter) {
     continue;
  }
  foreach($line as $key => $value){
   // For example insert in 3 column (e.g. id, name, email )
   $importSQL = "insert into $databasetable values('$line[0]','$line[1]','$line[2]')";
   mysql_query($importSQL) or die(mysql_error());
  }
  $counter++;
}

fclose($fp);
?>