[SOLVED] CSV import into Mysql database problem

[brooksh]

I'm trying to import my csv but how do I tell it that it is ENCLOSED BY '\"' and LINES TERMINATED BY '\r\n'

 

My CSV looks like this

 

//line1 "OfficeID","Name","Phone","Extension","Address","City","State","Zip Code","Country","Fax Number","Email Address","URL","Short ID"

//line2 1,"Exact","789-9400","","Aleshia, 421 Oak","City","State","33333","","838-2117","","","ext"

//line3 2,"Martin","667-9800","","5 South Washington","City","State","33333","","456-8201","","","MPR"

 

Here is my code, but because the first line is enclosed with "" and the lines there after are enclosed with " it messes up.

 

$file = "file.csv";
$tbl = "organizations"; 
$handle = fopen ($file, "r");
mysql_query($sql);
while (!feof ($handle))
{ 
   $line = fgets($handle, 1000);

   list($OfficeID,$Name,$Phone,$Extension,$Address,$City,$State,$Zip,$Country,$Fax,$Email,$URL,$ShortID) = split("'","\'",$line);

   $sql = "insert into $tbl (OfficeID,Name,Phone,Extension,Address,City,State,Zip,Country,Fax,Email,URL,ShortID) values

   ('$OfficeID', '$Name', '$Phone', '$Extension', '$Address', '$City', '$State', '$Zip', '$Country', '$Fax', '$Email', '$URL', '$ShortID')";
mysql_query($sql);
}

[Donovan]

Here is how I did mine.  It was test results from a university scantron that was a Excel file.  I saved as a CSV and it works well.

$file_name = $HTTP_POST_FILES['file_source']['tmp_name'];

//Create the Import CSV table
$sql	= "CREATE TABLE IF NOT EXISTS ".$prefix."$table_name (
  `ReportNumber` int(3) NULL,
  `Student_ID` varchar(9) NULL,
  `Name_Last` varchar(50) NULL, 
  `Name_First` varchar(30) NULL,  
  `Total_RS` tinyint(4) NULL,
  `Total_Percent` tinyint(3) NULL,
  `Percentile` tinyint(3) NULL,
  `Subtest_RS` tinyint(3) NULL,
  `Subtest_Percent` tinyint(3) NULL
  )";    
$result = $db->sql_query($sql);	
if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}	

$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'
 INTO TABLE ".$prefix."$table_name 
 FIELDS TERMINATED BY ','	  	 
 LINES TERMINATED BY '\r'"; 
if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
$result = $db->sql_query($sql);	

 

 

[brooksh]

sorry but it didn't work, and I did not get any errors.

[aztec]

Hello

 

I used a program called SQLyog which will allow you to inport a CVS file. Sorry I cannot be more specific but it was some time ago. I know it worked with an Excel sheet I needed to get into mysql, you can download it for free at some sites.

 

I also remember that when I searched there were other programs that will do what you require.

 

Regards

[brooksh]

It needs to be done by a script via cron.

[resago]

split by ,

 

strip the "

 

 

[resago]

change

= split("'","\'",$line);

 

to

 

= split(",",str_replace('"','',$line));

 

 

[brooksh]

Thank you. What about skipping line 1?

[resago]

read line 1 before entering the loop

 

[brooksh]

How could I do that? I tried

 

fopen ($file[0], "r"); and I tried echo $file[0];

 

[resago]

mysql_query($sql);

while (!feof ($handle))

{

  $line = fgets($handle, 1000);

 

to

 

mysql_query($sql);

$line = fgets($handle, 1000);

while (!feof ($handle))

{

  $line = fgets($handle, 1000);

 

 

[craygo]

use fgetcsv instead

 

<?php
$row = 0;
$handle = fopen($filename, "r");
while(($data = fgetcsv($handle, 1000, ",")) !== FALSE){
$row++;  // will skip first row which contains field names
  if($row > 1){
     $sql = "insert into $tbl (OfficeID,Name,Phone,Extension,Address,City,State,Zip,Country,Fax,Email,URL,ShortID) values

   ('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."', '".$data[5]."', '".$data[6]."', '".$data[7]."', '".$data[8]."', '".$data[9]."', '".$data[10]."', '".$data[11]."', '".$data[12]."')";
  }
}
?>

 

Ray

 

[resago]

SSDN