CSV upload

[l12852]

Hi, I have a script that will upload a CSV file into MySQL. The file has some quotation marks around the values. This script seems to get rid of those. Now I have a file without quotation marks around the values; they are only separated by a comma. seems simple, but I can't get them load. I was wondering if anybody see's something I don't in these few lines of code that is preventing me from uploading this new file type.

$openit = file($UPLOAD_PATH."/".$name);
$filearray = file($UPLOAD_PATH."/".$name);
$rowcount = count($openit);
$validrecords = $rowcount; $counter_fail = 0;
$counter_succ = 0;
for($j=0; $j<$rowcount; $j++)

{
$item = explode("$separator", $filearray[$j]);

[suntra]

Could you post a more complete code please ? For example, $separator is not defined in the code you sent...

[sneakyimp]

firstly, uploading the file is an entirely different task than reading the file. there could be a number of reasons:
the file doesn't exist
the file is empty
* etc.

your code appears to open the file twice. there is also nothing inside your loop. you might wanna try:

$file_path = $UPLOAD_PATH . "/" . $name;
if (!file_exists($file_path)) {
die("File doesn't exists");
}
$filearray = file($file_path)
or die("file open failed");
$rowcount = count($filearray);
for($j=0; $j<$rowcount; $j++) {
$line = trim($filearray[$j]);
$item = explode($separator, $filearray);
}

other tips:
reading the entire file into memory won't work for large files. you might want to loop on fgetcsv() instead. go to php.net for more info.
performance might suffer if your file is large because you are constantly indexing into a large array, $filearray. you might want to chane your for loop to a foreach loop.

Also, it would be helpful for you to describe what about the behavior or your script is unsatisfactory.

[l12852]

$UPLOAD_PATH = "/home/content/w/e/s/westernmotor/html/load/";

$UPLOAD_NUM = 2;

function displayUploadedList ()
{
global $UPLOAD_PATH,
$UPLOAD_NUM,
$_FILES;

$address ="?";
$user = "?";
$pass ="?";
$mydatabase = "?";
$mytable = "table";
$separator = ",";
$noconnection = "Error: Could not connect to database. Please try again later!";
@ $db = mysql_pconnect("$address", "$user", "$pass");
mysql_select_db($mydatabase);

// Connect to the database - exit if connection fails
if (!$db)
{
	?>
	<span class=Error><?=$noconnection?></span>
	<?	

	return false;
}

mysql_query("delete from $mytable");


$buff = '';
$error = 0;
for ($i = 0; $i < $UPLOAD_NUM; $i++)
{
	$name = $_FILES["File$i"]['name'];        
	$tmp  = $_FILES["File$i"]['tmp_name'];   

	if (!is_uploaded_file ($tmp))                     
		continue;

	$buff .= "<li class=Text><b>".$name."</b>";

	if (!@move_uploaded_file($tmp, $UPLOAD_PATH."/".$name)) 
	{
		$error = 1;
		continue;
	}

	$openit = file($UPLOAD_PATH."/".$name);
	$filearray = file($UPLOAD_PATH."/".$name);
	$rowcount = count($openit);
	$validrecords = $rowcount;          
	$counter_fail = 0;
	$counter_succ = 0;
	for($j=0; $j<$rowcount; $j++)         
	{

$item = explode("$separator", $filearray[$j]);
include("query.inc");

$result = mysql_query($query);
if ($result)
{
$counter_succ++;
}
else
{
$counter_fail++;
}
}

	$buff .= "<ul class=Text><li>$counter_succ of $validrecords records added</li>";
	if ($counter_fail > 0)
	{
		$buff .= "<li class=Text>$counter_fail records <font color=red>failed</font> to be added</li>";
	}

	$buff .= "</ul></li>";
}

if (strlen ($buff) == 0)
{
	?>
	<span class=Warning>Select at least one file to upload.</span>	
	<?	
	return false;
}
else if ($error)
{
	?>
	<span class=Error>Some of the selected files have not been uploaded.</span><br>
	<span class=Text>Check the existence of upload directory: <b><?=$UPLOAD_PATH?></b> and your permissions.</span>	
	<?	

	return false;
}

?>
<span class=Message>The following files have been uploaded</span>
<ul class=Text><?=$buff?></ul>

[sneakyimp]

so what is the problem?

[l12852]

"vaule1","value2","value3" this works
value1,value2,value3 this doesn't work

[sneakyimp]

so by "doesn't work" i am guessing that you mean the query is failing, right? What, exactly, is the error? And if the error is related to the query, please let us know what query.inc contains.

Also, please use the code tags in your posts...the code will be easier to read.

[l12852]

doesn't work means it doesn't upload. it says 0 of 44 records failed to be added where when it does work it says 44 of 44 records added

Query.inc this because some of the items were made before the year 2000 but only show up as 2 digits i.e. 98 and some were made after 2000 and they show up as 05. I don't think query.inc is the problem then again it maybe. what do you think?

<?

if ($item[1]>=50 && $item[1]<=99)
{
$item[1] = $item[1] + 1900;
}
elseif ($item[1]<50)
{
$item[1] = $item[1] + 2000;
}

if ($item[2] == '') $item[2]="''";
if ($item[3] == '') $item[3]="''";
if ($item[4] == '') $item[4]="''";
if ($item[5] == '') $item[5]="''";
if ($item[6] == '') $item[6]="''";

$query = "insert into $mytable values ('', '$item[0]', '$item[1]', $item[2], $item[3], $item[4], $item[5], $item[6], '$item[7]')";
?>

[sneakyimp]

first off, you are trying to get answers to this post in two different places. double-posting is a no-no on this site so KNOCK IT OFF.

Secondly, it's probably the query that's failing because of the quote issue. You need to remove the quotes before you feed the data into the query.

Thirdly, the file is apparently 'uploading' fine meaning that it gets transferred to the server, opened and parsed, and then results in a bunch of crap sql statements that don't accomplish anything.

[l12852]

sorry about the double post, I've never been here before I didn't see the database area initially. then when you replied I got excited about it.

[l12852]

so does anybody know why
this "vaule1","value2","value3" uploads
and this value1,value2,value3 doesn't?

also when it does upload, the quotes are somehow erased or not included or stripped or something

[sneakyimp]

read my post.

1) i'm nearly certain the file is 'uploaded' just fine or you wouldn't get ANY records at all failing or succeeding.
2) the query is failing because when you include query.inc and the variable $query gets defined, the sql IS NOT VALID.

try changing this line:

$result = mysql_query($query);

to this:

$result = mysql_query($query)
  or die("QUERY FAILED! - " . mysql_error() . " - original query was " . $query);

that should display an error because your queries are not working.

Generally speaking, when you read in data from a CSV file it MAY OR MAY NOT have each field enclosed in quotes. Your query code is not smart enough to automatically handle the presence or absence of quotes in your data so you are going to have to write additional code which adds or removes quotes depending on what your data looks like. Furthermore, if the data contains any quotes (like names such as George O'Malley or Finn "the finger" McCool) then those other quotes might cause additional problems.