Page 1 of 1

problem in uploading csv file in mysql database

Posted: Sat Apr 07, 2012 12:28 pm
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>

Re: problem in uploading csv file in mysql database

Posted: Sat Apr 07, 2012 2:35 pm
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".

Re: problem in uploading csv file in mysql database

Posted: Sat Apr 07, 2012 10:40 pm
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

Re: problem in uploading csv file in mysql database

Posted: Sun Apr 08, 2012 3:45 pm
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.