problem with import script - CSV to MySQL 2

[mrsbean]

Using a script I found in this forum

http://www.tek-tips.com/viewthread.cfm?qid=1103485

I managed to import a csv file into a MySQL table. I need assistance to work out one bug. Some of the lines in the csv file contain commas. When a line contains commas, the data doesn't go into the right columns starting from the first column with contained a comma.

How can I work around this?

The code for my import (based on the thread mentioned above) is below

Thanks in advance,

MrsBean

//empty out the import (temporary) table
$sql = "delete from ImportMemberUpdate ";
$result = MYSQL_QUERY($sql);

$filename = "ImportMemberUpdate.csv";
$fp = fopen($filename, "r") or die("Failed to open flat file");
echo "Opened flat file ok...<br>";
flock($fp, LOCK_EX); // lock the file while we got it open

// the first line of the csv usually contains
// the descriptions of the columns. We don't want
// to write this into the db so set a flag

//note - I left this in, but it doesnt work
//the first line (column names) gets imported

$firstline = true;

// now we read each line and write that to the database
while (!feof($fp)){
  if ($firstline) {
    // skip this iteration of the while loop
    // and carry on with next line
    $firstline = false;
    continue;
  }

  // read this line
  $line = fgets($fp,16384);

  // remove any ' and replace with `
  $line = str_replace("'", "`", $line);
  
  // next split this line into it's componates
  $delim = ",";
  $data = explode($delim, $line);
  // now $data will be an array contining
  // all the fields. eg the first field will be
  // $data[0] and the second $data[1] etc
  // do any processing you want with these now
  
    
  // now we have all the luvly data ...
  $sql = "insert into ImportMemberUpdate values (";
  $sql .= $data[0] . ", " ;  // a numberic value
  $sql .= "'" . $data[1] . "', "; // a string
  $sql .= "'" . $data[2] . "', "; // a string

snipped part of the code

  $sql .= "'" . $data[29] . "')"; // the last value

  // remember that string value must be enclosed in
  //  quotes ' and numeric don't


  echo "<p>$sql</p>";
  $result = mysql_query($sql) ;
   
 }

echo "all done we hope! <br>";

flock($fp, LOCK_UN); // unlock the file

 

[sleipnir214]

What does your CSV look like? How are string-internal commas to be differentiated from field-separation commas?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[mrsbean]

I opened the csv in notepad and modified the 1st record for demonstration purposes. Here is the first record and the column names:

wsuaaID,username,password,GradYear,FirstName,LastName,FirmName,Address,City,State,Zip,Phone,Email,ListDir,AreasPrac,LangSpoken,MoreInvolved,MCLE,AlumniAssoc,Newsletter,Scholarship,Socials,Directory,Fundraising,Mentoring,ProBono,Website,Philanthropic,Committees,Other
1,someusername,somepass2222,1974,Somebody,Somewhere,XYZ,1234 Some street,Santa Ana,CA,92706,714-555-4444,somebody@somewhere.com,1,,,0,0,0,0,0,0,0,0,0,0,0,0,0,

The problem occurs when somebody has a suffix included with their last name with a comma in the field or when the address includes something like "1234 Some Street, Suite 222"

I tried escaping the internal commas from within Excel by using a find replace ... Find "," and Replace with "\,"

This didn't quite work right either. Is there someway to get excel to save a CSV file with quotes around all text values?

MrsBean

 

[sleipnir214]

Why not just get Excel to output a CSV with tabs as the delimiter instead of commas?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

As I recall, you just save the file as the type [tt]Text (tab delimited)[/tt] or something like that.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[mrsbean]

The idea to use tab delimited instead of comma separated got me a lot closer, but I'm only part way there. The values which contained a comma within the contents of a field now have double quotes wrapped around the contents of the field. For example, instead of the Address column reading

1234 Some Street

it reads

"1234 Some Street"

I know that I could use a query to find and replace " when it comes at the very beginning or the very end of the field, but I'm wondering if there is something else I can do to the script. Thank you all for your help.

MrsBean

 

[sleipnir214]

Then remove the quotes before inserting the record into MySQL.

preg_replace() can remove a quote at the beginning or end of a string. I think:

$value = preg_replace ('/^"/', '', $value);

will remove a quote at the beginning of a string.


Of course, going back to the other thread you quoted, MySQL's LOAD DATA command can use the clause OPTIONALLY ENCLOSED BY to specify that some fields may (or may not) be enclosed by a character.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

could you try using fgetcsv intead?

http://www.php.net/fgetcsv

you can get rid of the string identifiers within the call

fgetcsv ( $fh, 2048, "\t", "\""); //for tab delimeted files

 

[sleipnir214]

jpadie:
Thanks for that link. I knew there was a builtin function for parsing a CSV file, but I couldn't find it in the manual.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

@ sleipnir214
my pleasure

@ OP and readers

in an idle moment I have recast mrs Bean's code to use fgetcsv and some other timesavers.

personally, for an import like this i'd rather build a text file of insert queries and import them from phpmyadmin but for those that are confident of their data (!):

<?
//empty out the import (temporary) table
$sql = "delete from ImportMemberUpdate ";
$result = MYSQL_QUERY($sql);

$filename = "ImportMemberUpdate.csv";
$fp = fopen($filename, "rb") or die("Failed to open flat file");
echo "Opened flat file ok...<br>";
flock($fp, LOCK_EX); // lock the file while we got it open

$fbad = fopen("badqueries.txt", "wbt");
$fb = 0;

$firstline = true;
if ( version_compare("5.0.0", phpversion()) < 0) {
	$len = 16384;
} else {
	$len = 0; 
}

// now we read each line and write that to the database
while (($data = fgetcsv($fp, $len, "\t", "\"")) !== FALSE) {
   $sql = "";
   foreach ($data as $val) {
		$sql .= "'".mysql_escape_string($val)."',";   
   }
   $sql = "Insert 
   			Into ImportMemberUpdate
			Values (".rtrim($sql,",").")";
	if (!$firstline) {
		$result = @mysql_query($sql);
		$firstline = false;
	}
	if (!$result) {
		fwrite ($fbad, $sql);
		fwrite($fbad, mysql_error(). "\r\n");
		$fb++;
	} else {
	echo $sql . " executed correctly \r\n <br/>";
	}
}
fclose($fbad);
fclose($fp);
flock($fp, LOCK_UN); // unlock the file
echo "$fb queries did not process correctly.  Check badqueries.txt for details";
?>