problem in uploading csv file in mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jamhussain
Forum Newbie
Posts: 17
Joined: Sat Apr 07, 2012 2:04 am

problem in uploading csv file in mysql database

Post by jamhussain »

I have csv file having data of Asian Countries.name of countries, years, progress in paultry .these are fields.
when I upload the file, it does not upload. it gives errors.
please observe this code and give sugestion .
How can I improve this code.
what should be structure of database so that all values in csv file should be uploaded in database.
please observe this code.

Code: Select all

 <?php  
 include("opendb.php");  
 //echo isset($_POST['submit']);  
 if(isset($_POST['submit']))  
 {  
      $filename=$_FILES['files']['name'];  
      $filename=date('U').$filename;   
      $add1 = "import/$filename";   
      move_uploaded_file($_FILES[files][tmp_name], $add1);  
      chmod($add1,0777);  
      basename($_FILES['files']['name'] );   
      $filename1="import/$filename";  
      $fcontents = file ($filename1);   
      echo sizeof($fcontents);  
      for($i=0; $i<sizeof($fcontents); $i++) {   
      $line = addslashes(trim($fcontents[$i]));   
      $arr = explode("\t", $line);   
      echo "<pre>";   
      print_r($arr);  
 //exit();   
 //echo $arr;  
 $Repl_arr=array("[","]");   
 //echo $Repl_arr;  
 foreach($arr as $key=>$val)  
 {  
      $arr[$key]=str_replace($Repl_arr,"",$arr[$key]);  
      $arr_new=$arr[$key];  
      $arr_new1 = explode(",", $arr_new);  
 }  
 //echo "<PRE>";  
 //var_dump($arr_new1);  
 /*  
 $date=explode("/", $arr_new1[0]);  
 $dd=$date[0];  
 $mm=$date[1];  
 $yy=$date[2];  
 if(strlen($dd)==1){  
 $dd="0".$dd;  
 }  
 if(strlen($mm)==1){  
 $mm="0".$mm;  
 }  
 $date1=$yy."-".$mm."-".$dd;  
 */  
 $name=$arr_new1[0];  
 $addr = $arr_new1[1];  
 //$email=$arr_new1[2];   
 //$amount=$arr_new1[3];  
 $sql = "insert into mytestupload set   
 tfield='$name',tfield1='$addr'";   
 //echo $sql;  
 //exit();  
 $qr=mysql_query($sql);  
 echo "-----------1 Row Inserted----------";  
 $sql ."<br><br>\n";  
 if($qr!=0)   
 $msg= "Data has been imported";  
 if(mysql_error()) {  
 echo mysql_error() ."<br>\n";  
 }  
 }  
 unlink($filename1);  
 unset($_POST);  
 $_POST['submit']='';  
 $_POST='';  
 //echo "<script>window.location=('addcsv.php');</script>";  
 } ?>  
 <html>  
 <head>  
 <title>Import CSV</title>  
 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">  
 <link href="../css/decorate.css" rel="stylesheet" type="text/css">  
 </head>  
 <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">  
 <table width="100%" border="0" cellspacing="0" cellpadding="0">  
 <tr>   
 <td align="center" valign="top"><table width="100%" border="0">  
 <tr>  
 <td height="25" align="center" class="label">Import CSV Tax File </td>  
 </tr>  
 </table>  
 <table width="100%" border="0" cellpadding="3" cellspacing="0">  
 <tr>  
 <td valign="top"><form action="importdata.php" method="post" enctype="multipart/form-data">  
 <table width="100%" border="1">  
 <tr>   
 <td width="30%" class="line2">File Name:</td>  
 <td width="70%" align="left" class="line2"><input name="files" type="file" class="inp"></td>  
 </tr>  
 <tr>  
 <td class="line2">&nbsp;</td>  
 <td align="left" valign="middle" class="line2">&nbsp;</td>  
 </tr>  
 <tr><td class="line2">&nbsp;</td>  
 <td align="left" valign="middle" class="line2">  
 <input name="submit" type="submit" class="buttons" value="Submit" ></td>  
 </tr>  
 </table>  
 </form></td>  
 </tr>  
 </table></td>  
 </tr>  
 </table>  
 </body>  
 </html>
Last edited by califdon on Sat Apr 07, 2012 2:28 pm, edited 1 time in total.
Reason: Moderator added syntax=php tags to make code readable. Note to poster, please always do this.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: problem in uploading csv file in mysql database

Post by califdon »

When you describe a problem, please state exactly what happens and what exact error messages are issued. It is very difficult to give advice when someone just says "it gives errors".
jamhussain
Forum Newbie
Posts: 17
Joined: Sat Apr 07, 2012 2:04 am

Re: problem in uploading csv file in mysql database

Post by jamhussain »

Dear, when I Submit the CSV file in localhost on wamp. it gives syntax errors at many places.
My second question is : what should be structure of database, I mean table and its fields so that csv file can be uploaded.
that is my file.

inflows(US$Million) 2001 2002 2003 2004 2005 2006 2007 2008 2009
developcountry 94,896 111,018 137,434 159,295 192,127 226,707 278,456 324,832 307,088






South Asia 19,172 24,137 30,366 28,694 33,924 42,523 54,041 71,598 74,850
Sub-Saharan Africa 6,599 8,367 9,538 10,767 11,944 14,160 17,362 22,874 24,245
Fragile States 2,470 2,683 3,213 4,052 4,389 4,778 5,348 5,345 4,760
Small States 4,179 4,594 4,821 5,419 5,352 6,579 8,382 11,016 9,946
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: problem in uploading csv file in mysql database

Post by califdon »

jamhussain wrote:Dear, when I Submit the CSV file in localhost on wamp. it gives syntax errors at many places.
Then list those errors. We cannot help you if you don't tell us what the problems are.
jamhussain wrote:My second question is : what should be structure of database, I mean table and its fields so that csv file can be uploaded.

that is my file.
inflows(US$Million) 2001 2002 2003 2004 2005 2006 2007 2008 2009
developcountry 94,896 111,018 137,434 159,295 192,127 226,707 278,456 324,832 307,088

South Asia 19,172 24,137 30,366 28,694 33,924 42,523 54,041 71,598 74,850
Sub-Saharan Africa 6,599 8,367 9,538 10,767 11,944 14,160 17,362 22,874 24,245
Fragile States 2,470 2,683 3,213 4,052 4,389 4,778 5,348 5,345 4,760
Small States 4,179 4,594 4,821 5,419 5,352 6,579 8,382 11,016 9,946
You are forcing me to guess, but it appears that you need fields for 9 years for each country or group. That is what is called an "unnormalized" table, and if there is any intent to later add figures for 2010 and additional years, it is unsatisfactory. If this is a one-shot project that will never be updated, it may serve a purpose, but you should understand that a spreadsheet format like that will not provide much capability to query the database for analysis. For that you need to normalize your database, which would require 2 tables. If you need to do that, you must first study relational database theory. We cannot teach you that in a forum.

You must also understand that if you are going to use those large values for any kind of calculations, you must NOT include the commas to separate the thousands. In other words, instead of 94,137 it should be 94137. Even if you aren't going to calculate anything, you should express numeric values as values (either integers or floating point), NOT character strings. You can always format the output in any way you want, but don't store them as strings.
Post Reply