Solved

php script converting csv to mysql format problem

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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 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 …

747 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

10 Experts available now in Live!

Get 1:1 Help Now