|
|
Posted by Richard Lynch on 02/08/05 19:41
Afan Pasalic wrote:
> <?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')
This is not a Good Thing.
The MIME type sent by the browser is browser-dependent. So IE will send
text/x-csv and Netscape will sent text/csv and Mozilla will send
text/plain and...
You should just remove this test entirely.
> $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());
Use http://php.net/move_uploaded_file instead. Safer.
> # READING UPLOADED CSV FILE
> $row = 1;
> $handle = fopen('temp/'.$_FILES['NewItems']['name'], "r");
>
> $data = fgetcsv($handle, 10000, ",");
"," is the default, so adding it here is cruft.
I believe the 10000 is optional in recent versions of PHP, and while you
might want to limit lines to 10K, it's not needed, and if you ever NEED a
line bigger than 10K...
> # 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.';
This should probably be $alert .= instead of = and you should initialize
$alert = '' at the tip-top of your script.
Because A) this is in a loop, so you may have LOTS of lines with too many
columns, and B) you may need to add an alert earlier in your script some
day.
Also consider using an array for $alert, and using:
$alert[] = 'Alert message here.';
You can then use something like:
echo '<span class="error"><p>', implode("</p>\n<p>", $alert), '</p></span>';
if your SAPI is *not* CLI nor CGI, but use just
echo implode("\n", $alert);
if your SAPI is CLI/CGI (in a cron job or something some day)
> 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])
You might want to use http://php.net/strtolower on each, in case different
CSV engines decide to use upper/lower case on the column names...
Also be aware that some folks don't put the first line with column names
when they do CSV dumps...
Maybe you can force your users to do that, maybe not.
> {
> $alert .= $data[$i] .' - '. $column_names[$i].' <---
> Columns in database and CSV file are not in the same order<br>';
> }
> }
> }
>
>
> if(!isset($alert))
This will change to !count($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());
Ouch. If two people are uploading CSV files, then they will most likely
be generating duplicate itemNumber values for the same data...
If you're just uploading your own data, this is fine, though.
Although I think there's a PHP application "out there" that is designed to
synchronize ODBC and MySQL databases. You may want to look around for it
instead of rolling you rown.
> 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
But this is not counting ROWS, it's counting COLUMNS.
You've ALREADY completely and thoroughly processed that first bogus row
with the field names...
Maybe eXcel dumps out some bogus first column you don't want?
> {
> $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>
Puut quotes on the "multipart/form-data" to be kosher.
> 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.
You might be able to force it to use the columns that are all blank by
click-and-drag to select, and then choosing to export only the selection.
You might also be able to write some kind of ExCel macro or somesuch that
will do what you want.
You may also want to simply not worry about missing columns in your PHP
script, and use NULL for them.
> 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.
There may also be existing PHP code to read raw .xls files... Check
http://phpclasses.org and Google for it.
--
Like Music?
http://l-i-e.com/artists.htm
Navigation:
[Reply to this message]
|