Have to create a script to select excel file and store info from the file in existing table (mysql).
After some research found the best way will be save excel (xls) file as csv comma delimited file and then store info in table.
For first step I just used in Excel app File > Save As.. > CSV (Comma delimited) *.csv
Then, copy the file to server and then read info and store them in table using this script:
<?php
# names of columns in products table
$column_names = array('ITEM NUMBER', 'ITEM NAME', 'DESCRIPTION', 'COLORS', 'INCLUDES', 'QTY', 'PRICE', 'IMAGE', 'THUMB', 'CATALOG', 'STATUS', 'SUPPLIER', 'EXP_DATE');
if(isset($_POST['SubmitExcelFile']))
{
# VALIDATING UPLOADED FILE
if($_FILES['NewItems']['type'] != 'text/x-csv')
$alert = 'Uploaded file is not csv file.';
elseif($_FILES['NewItems']['size'] > 1000000)
$alert = 'File you are uploading is bigger then 1MB. Please contact administrator';
else
{
# IF THERE IS NO TEMP DIR CREATE ONE
# COPY SELECTED FILE TO TEMP DIR
copy($_FILES['NewItems']['tmp_name'], "temp/".$_FILES['NewItems']['name']) or die (mysql_error());
# READING UPLOADED CSV FILE
$row = 1;
$handle = fopen('temp/'.$_FILES['NewItems']['name'], "r");$data = fgetcsv($handle, 10000, ",");
# checking if number of columns in csv file and number of columns of table ($column_names) are the same
if(count($data) != count($column_names))
$alert = 'CSV file is not correct. Number of columns in database and number of columns in file are not the same.';
else
{
for($i=0; $i<count($data); $i++)
{
# checking if the order of columns in csv file is the same as order in table
if($data[$i] != $column_names[$i])
{
$alert .= $data[$i] .' - '. $column_names[$i].' <--- Columns in database and CSV file are not in the same order<br>';
}
}
}
if(!isset($alert))
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$query = "select itemNumber from products_test where itemNumber='".$data[0]."'";
$query = mysql_query($query) or die (mysql_error());
if(mysql_num_rows($query) > 0)
{
$duplicates[] = $data[0];
}
else
{
$num = count($data);
$insert_item_query = 'insert into products_test values (';
for ($c=1; $c < $num; $c++) #starting from 1 beacause first row are column names
{
$c == 1 ? $insert_item_query .= "'".addslashes($data[$c])."'" : $insert_item_query .= ", '".addslashes($data[$c])."'" ;
}
$insert_item_query .= ')';
echo 'QUERY: '.$insert_item_query.'<br><br>';
mysql_query($insert_item_query) or die (mysql_error());
++$row;
}
}
fclose($handle);
# list of all items already exist in table
echo '<p>no. of duplicates: '.count($duplicates).'<br>duplicates: '.print_array($duplicates).'<br>';
}
}
}
if(!isset($_POST['SubmitExcelFile']) or isset($alert))
{
echo '<div style="color: red;"><b>'.$alert.'</b></div>';
?>
<form method="post" action="add_csv.php" ENCTYPE=multipart/form-data>
Select Excel file:<br>
<input type="File" name="NewItems"><br>
<input type="Submit" name="SubmitExcelFile" value="Submit">
</form><?php } ?>
Problem I have is actually making csv file from xls file. Last two columns in xls file are 99% empty and when I Save As xls as csv and open then csv in TextPad some of rows don't have last two (empty) values entered? It says 11 instead 13 elements of row. And then it breaks the code.
Once I put "fake" column on the end of xls file, after EXP_DATE and put 1 as value in each row - everything looks ok.
Ok, it looks like it's for EXCEL forum but I need a code that would read content of the xls file, to avoid saving xls file in Excel and need opinions about the code (code is from php.net), to improve it for my case.
Thanks for any help.
-afan
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
