pslr2301

I ended up just moving the files. If anyone has a better approach I would greatly appreciate it! =) Thanks! Pam $shell = "unzip $local_file"; $shell = escapeshellcmd($shell); exec($shell,$nu); rename("StandardExport.csv", "$file_path/StandardExport.csv"); rename("AmazonExport.csv", "$file_path/AmazonExport.csv");

I am new to Drupal and am attempting to build a custom module to connect to ftp, download a zip file and unzip the file on the server. The zip file contains two product csv files to update price lists on an ecommerce site. I have gotten it worked out to connect, and download the file. But no matter what code I try I cannot seem to get the files extracted. I can get the code working within a separate php file for testing but once I put it in the php and run from the cron tab it refuses to unzip. I am at a loss as to what to try next. I would really appreciate any pointers I could get! These are some of the things I have tried... the closest I have gotten is to have the first csv file within to create and start but it never finishes and the second file never starts. After changing the code so many times I couldn't even begin to tell you what I had when it was able to get that far. $server_file = 'ItemExport.zip'; $shell = "unzip $server_file"; $shell = escapeshellcmd($shell); exec($shell); $zip = new ZipArchive(); if ($zip->open('ItemExport.zip') === TRUE) { for($i = 0; $i < ($zip->numFiles); $i++) { $filename = $zip->getNameIndex($i); $zip->extractTo('.', array(trim($filename))); } $zip->close(); } else { echo 'failed'; } $zip = zip_open("ItemExport.zip"); if ($zip) { while ($zip_entry = zip_read($zip)) { $fp = fopen(zip_entry_name($zip_entry), "w"); if (zip_entry_open($zip, $zip_entry, "r")) { $buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); fwrite($fp,"$buf"); zip_entry_close($zip_entry); fclose($fp); } } zip_close($zip); } $zip_entry = 'ItemExport.zip'; $file_name = array('StandardExport.csv', 'AmazonExport.csv'); //get the file's size $fileSize = zip_entry_filesize($zip_entry); for($x = 0; $x<2;$x++) { //open the target file $outFile = fopen($file_name[$x],"wb"); while ($fileSize>0) { //read/write only up to 10kb per step $readSize = min($fileSize,10240); //decrease the size of the remaining data to read $fileSize -= $readSize; //get the data $content = zip_entry_read($zip_entry, $readSize); //write the data (if any) if ($content !== false) fwrite($outFile,$content); } fclose($outFile); }

Barand, The code worked perfectly!! I made some minor changes and it is all in the db. You rock! =) Thanks so much! I am still having trouble with the zip file itself. If you have time, any way you could point me in the right direction of an unzip script I can run to unzip the file on the server? I did have... <?php ini_set('memory_limit','-1'); $zip = new ZipArchive; if ($zip->open('ItemExport.zip') === TRUE) { for($i = 0; $i < $zip->numFiles; $i++) { $filename = $zip->getNameIndex($i); $zip->extractTo('.', array($filename)); } $zip->close(); } else { echo 'failed'; } } ?> but that would get to the near end of the first file and hang. Never finishing the first file and not ever finding the second file. I then tried $zip_file = $file_path; shell_exec("unzip $zip_file"); And that worked in a php file outside of drupal. But within drupal it doesn't seem to do anything. I want to automate the download of the zip from external ftp server and unzip on my server with a cron job so that our product list is updated every day. I really appreciate your time. Thanks so much. Pam

OK trying this now. I will let you know shortly. I am sure I will have a couple of questions for you. I had finally gotten the code I had written to unzip both files correctly and load up the table headers but now it seems as though that is going to stop working. (UGH!) Going to try yours and see what happens. Let you know here in a bit. Thanks so much for your time doing this.

Thank you so much for all your help! I really appreciate you taking the time to help me figure this out. The zip file with the 2 files is 6285kb. It looks like it will be too big to attach here. I just downloaded dropbox and uploaded it to this link. https://www.dropbox.com/s/mu94pce5xvtiavg/ItemExport.zip Let me know if this doesn't work. Thanks again! Pam

Yeah I saw that. I had changed it just seconds after I made that post. I make little changed and test as I go along to see what I am doing wrong. Since I don't know the code real well it is trial and error. My (limited) experience is with Java and only in the academic setting for the last few years. <?php // ini_set('auto_detect_line_endings', true); // ini_set('memory_limit','-1'); // ini_set('max_execution_time', '3600'); // setlocale(LC_ALL, 'en_US.UTF-8'); /*1. Create database connection*/ $connection = mysql_connect("localhost", "user", "password"); if (!$connection) { die("Databse connection failed: " . mysql_error()); } /*2. Select database to use*/ $db_select = mysql_select_db("databasename", $connection); if (!$db_select) { die("Database selection failed: " . mysql_error()); } //Standard List if ($handle = fopen(trim("StandardExport.csv"), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE product_list_standard;'; mysql_query($sql); $sql = 'CREATE TABLE product_list_standard ('; for($i=0;$i<count($data); $i++) { if ($i == 0) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, ';} else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE 'StandardExport.csv' INTO TABLE product_list_standard FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); } else { } //Amazon list only way this works with the code as is is to put it in a separate file. if ($handle = fopen(trim("AmazonExport.csv"), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE product_list_amazon;'; mysql_query($sql); $sql = 'CREATE TABLE product_list_amazon ('; for($i=0;$i<count($data); $i++) { if ($i == 3) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, ';} else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE 'AmazonExport.csv' INTO TABLE product_list_amazon FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); } else { } //close connection to the database mysql_close($connection);

OK so apparently drupal cant handle doing both files at once. It ran and found the amazon file just fine in a separate php file testing. I am going to separate the files and go with that. Just cron them at different times I guess. =) Thanks! For your help!!

I apologize if I am being a pain. I am trying to figure this out on my own. I know making all these changes to code isn't really helping you guys help me much. Drupal is finally giving me an error but it doesn't make much sense as like I said earlier... I ran the mini php file and it found the AmazonExport file just fine. Warning: fopen(AmazonExport.csv) [function.fopen]: failed to open stream: No such file or directory in include() (line 44 of database_standard.php). Line 44 is this: if ($handle = fopen(trim("AmazonExport.csv"), "r")) { This is my most recent version of the code. <?php ini_set('auto_detect_line_endings', true); ini_set('memory_limit','-1'); ini_set('max_execution_time', '3600'); setlocale(LC_ALL, 'en_US.UTF-8'); /*1. Create database connection*/ $connection = mysql_connect("localhost", "user", "password"); if (!$connection) { die("Databse connection failed: " . mysql_error()); } /*2. Select database to use*/ $db_select = mysql_select_db("databasename", $connection); if (!$db_select) { die("Database selection failed: " . mysql_error()); } //Standard List if ($handle = fopen(trim("StandardExport.csv"), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE product_list_standard;'; $sql = 'CREATE TABLE product_list_standard ('; for($i=0;$i<count($data); $i++) { if ($i == 0) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, ';} else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); } else { } // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE 'StandardExport.csv' INTO TABLE product_list_standard FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); //Amazon list if ($handle = fopen(trim("AmazonExport.csv"), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE product_list_amazon;'; $sql = 'CREATE TABLE product_list_amazon ('; for($i=0;$i<count($data); $i++) { if ($i == 3) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, ';} else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); } else { } // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE 'AmazonExport.csv' INTO TABLE product_list_amazon FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); //close connection to the database mysql_close($connection);

After much testing even the code I wrote above doesn't work to load the data from either file. This was my original code and it does work for StandardExport but not AmazonExport... [code=php:0]<?php ini_set('auto_detect_line_endings', true); ini_set('memory_limit','-1'); ini_set('max_execution_time', '3600'); setlocale(LC_ALL, 'en_US.UTF-8'); /*1. Create database connection*/ $connection = mysql_connect("localhost", "user", "password"); if (!$connection) { die("Databse connection failed: " . mysql_error()); } /*2. Select database to use*/ $db_select = mysql_select_db("databasename", $connection); if (!$db_select) { die("Database selection failed: " . mysql_error()); } /**set up table names and way to reference them $table_name = array('product_list_amazon', 'product_list_standard'); $z = 0; $table_primary = array('3','0'); $y = 0; */ //Load headers if ($handle = fopen(trim("StandardExport.csv"), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE product_list_standard;'; $sql = 'CREATE TABLE product_list_standard ('; for($i=0;$i<count($data); $i++) { if ($i == 0) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, ';} else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); } else echo "Arg!"; // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE 'StandardExport.csv' INTO TABLE product_list_standard FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); //close connection to the database mysql_close($connection); [/code]

The csv file opens in excel fine and looks like any other normal csv. Contents of the csv are set up like the "working" csv except for the part I mentioned about some data being in a different column. To me, it looks like the loop is just skipping over AmazonExport.csv as if it was not even there. From everything I have read the last few days while trying to fix this on my own it appears to me that as long as the | delimiters are there the data will still work the same regardless on how many columns the data is in. I would like some verification of that if possible. The csv file shows up when I run a little mini php to test whether it is finding the files and how it is finding the names. [code=php:0] //set up table names and way to reference them $table_name = array('product_list_amazon', 'product_list_primary'); $z = 0; $table_primary = array('3','0'); $y = 0; //Traverse through csv files to load into database foreach(glob("*.csv") as $file) { //Load headers if ($handle = fopen(trim($file), "r")) { echo "File name:".$file; echo "*****table Name:".$table_name[$z]; echo "*****tablePrimary:".$table_primary[$y]; fclose($handle); } else echo "Arg!"; $z++; $y++; } [/code] This results in an output to screen File name:AmazonExport.csv*****table Name:product_list_amazon*****tablePrimary:3File name:StandardExport.csv*****table Name:product_list_primary*****tablePrimary:0 So it does find the file in the directory. StandardExport is loading into my database just fine. But AmazonExport just won't open. I assumed that it had something to do with fopen and maybe some encoding or something. I have read the manual on fopen, fgetcsv, everything discussion board listed on google I can find. Just can't see why this particular file (AmazonExport) would not open the same way that StandardExport does. StandardExport.csv 38,488kb AmazonExport.csv 35,478kb (Example of content shown in OP) Server: localhost via TCP/IP Server version: 5.5.16 Protocol version: 10 User: root@localhost MySQL charset: UTF-8 Unicode (utf8) Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1 MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $ PHP extension: mysql

I am trying to bring in 2 csv files from the current directory and throw them into my database. It brings in one but won't touch the other. I have tried every configuration of code I can think of. I am left thinking that something might be wrong with the csv but it opens correctly and looks fine. I am new to php so my code is probably really crap... I could really use some help figuring this out. This is for a module in drupal. (New to that also!) Have switched to glob so that I could get the file without using the name directly thinking it might be something in the name messing it up. I wrote a separate file to test that it can find the file and it finds it perfectly. I guess my biggest question is to make sure that my php *should* work before investigating other avenues. Also is there some special line that I should add to force my php to recognize as csv or IDK I know I am missing something! Thanks in advance!!! Pam [code=php:0] //Not sure if all these are needed but //until I know better to be safe than sorry ini_set('auto_detect_line_endings', true); ini_set('memory_limit','-1'); ini_set('max_execution_time', '3600'); setlocale(LC_ALL, 'en_US.UTF-8'); /*1. Create database connection*/ $connection = mysql_connect("localhost", "user", "password"); if (!$connection) { die("Databse connection failed: " . mysql_error()); } /*2. Select database to use*/ $db_select = mysql_select_db("databasename", $connection); if (!$db_select) { die("Database selection failed: " . mysql_error()); } //set up table names and way to reference them $table_name = array('product_list_amazon', 'product_list_primary'); $z = 0; $table_primary = array('3','0'); $y = 0; //Traverse through csv files to load into database foreach(glob("*.csv") as $file) { //Load headers if ($handle = fopen(trim($file), "r")) { $data = fgetcsv($handle, 4096, "|"); $sql = 'DROP TABLE '.$table_name[$z].';'; $sql = 'CREATE TABLE '.$table_name[$z].' ('; for($i=0;$i<count($data); $i++) { if ($i == $table_primary[$y]) { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50) NOT NULL PRIMARY KEY, '; } else { $sql .= strtolower(str_replace(" ","_",$data[$i])).' VARCHAR(50), '; } } //The line below gets rid of the comma $sql = substr($sql,0,strlen($sql)-2); $sql .= ') CHARACTER SET utf8 COLLATE utf8_general_ci;'; fclose($handle); } else echo "Arg!"; // Create table with headers mysql_query($sql); //Load the rest of the data into the table mysql_query("LOAD DATA LOCAL INFILE ".$file." INTO TABLE ".$table_name[$z]." FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';"); $z++; $y++; } //close connection to the database mysql_close($connection); [/code] First lines of csv are Brand|Line Code|Part Number|SKU|Distributor Cost|Package Quantity|Core Price|UPC|Part Description|Inventory Count|Inventory Type A1 Technologies|A1T|6-12VALVEHDSTD17-22A|A1T6-12VALVEHDSTD17-22A|406|1|0|83747 |KIT 17-22A Cummins 12 Valve HD Stud Mat'L 17-22A 215 KSI MIN GLASS BEAD Finish With Broach|40|Stocking Now the file that works is very similar but all of the information is in the first column of each row. In this file, (non-working) I did notice that some of the rows have information on different columns. It still works for the separator | but it is in a different column. (Shown by *) A1 Technologies|A1T|6-12VALVEHDSTD17-22A|A1T6-12VALVEHDSTD17-22A|406|1|0|83747 |KIT 17-22A Cummins 12 Valve HD Stud Mat'L 17-22A *215 KSI MIN *GLASS BEAD Finish With Broach|40|Stocking Does this matter?