PHP fgetcsv not bringing in one csv from file

[pslr2301]

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?

[Barand]

Can you define "non-working" so we have an idea what to look for

[pslr2301]

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

 

[pslr2301]

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]

[pslr2301]

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);

[pslr2301]

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!!

[Barand]

The first thing that stands out in your code is that the DROP TABLE queries are never executed.

[pslr2301]

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);

[Barand]

if you attach those 2 csv files I'll try it at my end

[pslr2301]

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

[Barand]

I had the downloaded data but not your code and I couldn't get it today (site down) so I wrote my own code.

 

It works but there seems to be some duplicate primary keys as not all records are loaded. I was guessing at which field should be the primary key as I couldn't review your posts. Any way, try it and we'll discuss.

 

<?php
   include("testDBconnect.php");
   $filepath = realpath("./csvs");

   $fileArray = array (
				 'pam_table_1' => array(				    // mysql table name
					    'csv' => 'AmazonExport.csv',		    // csv name
					    'pk' => 'brand,part_number',		    // primary key field/s
					    'numrows' => 214446					 // number of records
					    ),
				 'pam_table_2' => array(
					    'csv' => 'StandardExport.csv',
					    'pk' => 'part_number',
					    'numrows' => 214446
					    )
		    );

   foreach ($fileArray as $table => $fdata) {
    extract($fdata) ;

    if ($flds = getFields("$filepath/$csv"))
    {
	    createTable($table,$flds,$pk);
	    loadFile($table,"$filepath/$csv",$numrows);
    }
    else echo "ERROR - $csv<br />";
   }

   /***
   *  Function definitions
   */

   function getFields ($csv)
   {
    $fh = fopen ($csv,'r');
    if ($fh) {
	    $flds = fgetcsv($fh, 4096,'|');
	    fclose($fh);
	    return $flds;
    }
    else return false;
   }

   function createTable ($name, $fields, $pk)
   {
    $sql = "DROP TABLE IF EXISTS $name";
    mysql_query($sql);
    $sql = "CREATE TABLE IF NOT EXISTS $name ( \n";

    foreach ($fields as $f) {
	    $f = str_replace(' ', '_', strtolower($f));
	    $sql .= "$f VARCHAR(150) ,\n";
    }
    $sql .= "PRIMARY KEY ($pk))";
    mysql_query($sql);
   }

   function loadFile ($table, $csv, $n)
   {
    $csv = addslashes($csv);
    $sql = "LOAD DATA LOCAL INFILE '$csv'
	    INTO TABLE $table
	    FIELDS TERMINATED BY '|'
	    IGNORE 1 LINES";
    mysql_query($sql) or die(mysql_error()."<pre>$sql</pre>");

    // control check
    $res = mysql_query("SELECT COUNT(*) FROM $table");
    $count = mysql_result($res, 0);
    printf("TABLE: %s<br /> CSV: %s<br />Loaded: %d , Expected: %d , Missing: %d<br /><br />",
	    $table, stripslashes($csv), $count, $n, $n-$count);
   }
?>

[Barand]

Primary key on amazon table should be "sku". Both CSV files contain duplicate key "AFE44-LF004".

[pslr2301]

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.

[pslr2301]

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

[Barand]

I tried your zip file with the same zipArchive class as you and also with zip_open();

 

Both time had the same problem as you - hangs at or near end of first.

 

I'll try in a while with one of my own zip files to see if it's an input problem.

[Christian F.]

Downloaded it via Dropbox itself, and checked it out with unzip locally. Didn't get any errors on the zip file, so the file itself is good. At least the copy sitting in your DB folder at this moment.

 

However, when I tried your code (after removing the superfluous closing bracket) PHP stopped at a certain point with 100% CPU (core) load:

$ ls -al *csv
-rw-rw-r-- 1 1000 1000 39403520 Sep 23 21:15 StandardExport.csv

 

Letting it run to see if it suddenly snaps out of it, but I doubt it.

[Barand]

The code below worked fine with a couple of test files that I zipped but still hangs near the end of your first file.

 

Like Christian I have no problem otherwise opening the zip and extracting the files.

<?php
$path = realpath('./csv2');
$zip = new ZipArchive;
if ($zip->open('ItemExport.zip') === TRUE) {
    $zip->extractTo($path);
    echo 'ok';								
    $zip->close();
} else {
   echo 'failed';
}
?>

[Christian F.]

Might be that you've found a bug in the class/PHP. In any case, this warrants a closer look at.

 

As for the results of my test I just killed the script in the end, didn't change at all after waiting for about an hour.