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

Question

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

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

How can I explode in all versions of excel? How can I find out the perfect splitter to explode?

yes there is a code ..

if (!function_exists('create_csv')) { function create_csv($query, &$filename = false, $old_csv = false) { if(!$filename) $filename = "data_export_".date("Ymd").".csv"; $ci = &get_instance(); $ci->load->helper('download'); $ci->load->dbutil(); $delimiter = ";"; $newline = "\r\n"; $csv = "Data:".date("Ymd").$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..

Answer 1

You can use a helper function to find the best separator, for example:

 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; } 

Answer 2

If you have an idea of ​​the expected data (number of columns), then this may seem like a good guess and may be a good alternative to the comparison that happens the most (depending on what kind of data you expect), I would think that this would work more better if you have a title entry. (You can check for certain header values)

Sorry for not fitting it into your code, but I'm not quite sure what you are doing, but you should be able to tweak it.

 $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 . ")"); } 

Do not use this if, for example, all or most of the fields contain non-integer numbers (for example, a list of sums of money) and do not have a header entry, since files are separated by a symbol ; most likely will be used as a decimal point and there may be the same number of commas and half-columns.

Answer 3

There is no way to be 100% sure that you are targeting a real delimiter. All you can do is guess.

You should start by looking for the correct separator, and then split the CSV on that separator.

To find the separator, basically you want a function that counts a number and a number ; and returns more.

Sort of:

 $array = explode(find_delimiter($csv), $csv); 

Hope this helps;)

Edit: your find_delimiter function might be something like this:

 function find_delimiter($csv) { $arrDelimiters = array(',', '.', ';'); $arrResults = array(); foreach ($arrDelimiters as $delimiter) { $arrResults[$delimiter] = count(explode($delimiter, $csv)); } $arrResults = rsort($arrResults); return (array_keys($arrResults)[0]); } 

Answer 4

Short answer: you probably won’t be able to if you cannot apply some heuristics to determine the file format. If you do not know and cannot determine the format of the file you are parsing, then parsing will be difficult.

However, once you have defined (or, if required, a specific) format for the delimiter. You will probably find that the php-built-in fgetcsv will be simpler and more accurate than the manual explode strategy.

Answer 5

Well, it looks like you know for sure that your separator will be "," or ";". This is a good place to start. Thus, you can try to replace all commas (,) with semicolons (;), and then explode with only a semicolon. However, in this case, you will definitely have a problem in some cases, because some lines of your CSV files may be like this:

"name, value", another name, another value, last name, last value

Thus, the delimiter of your CSV file will be a comma if there are four columns in your CSV file. However, changing the commas to semicolons will give you five columns that would be incorrect. So changing a separator to another is not a good way.

But still, if your CSV file is formatted correctly, you can find the correct separator in any of the lines. Thus, you can try to create some function like find_delimiter ($ csvLine) proposed by @johnkork, but the problem is that the function itself cannot know which separator to look for. However, you know exactly all the possible separators, so you can try to create another, very similar function, such as delimiter_exists ($ csvLine, $ delimiter), which returns true or false.

But even the delimiter_exists function ($ csvLine, $ delimiter) is not enough. What for? Because for the CSV example above, you get this, both "," and ";" are the delimiters that exist. For a comma, this is a CSV file with four columns, and for a semicolon, two columns.

Thus, there is no universal way that would give you exactly what you want. However, there may be another way to check - the first line of the CSV file, which is the header, if your CSV files have a header. Basically, headers in a CSV file have (optionally) no characters other than alphanumeric column names, which are limited to a specific delimiter. Thus, you can try to create a function such as delimiter_exists ($ csvHeader, $ delimiter), the implementation of which may be like this:

 function delimiter_exists($csvHeader, $delimiter) { return (bool)preg_match("/$delimiter/", $csvHeader); } 

For a specific case, you can use it as follows:

 $csvHeader = "abc;def"; $delimiter = delimiter_exists($csvHeader, ',') ? ',' : ';'; 

Hope this helps!