By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,143 Members | 1,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,143 IT Pros & Developers. It's quick & easy.

csv import which contains comma in data field

P: 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.
Mar 25 '08 #1
Share this Question
Share on Google+
3 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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
Mar 25 '08 #2

ronverdonk
Expert 2.5K+
P: 4,258
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
Mar 25 '08 #3

P: 2
This is the full coding
Expand|Select|Wrap|Line Numbers
  1. $path = $fileloc;
  2. $file = basename($path);         // $file is set to "index.php"
  3.  
  4. $databasehost = "localhost";
  5. $databasename = "test";
  6.  
  7. $databaseusername ="test";
  8. $databasepassword = "";
  9. $fieldseparator = ",";
  10. $lineseparator = "\n";
  11. $csvfile = $fileloc;
  12.  
  13.  
  14. $addauto =1;
  15.  
  16. if(!file_exists($csvfile)) {
  17.     echo "File not found. Make sure you specified the correct path.\n";
  18.     exit;
  19. }
  20.  
  21.  
  22. $file = fopen($csvfile,"r");
  23.  
  24. if(!$file) {
  25.     echo "Error opening data file.\n";
  26.     exit;
  27. }
  28.  
  29. $size = filesize($csvfile);
  30.  
  31. if(!$size) {
  32.     echo "File is empty.\n";
  33.     exit;
  34. }
  35.  
  36. $csvcontent = fread($file,$size);
  37.  
  38. fclose($file);
  39.  
  40. $con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
  41.  mysql_select_db("test") or die ("no database");
  42. $result = mysql_query("SELECT * FROM ex order by count");
  43. $count=0;
  44. while($row = mysql_fetch_array($result))
  45.   {
  46.    $count= $row['count'];
  47.   }
  48. if($count==0)
  49.   $count=0;
  50.   else
  51.   $count=$count;
  52.  
  53.  
  54.  @mysql_close($con);
  55.  $con = mysql_connect("$hostname","$user","$pass") or die("did'nt connect to mysql");
  56.  mysql_select_db("test") or die ("no database"); 
  57.  
  58. $databasetable = "$file3";
  59. $lines = 0;
  60. $queries = "";
  61. $linearray = array();
  62.  
  63. foreach(split($lineseparator,$csvcontent) as $line) {
  64. $count = $count + 1;
  65.  
  66. $lines++;
  67.  
  68. $line = trim($line," \t");
  69.  
  70. $expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
  71.  
  72. $results=preg_split($expr,trim($line));
  73.  
  74. $results1= preg_replace("/^\"(.*)\"$/","$1",$results);
  75.  
  76. $linearray = explode($fieldseparator,$results1);
  77.  
  78. $linemysql = implode("','",$linearray);
  79.  
  80. if($addauto)
  81. {
  82. $query = "insert into $databasetable VALUES('$count','$linemysql');";
  83. $result1 = mysql_query($query) or die("didn't query for insert");
  84. }
  85. else
  86. $query = "insert into $databasetable values('$linemysql');";
  87. $queries .= $query . "\n";
  88. @mysql_query($query);
  89. }
  90. @mysql_close($con);
  91.  
Mar 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.