[SOLVED] loop problem while uploading csv file into the database

suma237 · July 9, 2007

Why this error?

'ÐÏà¡±á',create table temp1(id int(20) not null auto_increment,ÐÏà¡±á VARCHAR (200) not null,extra varchar(200) not null, primary key(id))could not create

The code is:

<?

//include("../mysqlconnect.php");

$link = mysql_connect("localhost","root","")

or die("Could not connect: " . mysql_error());

mysql_select_db("suma");

include("include/upload.inc.php");

if(isset($_POST['upload']))

{

$deli=$_POST['delimeter'];

$userfile=$_FILES['userfile']['name'];

$userfile_tmp=$_FILES['userfile']['tmp_name'];

$userfile_type=$_FILES['userfile']['type'];

$userfile_size=$_FILES['userfile']['size'];

$dir="upload";

$filename=basename($userfile);

move_uploaded_file("$userfile_tmp","$dir/$filename");

$row= 1;

$fp = fopen("$dir/$userfile", "r");

$s="";

$s1="";

$s2="";

while (($data = fgetcsv($fp, 100000, "$deli")) !== FALSE)

{

$num = count($data);

$row++;

if($row==2)

{

for ($c=0; $c < $num; $c++)

{

$s.=$data[$c]." VARCHAR (200) not null,";

$s2.=$data[$c].",";

$s1.="'$data[$c]',";

}

$sql="create table temp1(id int(20) not null auto_increment,".$s."extra varchar(200) not null, primary key(id))";

mysql_query($sql)or die("could not create");

$sql1="insert into temp1 (".$s2."extra) values (".$s1."'choose')";

mysql_query($sql1)or die("could not insert");

}

if($row > 2)

{

$s1="";

for ($c=0; $c < $num; $c++)

{

$s1.="'$data[$c]',";

}

$sql2="insert into temp1 (".$s2."extra) values (".$s1."'choose')";

mysql_query($sql2)or die("could not insert");

}

fclose($fp);

}

?>

<table>

<td width="60%" height="33"> <div align="left">Please Enter the delimeter

</div></td>

</tr>

<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>

</tr>

</form>

</table>

suma237 · July 9, 2007

please help me

suma237 · July 9, 2007

bump

redarrow · July 9, 2007

use mysql statement to get the infomation in the database ok.

take a look at a prevous post

<?php
$row = 1;
$handle = fopen("jos_muse_users1.csv", "are");
while (($data = fgetcsv($handle, 5774, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//---- database
$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'MCJoomla';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

$query = "INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database
} //<--this one to close the while loop
fclose($handle);
?>

suma237 · July 9, 2007

how can i check whether the file is reading or not?

the while loop is not working .The *.csv file is uploading in to the folder

matto · July 9, 2007

To answer your question: How can I check if the file is reading or not....

$fp = fopen("$dir/$userfile", "r") or die("<tt>unable to read file</tt>\n");

Also, when you are creating a "temp" table you really should use the following.

create temporary table if not exists temp1...

If your code above is run more than once your table "temp1" will already exist unless you drop the table before the script has finished executing. Looks like you may be better to have a permanent table and delete all rows at the start of your script and then load the cvs data in....

suma237 · July 9, 2007

how to solve this error?

ÐÏà¡±á

INSERT INTO test(id,name,address) VALUES (ÐÏà¡±á)

The content of csv file is

id name address

1 abc dsdd

2 xyz dsfdd

aniesh82 · July 9, 2007

Hi Suma,

Are you sure that the csv file uploaded and copy to the directory?

Please make sure that first.

If the file is moved to teh directory in the server, then please go through the following piece of code. I used this to read the cotenst from a file and insert into the table.

$handle = fopen($city,"r");

while(($data = fgetcsv($handle, 1000, ",")) !== FALSE)

{

$countryCode = $data[0];

$cityName = $data[2];

$latitude = $data[3];

$longitude = $data[4];

$sql = 'INSERT INTO nameCity (countryCode , cityName , latitude, longitude ) VALUES ("'.$countryCode.'", "'.$cityName.'","'.$latitude.'","'.$longitude.'")';

mysql_query($sql);

}

suma237 · July 9, 2007

how to store the content in csv file?

suma237 · July 9, 2007

one field (name) is inserting in the table .how to insert second field?

code:

$row = 1;

$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

$num = count($data);

echo "<p> $num fields in line $row: <br /></p>\n";

$row++;

$SQLq ="";

if($row==2)

{

for ($c=0; $c < $num; $c++) {

echo $data[$c] . "<br />\n";

$SQLq .= "$data[$c],";

}

$SQLq = trim($SQLq, ",");

$query = "INSERT INTO test(name,address) VALUES ($SQLq)";

echo $query;

$sql = mysql_query($query) or die (mysql_error());

//--- database

}

} //<--this one to close the while loop

fclose($handle);

Yesideez · July 9, 2007

Suma, if you post code please try and surround it with [code] and [/code] tags as it makes it a lot easier to read.

suma237 · July 9, 2007

code is..

<?
//---- database
$dbhost = 'localhost';
$dbusername = 'root';
$dbpasswd = '';
$database_name = 'suma';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

include("include/upload.inc.php");
if(isset($_POST['upload']))
{


$userfile=$_FILES['userfile']['name'];
$userfile_tmp=$_FILES['userfile']['tmp_name'];
$userfile_type=$_FILES['userfile']['type'];
$userfile_size=$_FILES['userfile']['size'];
$dir="upload";
$filename=basename($userfile);
move_uploaded_file("$userfile_tmp","$dir/$filename");


//echo "$dir"."/"."$userfile";


$row = 1;
$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
if($row==2)
{
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");


$query = "INSERT INTO test(name,address) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database
} //<--this one to close the while loop
fclose($handle);




}//end of isset



?>

<table>

<form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">


<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>




</tr>
<tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
</form>
</table>

Yesideez · July 9, 2007

I've just pasted your code into my PHP editor and is the problem lying with the INSERT statement?

suma237 · July 9, 2007

the query is executing and 'name' field is inserting into the table ,but address field is not inserting.why?

Yesideez · July 9, 2007

When writing scripts its actually quite important to try and tabulate the code to make it easier to read. If in say a month or even a year's time you come back to your script what do you reckon the chances are that you'd be able to read it quickly without having to step through it figuring out what has been done?

<?php
//---- database
  $dbhost = '192.168.1.60';
  $dbusername = 'root';
  $dbpasswd = '';
  $database_name = 'suma';
  $connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server.");
  $db = mysql_select_db("$database_name", $connection) or die("Couldn't select database.");
  include("include/upload.inc.php");
  if (isset($_POST['upload'])) {
    $userfile=$_FILES['userfile']['name'];
    $userfile_tmp=$_FILES['userfile']['tmp_name'];
    $userfile_type=$_FILES['userfile']['type'];
    $userfile_size=$_FILES['userfile']['size'];
    $dir="upload";
    $filename=basename($userfile);
    move_uploaded_file("$userfile_tmp","$dir/$filename");
    //echo "$dir"."/"."$userfile";
    $row = 1;
    $handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
    while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
      $num = count($data);
      echo "<p> $num fields in line $row: <br /></p>\n";
      $row++;
      $SQLq ="";
      if ($row==2) {
        for ($c=0; $c < $num; $c++) {
          echo $data[$c] . "<br />\n";
          $SQLq .= "$data[$c],";
        }
        $SQLq = trim($SQLq, ",");
        $query = "INSERT INTO test (name,address) VALUES ($SQLq)";
        echo 'SQL='.$query;
        //$sql = mysql_query($query) or die (mysql_error());
        //--- database
    } //<--this one to close the while loop
    fclose($handle);
  }//end of isset
?>
<table>
  <form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">
    <tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td></tr>
    <tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
  </form>
</table>

If you can try that and post here the line you get starting with SQL= we'll take it from there and see what happens. It won't actually run the query as I've disabled it temporarily so we can concentrate on debugging the INSERT query.

Yesideez · July 9, 2007

Oh one last thing. I couldn't seem to find a closing } while sorting the tabulating out - are you missing one or have I read it wrong?

suma237 · July 9, 2007

please check the code with proper alignment.Thanks for your advice

<?
//---- database
$dbhost = '192.168.1.60';
$dbusername = 'root';
$dbpasswd = '';
$database_name = 'suma';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

include("include/upload.inc.php");
if(isset($_POST['upload']))
{


$userfile=$_FILES['userfile']['name'];
$userfile_tmp=$_FILES['userfile']['tmp_name'];
$userfile_type=$_FILES['userfile']['type'];
$userfile_size=$_FILES['userfile']['size'];
$dir="upload";
$filename=basename($userfile);
move_uploaded_file("$userfile_tmp","$dir/$filename");



$row = 1;
$handle = fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n");
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
			{

			$num = count($data);
			echo "<p> $num fields in line $row: <br /></p>\n";
			$row++;
		$SQLq ="";
			if($row==2)
					{
					for ($c=0; $c < $num; $c++)
						{
							echo $data[$c] . "<br />\n";
							$SQLq .= "$data[$c],";
						}
			$SQLq = trim($SQLq, ",");


			$query = "INSERT INTO test(name,address) VALUES ($SQLq)";
			echo $query;
			$sql = mysql_query($query) or die (mysql_error());
			//--- database

					}
		} //<--this one to close the while loop
fclose($handle);








}//end of isset



?>

<table>

<form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">


<tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td>




</tr>
<tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
</form>
</table>

Yesideez · July 9, 2007

It takes long time to replace the spaces and tabs - I've added some proper error checking:

<?php
//---- database
  $dbhost='192.168.1.60';
  $dbusername='root';
  $dbpasswd='';
  $database_name='suma';
  $connection=mysql_pconnect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server.");
  $db=mysql_select_db("$database_name", $connection) or die("Couldn't select database.");
  include("include/upload.inc.php");
  if (isset($_POST['upload'])) {
    $userfile=$_FILES['userfile']['name'];
    $userfile_tmp=$_FILES['userfile']['tmp_name'];
    $userfile_type=$_FILES['userfile']['type'];
    $userfile_size=$_FILES['userfile']['size'];
    $dir="upload";
    $filename=basename($userfile);
    if (move_uploaded_file("$userfile_tmp","$dir/$filename")) {
      //echo "$dir"."/"."$userfile";
      $row = 1;
      if ($handle=fopen("$dir/$userfile", "r")or die("<tt>unable to read file</tt>\n")) {
        while (($data = fgetcsv($handle, 10000, ",")) !== FALSE) {
          $num = count($data);
          echo "<p> $num fields in line $row: <br /></p>\n";
          $row++;
          $SQLq =$data[0];
          if ($row>1) {
            for ($c=1; $c < $num; $c++) {
              echo $data[$c] . "<br />\n";
              $SQLq .= ",$data[$c]";
            }
            $SQLq = trim($SQLq, ",");
            $query = "INSERT INTO test(name,address) VALUES ($SQLq)";
            echo 'SQL='.$query.'<br />';
            if (!mysql_query($query)) {echo 'Error at line '.($row-1).'<br />';
          }
          //--- database
        } //<--this one to close the while loop
        fclose($handle);
      } else {echo 'Error opening file';}
    } else {echo 'Error uploading file';}
  }//end of isset
?>
<table>
  <form action="" method="post" enctype="multipart/form-data" name="form3" id="manu">
    <tr><td>Upload CSV file</td><td><input name="userfile" type="file"></td></tr>
    <tr><td colspan="2">  <input type="submit" name="upload" value="upload"></td></tr>
  </form>
</table>

If you save that out as a new file and upload it see if it works. I've changed the way the routine builds the INSERT query slightly.

suma237 · July 9, 2007

thanks for your valuable time

Yesideez · July 9, 2007

Does it work?

suma237 · July 9, 2007

no..im tired with this code.no error but only 1 field is inserting.what to do?

can you help me?really in need the code.

Yesideez · July 9, 2007

A line in the above code is echo'ing a line to your browser which starts with "SQL=" and this is the contents of the INSERT query which is being called. If you show me this line we will be able to see what is going wrong.

If you can also show the first couple lines of your CSV file we can then compare that with what is being used in the query.

suma237 · July 9, 2007

you r right...insert statement is showing error

error

2 fields in line 1:

Address

SQL=INSERT INTO test(name,address) VALUES (Name,Address)

Error at line 1

2 fields in line 2:

shanthalayam

SQL=INSERT INTO test(name,address) VALUES (suma,shanthalayam)

Error at line 2

2 fields in line 3:

21 Flat

SQL=INSERT INTO test(name,address) VALUES (shilpa,21 Flat)

Error at line 3

2 fields in line 4:

23 tower road

SQL=INSERT INTO test(name,address) VALUES (kumar,23 tower road)

Error at line 4

suma237 · July 9, 2007

why that error is coming?

csv format is

suma,52 flat34

shilpa,21 Flat

kumar,23 tower road

Yesideez · July 9, 2007

Now we're getting somewhere.

In your table have you got `name` and `address` and are these fields either VARCHAR or TEXT?

Next change these lines:

          $SQLq ="'".$data[0]."'";

            $query = "INSERT INTO test (`name`,`address`) VALUES (".$SQLq.")";

See if that works and show the browser output.

Sign In

[SOLVED] loop problem while uploading csv file into the database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information