Solved

php script converting csv to mysql format problem

Posted on 2010-11-17
4
410 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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

776 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