Sunday 4 March 2012

Insert csv file in phpmyadmin

<?php

// Connecting To database

mysql_connect("localhost", "root", "root");
$result = mysql_select_db("sales_diamond");




// Readiong CSV File
$row=1;
$arrResult = array();
$handle = fopen("5-usa.csv", "r");  // CSV FILE
if( $handle ) {
while (($data = fgetcsv($handle,1000, ",",'"')) !== FALSE) { // terminated by ;
   
$arrResult[] = $data;


}


$query='CREATE TABLE IF NOT EXISTS `product_info` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `'.$arrResult[0][0].'` varchar(50),
  `'.$arrResult[0][2].'` varchar(50),
  `'.$arrResult[0][3].'` varchar(50),
  `'.$arrResult[0][4].'` varchar(50),
  `'.$arrResult[0][5].'` varchar(50),
  `'.$arrResult[0][6].'` varchar(50),
  `'.$arrResult[0][7].'` varchar(50),
  `'.$arrResult[0][9].'` varchar(50),
  `'.$arrResult[0][10].'` varchar(50),
  `'.$arrResult[0][11].'` varchar(50),
  `'.$arrResult[0][12].'`  varchar(50),
  `'.$arrResult[0][13].'` varchar(50),
  `'.$arrResult[0][14].'` varchar(50),
  `'.$arrResult[0][15].'` varchar(50),
  `'.$arrResult[0][16].'` varchar(50),
  `'.$arrResult[0][17].'` varchar(50),
  `'.$arrResult[0][20].'` varchar(50),
  `'.$arrResult[0][21].'` varchar(50),
  `'.$arrResult[0][24].'` varchar(50),
`'.$arrResult[0][25].'` varchar(50),
`'.$arrResult[0][26].'` varchar(50),
`'.$arrResult[0][27].'` varchar(50),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
';

$res=mysql_query($query);


if($res)
{

$insQuery='';

for($r=1;$r<count($arrResult);$r++)
{
   
    $insQuery ='INSERT INTO `product_info`(`id`,`Lot #`,`Shape`,`Carat`,`Color`, `Clarity`,`Cut Grade`,`Price`,`Cert`,`Depth`,`Table`,`Girdle`,`Culet`,`Polish`, `Sym`,`Fluor`, `Meas`,`Cert #`, `Stock #`,`City`,`State`,`Country`, `Image`) VALUES
              (NULL,"'.$arrResult[$r][0].'","'.$arrResult[$r][2].'",
              "'.$arrResult[$r][3].'","'.$arrResult[$r][4].'","'.$arrResult[$r][5].'",
              "'.$arrResult[$r][6].'","'.$arrResult[$r][7].'","'.$arrResult[$r][9].'",
              "'.$arrResult[$r][10].'","'.$arrResult[$r][11].'","'.$arrResult[$r][12].'","'.$arrResult[$r][13].'","'.$arrResult[$r][14].'","'.$arrResult[$r][15].'","'.$arrResult[$r][16].'","'.$arrResult[$r][17].'","'.$arrResult[$r][20].'","'.$arrResult[$r][21].'","'.$arrResult[$r][24].'","'.$arrResult[$r][25].'","'.$arrResult[$r][26].'","'.$arrResult[$r][27].'")';
  
    


mysql_query($insQuery) or die(mysql_error());
 

echo "Record Added".$r."<br>";     
}

}




echo "Record Added";

//print_r($arrResult);







fclose($handle);
}



?>

No comments:

Post a Comment