csv file with delimiter other than comma in php

Question

I insert data into table as bulk upload, $handle = fopen($_FILES['file_clg']['tmp_name'], "r");

        fgetcsv($handle);

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

        $collegename = trim(str_replace(array("’","'"),"&#39;",$data[0]));

        $description = trim(str_replace(array("’","'"),"&#39;",$data[1]));

        $sql1 = $db->selectquery("insert into $tbl(name,details)values('" .$collegename."','" .$description."')");

        }

        fclose($handle);

Only two fields is mentioned here: morethan 25 columns in my bulkupload csv

The problem is that the csv delimiter is the comma (',') but in some cases 'details' field contents include commas, as in this case record not inserted properly..

how to solve this case???

And a problem in insertion section,

College name : Alva’s Institute of Engineering & Technology (AIET)

and its saved in table as below format :

Alva�s Institute of Engineering & Technology (AIET)

I try below code:

          $collegename = htmlentities(iconv("cp1252", "utf-8", trim(str_replace(array("’","'"),"&#39;",$data[0]))), ENT_IGNORE, "UTF-8");

but its not working, how can i solve the issue in single quotes

And i placed : header("Content-Type: text/html; charset=ISO-8859-1");

into the header section..

For the first question, you will have to assign a unique delimiter to use which will preserve your field values that contain commas. If you cannot make this adjustment, you may have to work some regex pattern magic. As for the second question, use prepared statements. Can you provide a couple sample rows so we can see what kind of data is being handled? — mickmackusa, Apr 10 '17 at 10:26
how to use a unique delimiter? how can i save semicolon separated csv files? — Renjitha22, Apr 10 '17 at 10:43
If semicolons don't exist in any of your values, they will be suitable. Alternatively, you can use pipes `|` or another seldom used character. http://stackoverflow.com/questions/32309554/php-fputcsv-use-semicolon-separator-in-csv Can you show a couple lines of data in your question? — mickmackusa, Apr 10 '17 at 11:35

score 0 · Answer 1 · edited Jun 04 '17 at 08:46

I'd need to see some samples to say anything with confidence, but there are specs on quoting values with commas to preserve the value count. https://stackoverflow.com/a/769675/2943403

Alternatively, you could create a new fputcsv() code block that will generate a semi-colon (or other non-conflicting character) delimited csv file. I am not providing the actual snippet for this. There are many available resources on SO, including:

Then your while loop could use ; (or whatever) as a delimiter.

As for safely placing your values (which may have single quotes) into your query, use prepared statements

// I realize your query will be much larger than the sample...
// declare $collegename and $decription values
$stmt=$db->prepare("INSERT INTO `$tbl` (`name`,`details`) VALUES (?,?)");
$stmt->bind_param("ss", $collegename,$description);
if($stmt->execute()){
    echo "success";
}else{
    echo "Error: ",$db->error;
}

csv file with delimiter other than comma in php

1 Answers1