Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

explode csv file on delimiter (;) and delimiter(,)?

when I explode csv file on delimiter (;) the explode successfully in some excel program and failed in others

also when I explode csv file on delimiter (,) the explode successfully in some excel program and failed in others

How can I do explode in all versions of excel? How can I know the perfect delimiter to explode?

yes there is code..

if (!function_exists('create_csv')) {
    function create_csv($query, &$filename = false, $old_csv = false) {
        if(!$filename) $filename = "data_export_".date("Y-m-d").".csv";
        $ci = &get_instance();
        $ci->load->helper('download');
        $ci->load->dbutil();
        $delimiter = ";";
        $newline = "\r\n";
        $csv = "Data:".date("Y-m-d").$newline;
        if($old_csv)
            $csv .= $old_csv;
        else
            $csv .= $ci->dbutil->csv_from_result($query, $delimiter, $newline);
        $columns = explode($newline, $csv);
        $titles = explode($delimiter, $columns[1]);
        $new_titles = array();
        foreach ($titles as $item) {
            array_push($new_titles, lang(trim($item,'"')));
        }
        $columns[1] = implode($delimiter, $new_titles);
        $csv = implode($newline, $columns);
        return $csv;
    }
}

sometimes I put $delimiter = ";"; and sometims $delimiter = ",";

thanks..

like image 783
Eyes Charming Avatar asked May 27 '15 08:05

Eyes Charming


People also ask

How do I delimiter a CSV file?

Indicate separator directly in CSV file For this, open your file in any text editor, say Notepad, and type the below string before any other data: To separate values with comma: sep=, To separate values with semicolon: sep=; To separate values with a pipe: sep=|

What is meant by delimiter in CSV file?

The delimiter in your CSV is the character (comma or otherwise) that separates the data in your file into distinct fields.

What delimiters can be used for CSV?

A CSV file stores data in rows and the values in each row is separated with a separator, also known as a delimiter. Although the file is defined as Comma Separated Values, the delimiter could be anything. The most common delimiters are: a comma (,), a semicolon (;), a tab (\t), a space ( ) and a pipe (|).


2 Answers

You can use helper function to detect best delimiter like:

public function find_delimiter($csv)
{
    $delimiters = array(',', '.', ';');
    $bestDelimiter = false;
    $count = 0;
    foreach ($delimiters as $delimiter)
        if (substr_count($csv, $delimiter) > $count) {
            $count = substr_count($csv, $delimiter);
            $bestDelimiter = $delimiter;
        }
    return $bestDelimiter;
}
like image 57
MAMProgr Avatar answered Sep 22 '22 00:09

MAMProgr


If you have an idea of the expected data (number of columns) then this might work as a good guess, and could be a good alternative to comparing which occurs the most (depending on what kind of data you're expecting). It would work even better if you have a header record, I'd imagine. (You could put in a check for specific header values)

Sorry for not fitting it into your code, but I am not really sure what those calls you are making do, but you should be able to fit it around.

$expected_num_of_columns = 10;
$delimiter = "";

foreach (array(",", ";") as $test_delimiter) {
   $fid = fopen ($filename, "r");
   $csv_row = fgetcsv($fid, 0, $test_delimiter);
   if (count($csv_row) == $expected_num_of_columns) {
       $delimiter = $test_delimiter;
       break;
   }
   fclose($fid);
}

if (empty($delimiter)) {
   die ("Input file did not contain the correct number of fields (" . $expected_num_of_columns . ")");  
}

Don't use this if, for example, all or most of the fields contain non-integer numbers (e.g. a list of monetary amounts) and has no header record, because files separated by ; are most likely to use , as the decimal point and there could be the same number of commas and semi-colons.

like image 25
colmde Avatar answered Sep 24 '22 00:09

colmde