Solved

php script converting csv to mysql format problem

Posted on 2010-11-17
4
409 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
4 Comments
 
LVL 4

Assisted Solution

by:max-hb
max-hb earned 166 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 168 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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now