CODEWITHSUNDEEP
phpcsv
donk
donk

Reputation: 1570

escape semicolon character when writing to csv file

I want to write something like 

=HYPERLINK("http://example.com"; "Example")

to a comma-separated CSV file, but Excel parses the semicolon and puts "Example") part in another cell. I tried escaping the semicolon with backslash and wrapping everything in double quotes without any luck.

Any help?

Upvotes: 31

Views: 33519

Answers (4)

Hassan Munir
Hassan Munir

Reputation: 199

Yes, agreed with the solution

I applied below and it fixed the issue

Representing= tdOPP[2].text.replace(';', '"";""')

odata_row = [b_yr, b_no, full_nameOPP, f_nameOPP, l_nameOPP, tdOPP[1].text.strip(),Representing, witness_positionOPP]

Upvotes: 0

Ilya Eliseev
Ilya Eliseev

Reputation: 91

I use this function for each CSV value to pass it correctly. It quotes a value only if it contain new line symbols, double quotes or separator. Actually, the only value to escape is double quotes symbol. All other cell content gets into it and displayed correctly in Excel.

Checked with various versions of Excel and ODBC CSV parsers in Cyrillic locale under Windows.

/**
 * This function escapes single CSV value if it contains new line symbols, quotes or separator symbol and encodes it into specified $encoding.
 * 
 * @param string $source - origin string
 * @param string $sep - CSV separator
 * @param string $source_encoding - origin string encoding
 * @param string $encoding - destination encoding
 *
 * @return string - escaped string, ready to be added to CSV
 *
 * @example echo escapeStringCSV("Hello\r\n\"World\"!");
 *  will output
 *      "Hello
 *      ""World""!"
 */
function escapeStringCSV($source, $sep=';', $source_encoding='utf-8', $encoding="windows-1251//TRANSLIT"){

    $str = ($source_encoding!=$encoding ? iconv($source_encoding, $encoding, $source) :  $source);

    if(preg_match('/[\r\n"'.preg_quote($sep, '/').']/', $str)){
        return '"'.str_replace('"', '""', $str).'"';
    } else 
        return $str;
}

So usage can be like this:

 while($row = mysql_fetch_assoc($res)){
    foreach($row as $val){
        $csv .= escapeStringCSV($val).';';
    }
    $csv .= "\r\n";
}

Upvotes: 4

Jack
Jack

Reputation: 302

I also had a very wild time tring to figure the whole picture out, then is how I've got all my csv ready to open into excel in php ( which includes utf8 encoding as well ) :

$sep='";"';//note the separator is double quoted
while($t=mysql_fetch_assoc(mysql_query('select ..')){
  #replaces the caracters who are breaking csv display
  $t=array_map(function($x){return str_replace(array("\n","\r",'"'),array('\\n',"\\r",'""'),$x);},$t);
  $csv.="\n\"".implode($sep,$t)."\"";
}
$charset='utf-8';
header('Content-Type: application/csv;charset='.$charset);
header('Content-Disposition: attachment; filename="filename.csv"');
$bom=chr(239).chr(187).chr(191);#this tells excel document is utf8 encoded
die($bom.$csv);

Upvotes: 3

poke
poke

Reputation: 388313

The wrapping with double quotes was already the correct idea, but you have to make sure you do it correctly. You can put a column within double quotes, then everything inside is considered as a single value. Quotes itself have to be escaped by writing two of them ("").

See for example this:

Column A;Column B;Column C
Column A;"Column B; with semicolon";Column C
Column A;"Column B"";"" with semicolon and quotes";Column C
Column A;"=HYPERLINK(""http://example.com""; ""Example"")";Column C

Upvotes: 42

Related Questions