CODEWITHSUNDEEP
phpcsv
CaribeSoft
CaribeSoft

Reputation: 607

How can I remove extra commas in a CSV file with php

I'm writting a PHP script to import a CSV file to MySQL table.

My problem is that fields are separated by commas, but some fields came within an extra comma, or double quotes (I alread found the solution for double quotes), But not for extra commas. This is an example:

SORT_COLUMN,SORT_ALPHA,GUEST_NAME,PROFILE_ID,FULL_ADDRESS,IS_TOTAL_YN
0,DIAZ REBOLLO ANGELICA,Diaz Rebollo Angelica,824990,"Blvd. Manuel Avila Camacho 36, piso 10   Lomas de Chapul DF 11000"

As you can see in the FULL ADDRESS field:

"Blvd. Manuel Avila Camacho 36, piso 10   Lomas de Chapul DF 11000"

Comes with " and a comma before 36,

This is part of the PHP CODE:

$lineseparator = "\n";
foreach(explode($lineseparator,$csvcontent) as $line) {
$lines++;
$line = trim($line," \t");
$line = str_replace("\r","",$line);

    // Here I remove double quotes in the csv file
    $line =  str_replace(""",'',$line);
    $linearray = explode($fieldseparator,$line);
    $linemysql = implode("','",$linearray);
    $query = "insert into $databasetable values('$linemysql');";
    $queries .= $query . "\n";
    @mysql_query($query);
}

Upvotes: 0

Views: 2299

Answers (2)

Loïc
Loïc

Reputation: 11942

Usually, working with csv, I use ; as a field separator, and \n as a line separator.

Often you'll meet , in fields, that's why ; is better for separating.

EDIT :

As your address field is enclosed by ", you can use something like this :

$line = '0,DIAZ REBOLLO ANGELICA,Diaz Rebollo Angelica,824990,"Blvd. Manuel Avila Camacho 36, piso 10   Lomas de Chapul DF 11000"';

preg_match('/".*"/', $line, $matches);
$address = $matches[0];

$data = explode(',', $line);

$data[4] = $address;

var_dump($data);

Upvotes: 1

Alex
Alex

Reputation: 396

fgetcsv—which is a part of the standard PHP library—is your friend.

Well, most of the time. Note, of course, the $escape parameter, which is weird, useless, and broken. You need to pass 0 (note: the literal integer zero, not any of the other zero-ish things in PHP) to disable it.

Upvotes: 2

Related Questions