[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

php script converting csv to mysql format problem

Posted on 2010-11-17
4
Medium Priority
?
417 Views
Last Modified: 2012-05-10
Hi there,

I have csv data that I wish to import to a mySQL database.  I have been using phpmyadmin to do this without any problems, but now I need to have a script to to convert the csv file to mysql because the new person doing the data upload will not have access to phpmyadmin.

I believe what is happening is commas that are part of a text field being imported are causing the script I'm using to believe there are additional fields.

Below is one line of the csv file I need to import:
---------------------------------------------------------------------------------------------------
9780919317444,Anchorage and Marine Parks,Updated and Revised,,1283,29.95,Paperback,CAD,9,6,Inches,320,15/01/2008,Active,"<p class=""book_description"">An invaluable reference to anchorages and marine parks on the British Columbia coast and in the San Juan Islands. Hundreds of color photos, mostly aerials, useful maps and diagrams, GPS coordinates and expanded coverage on the west coast of Vancouver Island. The many anchorages are illustrated with diagrams and photos that include all the popular cruising spots, such as the Gulf Islands, and the Inside Passage. Scuba diving and kayaking regions are also included in this user friendly book.</p>",SPO005000,,,,
------------------------------------------------------------------------------------------------

The php code I've attached to this post is what I am using to convert is the data (downloaded from http://legend.ws/blog/tips-tricks/csv-php-mysql-import/).

I believe the problem is that the text fields are being enclosed in double quotes, and the php script isn't picking this up, causing additional commas to be inserted, making the database think the column count is off.

-------------------------------------------------------------------------------------------------
//example of csv text field enclosed in quotes
"<p class=""book_description"">An invaluable reference to anchorages and marine parks on the British Columbia coast and in the San Juan Islands. Hundreds of color photos, mostly aerials, useful maps and diagrams, GPS coordinates and expanded coverage on the west coast of Vancouver Island. The many anchorages are illustrated with diagrams and photos that include all the popular cruising spots, such as the Gulf Islands, and the Inside Passage. Scuba diving and kayaking regions are also included in this user friendly book.</p>",
------------------------------------------------------------------------------------------------

Any help on how to go about eliminating new fields on the commas in this text description would be greatly appreciated.

Thanks!

<?php

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "host";
$databasename = "dbname";
$databasetable = "dbtable";
$databaseusername ="dbuser";
$databasepassword = "dbpass";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "csv/csvfile.csv";
/********************************/
/* Would you like to add an empty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 1;
$outputfile = "csv/convertedsql.sql";
/********************************/


if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\n";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\n";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

	$lines++;

	$line = trim($line," \t");
	
	$line = str_replace("\r","",$line);
	
	/************************************
	This line escapes the special character. remove it if entries are already escaped in the csv file
	************************************/
	$line = str_replace("'","\'",$line);
	/*************************************/
	
	$linearray = explode($fieldseparator,$line);
	
	$linemysql = implode("','",$linearray);
	
	if($addauto)
		$query = "insert into $databasetable values('','$linemysql');";
	else
		$query = "insert into $databasetable values('$linemysql');";
	
	$queries .= $query . "\n";

	@mysql_query($query);
}

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\n";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\n";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

echo "Found a total of $lines records in this csv file.\n";


?>

Open in new window

0
Comment
Question by:ims1010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 4

Assisted Solution

by:max-hb
max-hb earned 664 total points
ID: 34158402
Parsing csv files can greatly benefit from PHPs built in csv functions (fgetcsv(),fputcsv()).
<?php

/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "host";
$databasename = "dbname";
$databasetable = "dbtable";
$databaseusername ="dbuser";
$databasepassword = "dbpass";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "csv/csvfile.csv";
/********************************/
/* Would you like to add an empty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 1;
$outputfile = "csv/convertedsql.sql";
/********************************/


if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\n";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\n";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	exit;
}

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

while ( ($line = fgetcsv ($handle, 1000, ",")) !== FALSE ) {
	$lines++;
	
	$linemysql = implode("','",$line);
	
	if($addauto)
		$query = "insert into $databasetable values('','$linemysql');";
	else
		$query = "insert into $databasetable values('$linemysql');";
	
	$queries .= $query . "\n";

	@mysql_query($query);
}

flose($file);

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\n";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\n";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

echo "Found a total of $lines records in this csv file.\n";


?>

Open in new window

0
 
LVL 12

Accepted Solution

by:
zappafan2k2 earned 672 total points
ID: 34158473
I think you should have a look at the fgetcsv function:
http://php.net/manual/en/function.fgetcsv.php

Something like

<?php

while ($data = fgetcsv($csvfile)) {       // check the parameters here if the delimiters change
	$data = array_map('mysql_real_escape_string',$data);
	// at this point, $data contains an array of escaped strings ready for 
        // insertion into the database or into a sql file
	}

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 664 total points
ID: 34158937
The script does not look technically competent to me, and judging from the comments at http://www.legend.ws/blog/tips-tricks/csv-php-mysql-import/ some others have had trouble with it, too.

This is not a direct solution to your problem, but it is something I wrote for another requirement and it might be useful as a starting point.  The data had column names in the first row and data values after that.
<?php // RAY_csv_to_db.php
error_reporting(E_ALL);
echo "<pre>\n";  //READABILITY FOR var_dump()

// PHP 5.1+  SEE http://us3.php.net/manual/en/function.date-default-timezone-set.php
date_default_timezone_set('America/Chicago');


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php
// MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// SET THE NAMES OF THE DATE FIELDS
$dts = array('CreatedDate', 'ModifiedDate');


// TEST DATA, SAVED FROM THE POST AT EE
$csv = "http://laprbass.com/RAY_csv_to_db.csv";
$fpo = fopen($csv, 'r');
if (!$fpo ) die('CRUMP');

// GET THE FIELD NAMES FROM THE TOP OF THE CSV FILE
$top = fgetcsv($fpo);

// SEE IF THE ARRAY CAN BE MUNGED INTO DATA BASE FIELD NAMES
$cnt = count($top);
$new = array();
foreach ($top as $p => $value)
{
    $new[] = strtolower($value); // NORMALIZE TO LOWERCASE (MySQL NOT CASE SENSITIVE)
}
// NAMES OF THE DATA BASE TABLE COLUMNS
$new = array_unique($new);

$nct = count($new);
if ($nct != $cnt)
{
    die('NON-UNIQUE COLUMN NAMES');
}
// HOW MANY UNIQUE COLUMNS?
echo "<br/>UNIQUE FIELDS: $cnt \n";

// SET UP KEY NAMES FOR USE IN OUR QUERY
$query_cols = implode(',', $top);


// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();
$empies = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

// GET A RECORD
    $csvdata = fgetcsv($fpo);

// SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;
    $str = trim(implode('', $csvdata));
    if (empty($str))
    {
        $empties[] = $counter;
        continue;
    }

// CHECK THE NUMBER OF FIELDS
    if ($cnt != count($csvdata))
    {
        $errors[] = $counter;
        continue;
    }

// ASSIGN KEY NAMES TO THE VALUES IN THE CSV FIELDS
    $csvdata = array_combine($top, $csvdata);

// ACTIVATE THIS TO SEE THE ASSOCIATIVE ARRAY
//  var_dump($csvdata);

// CONVERT DATES TO ISO8601 AND ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        if (in_array($ptr, $dts))
        {
            $value = date('c', strtotime($value));
        }
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

// SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

// SET UP A QUERY
    $sql = "REPLACE INTO myTable ( $query_cols ) VALUES ( $query_data )";

// RUN THE QUERY HERE....
    var_dump($sql);

}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter \n";

// SHOW THE NUMBERS OF THE ROWS WITH THE WRONG NUMBER OF FIELDS
if (count($errors))
{
    echo "<br/>ROWS WITH THE WRONG NUMBER OF FIELDS: \n";
    print_r($errors);
}

// SHOW THE NUMBERS OF THE ROWS WITH EMPTY FIELDS
if (count($empties))
{
    echo "<br/>ROWS WITH ALL FIELDS EMPTY: \n";
    print_r($empties);
}

Open in new window

0
 

Author Closing Comment

by:ims1010
ID: 34160307
Thanks for all the help!  I used information from each of your solutions to write a script from scratch to solve my problem.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question