Hi Im trying to make a function that takes in a CSV file then insert it into the file whitch works but not to what i need it to do.
at the moment i am using explode to get the rows from the file..
explode(",", $linearray);
this workes but if there is something like
field1,field2,field3,field4,"some text, some other text",field6
i get this array
array(
[0]=>field1,
[1]=>field2,
[2]=>field3,
[3]=>field4,
[4]=>"some text,
[5]=>some other text",
[6]=>field6
)
which is not the outcome i want. i know that preg_split can do it for me but im not that good at regular expressions. the outcome i want is.
field1,field2,field3,field4,"some text, some other text",field6
array(
[0]=>field1,
[1]=>field2,
[2]=>field3,
[3]=>field4,
[4]=>some text, some other text,
[5]=>field6
)
please help.
functions for CSV file from a PHP CLASS i have written
$lineseparator = "
";
$fieldseparator = "
";
function ReadFile(){
$this->csvcontent = fread($this->_file,$this->size);
fclose($this->_file);
return ($this->csvcontent)? true : false ;
}
function InsertFileToSQL(){
$query = "";
$i_count = 0;
$queries = "";
$linearray = array();
$file_array = explode($this->lineseparator,$this->csvcontent);
$lines = count($file_array);
foreach($file_array as $key => $value) {
$value = trim($value," \t");
$value = str_replace("","",$value);
/***********************************************************************************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************************************************************************************/
$value = str_replace("'","\'",$value);
$value = str_replace("\"","",$value);
/***********************************************************************************************************/
$linearray = explode($this->fieldseparator,$value);
foreach($linearray as $key2 => $value2){
// Format all fields that match a date format the Reformat for SQL.
$date = explode("/", $value2);
if(count( $date ) == 3 ){
$linearray[$key2] = $date[2]."-".$date[1]."-".$date[0];
}
}
$linemysql = implode("','",$linearray);
if($linemysql != "" && $linemysql != NULL){
if($this->csvheader ){
if($key != 0){
if($this->addauto == 1){
$query = "INSERT INTO `$this->db_table` VALUES (NULL,'$linemysql');";
}else{
$query = "INSERT INTO `$this->db_table` VALUES ('$linemysql');";
}
}else{
$lines--;
}
$insert = mysql_query($query) or die(mysql_error());
if($insert){
$queries .= $query . "
";
$i_count++;
}
}else{
if($this->addauto == 1){
$query = "INSERT INTO `$this->db_table` VALUES (NULL,'$linemysql');";
}else{
$query = "INSERT INTO `$this->db_table` VALUES ('$linemysql');";
}
$insert = mysql_query($query) or die((mysql_error()." in QUERY: ".$query));
if($insert){
$queries .= $query . "
";
$i_count++;
}
}
}else{
$this->null_row++;
$lines--;
}
}
if($this->save) {
$f = fopen($this->output_location.$this->outputfile, 'a+');
if ($f) {
@fputs($f, $queries);
@fclose($f);
}else{
echo("Error writing to the output file.", 'error');
}
}
$lines--;//fix array count
$text = "";
if($i_count - $this->null_row != 0){$i_count = $i_count - $this->null_row ;$text .= "<br>$i_count Records were inserted Successfully.";}
echo("Found a Total of $lines Record(s) in this csv file.<br>$this->null_row Record(s) were/are Blank or Null.$text", 'success');
}
I think your answer is in here:
exploding a string using a regular expression
As @Casimir et Hippolyte has said in that page:
You can do the job using preg_match_all
$string="a,b,c,(d,e,f),g,'h, i j.',k";
preg_match_all("~'[^']++'|\([^)]++\)|[^,]++~", $string,$result);
print_r($result[0]);
Explanation:
The trick is to match parenthesis before the ,
~ Pattern delimiter
'
[^'] All charaters but not a single quote
++ one or more time in [possessive][1] mode
'
| or
\([^)]++\) the same with parenthesis
| or
[^,] All characters but not a comma
++
~
if you have more than one delimiter like quotes (that are the same for open and close), you can write your pattern like this, using a capture group:
$string="a,b,c,(d,e,f),g,'h, i j.',k,°l,m°,#o,p#,@q,r@,s";
preg_match_all("~(['#@°]).*?\1|\([^)]++\)|[^,]++~", $string,$result);
print_r($result[0]);
explanation:
(['#@°]) one character in the class is captured in group 1
.*? any character zero or more time in lazy mode
\1 group 1 content
With nested parenthesis:
$string="a,b,(c,(d,(e),f),t),g,'h, i j.',k,°l,m°,#o,p#,@q,r@,s";
preg_match_all("~(['#@°]).*?\1|(\((?>[^()]++|(?-1)?)*\))|[^,]++~", $string,$result);
print_r($result[0]);
You can use preg_split with the PREG_SPLIT_DELIM_CAPTURE option.
$str = field1,field2,field3,field4,"some text, some other text",field6;
then something like this
$match = preg_split("ypir expression", $str, null, PREG_SPLIT_DELIM_CAPTURE);
I'm not going to answer the question that was asked because he's asking for the wrong solution to his problem. However, I hope this solution will be better for him:
Looking at the code in the question, the OP is basically reading a CSV file via PHP and importing it into a mysql database.
MySQL actually offers a method of doing this directly with it's LOAD DATA INFILE syntax, without having to parse the file in PHP at all. It is much quicker than processing it via PHP, and completely avoid the whole problem that the OP is having.
In PHP you simply need to do the following:
$query = <<<eof
LOAD DATA INFILE {$filename} INTO {$table}
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES
field1, field2, field3, field4, field5, field6
eof;
mysqli_query($conn, $query);
You may need to modify that query a bit for some of the more complex stuff in your code (ie converting date formats, etc), but once you've got the hang of the LOAD DATA INFILE syntax, you'll find that's fairly simple to incorporate.
I hope that helps.