csv import which contains comma in data field | Newbie | | Join Date: Mar 2008
Posts: 2
| | |
Can anyone find me a solution.
I want to import csv file into mysql database. I have the code to insert.But the rows that contains comma in data field is not getting inserted and is skipped.All the other rows are inserted.
This is the code that i have..
[php]@mysql_close($con);
$con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
mysql_select_db("test") or die ("no database");
$databasetable = "$file3";
$lines = 0;
$queries = "";
$linearray = array();
foreach(split($lineseparator,$csvcontent) as $line) {
$count = $count + 1;
$lines++;
$line = trim($line," \t");
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results=preg_split($expr,trim($line));
$results1= preg_replace("/^\"(.*)\"$/","$1",$results);
$linearray = explode($fieldseparator,$results1);
$linemysql = implode("','",$linearray);
if($addauto)
{
$query = "insert into $databasetable VALUES('$count','$linemysql');";
$result1 = mysql_query($query) or die("didn't query for insert");
}
else
$query = "insert into $databasetable values('$linemysql');";
$queries .= $query . "\n";
@mysql_query($query);
}
echo " CSV File imported to the database";
@mysql_close($con);
[/php]
It is inserting all the rows except the row that has comma in it.I couldn rectify it.Can anyone help me out please.. thank u.
|  | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: csv import which contains comma in data field
Welcome to The Scripts!
Please enclose your posted code in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use [code] tags in future.
MODERATOR
| | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: csv import which contains comma in data field
Your code if very difficult to follow, without any comments. And whatever happened to the good old (standard PHP) fgetcsv() function?
To get some insight into the code, what I miss is what is the content of the reg.ex in the splits, i.e. $lineseparator and $fieldseparator.
Since the record is not inserted into the db, it would be handy to know how the sql statement looks like, so, for debug reasons, add an echo to each of the SQL statements before you execute it.
(The last mysql_query is not even tested for errors, so how do you know what happens?)
Ronald
| | Newbie | | Join Date: Mar 2008
Posts: 2
| | | re: csv import which contains comma in data field
This is the full coding -
$path = $fileloc;
-
$file = basename($path); // $file is set to "index.php"
-
-
$databasehost = "localhost";
-
$databasename = "test";
-
-
$databaseusername ="test";
-
$databasepassword = "";
-
$fieldseparator = ",";
-
$lineseparator = "\n";
-
$csvfile = $fileloc;
-
-
-
$addauto =1;
-
-
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("$hostname","$user","$pass") or die("did'nt connect to mysql");
-
mysql_select_db("test") or die ("no database");
-
$result = mysql_query("SELECT * FROM ex order by count");
-
$count=0;
-
while($row = mysql_fetch_array($result))
-
{
-
$count= $row['count'];
-
}
-
if($count==0)
-
$count=0;
-
else
-
$count=$count;
-
-
-
@mysql_close($con);
-
$con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
-
mysql_select_db("test") or die ("no database");
-
-
$databasetable = "$file3";
-
$lines = 0;
-
$queries = "";
-
$linearray = array();
-
-
foreach(split($lineseparator,$csvcontent) as $line) {
-
$count = $count + 1;
-
-
$lines++;
-
-
$line = trim($line," \t");
-
-
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
-
-
$results=preg_split($expr,trim($line));
-
-
$results1= preg_replace("/^\"(.*)\"$/","$1",$results);
-
-
$linearray = explode($fieldseparator,$results1);
-
-
$linemysql = implode("','",$linearray);
-
-
if($addauto)
-
{
-
$query = "insert into $databasetable VALUES('$count','$linemysql');";
-
$result1 = mysql_query($query) or die("didn't query for insert");
-
}
-
else
-
$query = "insert into $databasetable values('$linemysql');";
-
$queries .= $query . "\n";
-
@mysql_query($query);
-
}
-
@mysql_close($con);
-
|  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
