PHP - Best approach to detect CSV delimiter

Question

I have seen multiple threads about what the best solution to auto detect the delimiter for an incoming CSV. Most of them are functions of length between 20 - 30 lines, multiple loops pre-determined list of delimiters, reading the first 5 lines and matching counts e.t.c e.t.c

Here is 1 example

I have just implemented this procedure, with a few modifications. Works brilliantly.

THEN I found the following code:

private function DetectDelimiter($fh)
{
    $data_1 = null;
    $data_2 = null;
    $delimiter = self::$delim_list['comma'];
    foreach(self::$delim_list as $key=>$value)
    {
        $data_1 = fgetcsv($fh, 4096, $value);
        $delimiter = sizeof($data_1) > sizeof($data_2) ? $key : $delimiter;
        $data_2 = $data_1;
    }

    $this->SetDelimiter($delimiter);
    return $delimiter;
}

This to me looks like it's achieving the SAME results, where $delim_list is an array of delimiters as follows:

static protected $delim_list = array('tab'=>"\t", 
                                     'semicolon'=>";", 
                                     'pipe'=>"|", 
                                     'comma'=>",");

Can anyone shed any light as to why I shouldn't do it this simpler way, and why everywhere I look the more convoluted solution seems to be the accepted answer?

Thanks!

Answer 1

This function is elegant :)

/**
* @param string $csvFile Path to the CSV file
* @return string Delimiter
*/
public function detectDelimiter($csvFile)
{
    $delimiters = [";" => 0, "," => 0, "\t" => 0, "|" => 0];

    $handle = fopen($csvFile, "r");
    $firstLine = fgets($handle);
    fclose($handle); 
    foreach ($delimiters as $delimiter => &$count) {
        $count = count(str_getcsv($firstLine, $delimiter));
    }

    return array_search(max($delimiters), $delimiters);
}

Answer 2

None of these worked for my use case. So I made some slight modifications.

   /**
    * @param string $filePath
    * @param int $checkLines
    * @return string
    */
   public function getCsvDelimiter(string $filePath, int $checkLines = 3): string
   {
      $delimiters =[",", ";", "\t"];

      $default =",";

       $fileObject = new \SplFileObject($filePath);
       $results = [];
       $counter = 0;
       while ($fileObject->valid() && $counter <= $checkLines) {
           $line = $fileObject->fgets();
           foreach ($delimiters as $delimiter) {
               $fields = explode($delimiter, $line);
               $totalFields = count($fields);
               if ($totalFields > 1) {
                   if (!empty($results[$delimiter])) {
                       $results[$delimiter] += $totalFields;
                   } else {
                       $results[$delimiter] = $totalFields;
                   }
               }
           }
           $counter++;
       }
       if (!empty($results)) {
           $results = array_keys($results, max($results));

           return $results[0];
       }
return $default;
}

Answer 3

Fixed version.

In your code, if a string has more than 1 delimiter you'll get a wrong result (example: val; string, with comma;val2;val3). Also if a file has 1 row (count of rows < count of delimiters).

Here is a fixed variant:

private function detectDelimiter($fh)
{
    $delimiters = ["\t", ";", "|", ","];
    $data_1 = null; $data_2 = null;
    $delimiter = $delimiters[0];
    foreach($delimiters as $d) {
        $data_1 = fgetcsv($fh, 4096, $d);
        if(sizeof($data_1) > sizeof($data_2)) {
            $delimiter = $d;
            $data_2 = $data_1;
        }
        rewind($fh);
    }

    return $delimiter;
}

Answer 4

In general, you cannot detect the delimiter for a text file. If there are additional hints, you need to implement them in your detection to be sure.

One particular problem with the suggested approach is that it will count the number of elements in different lines of the file. Suppose you had a file like this:

a;b;c;d
a   b;  c   d
this|that;here|there
It's not ready, yet.; We have to wait for peter, paul, and mary.; They will know what to do

Although this seems to be separated by a semicolon, your approach will return comma.

Answer 5

Another one (built by combining lots of answers I found on the internet:

/**
 * Detects the delimiter of a CSV file (can be semicolon, comma or pipe) by trying every delimiter, then
 * counting how many potential columns could be found with this delimiter and removing the delimiter from array of
 * only one columns could be created (without a working limiter you'll always have "one" column: the entire row).
 * The delimiter that created the most columns is returned.
 *
 * @param string $pathToCSVFile path to the CSV file
 * @return string|null nullable delimiter
 * @throws \Exception
 */
public static function detectDelimiter(string $pathToCSVFile): ?string
{
    $delimiters = [
        ';' => 0,
        ',' => 0,
        "|" => 0,
    ];

    $handle = fopen($pathToCSVFile, 'r');
    $firstLine = fgets($handle);
    fclose($handle);

    foreach ($delimiters as $delimiterCharacter => $delimiterCount) {
        $foundColumnsWithThisDelimiter = count(str_getcsv($firstLine, $delimiterCharacter));
        if ($foundColumnsWithThisDelimiter > 1) {
            $delimiters[$delimiterCharacter] = $foundColumnsWithThisDelimiter;
        }else {
            unset($delimiters[$delimiterCharacter]);
        }
    }

    if (!empty($delimiters)) {
        return array_search(max($delimiters), $delimiters);
    } else {
        throw new \Exception('The CSV delimiter could not been found. Should be semicolon, comma or pipe!');
    }
}

And the according Unit Tests (you'll have to add custom test.csv files):

/**
 * Test the delimiter detector
 *
 * @test
 */
public function testDetectDelimiter()
{
    $this->assertEquals(',', Helper::detectDelimiter('test1.csv'));
    $this->assertEquals(';', Helper::detectDelimiter('test-csv-with-semicolon-delimiter.csv'));
    $this->assertEquals('|', Helper::detectDelimiter('test-csv-with-pipe-delimiter.csv'));

    $this->expectExceptionMessage('The CSV delimiter could not been found. Should be semicolon, comma or pipe!');
    Helper::detectDelimiter('test-csv-with-failing-delimiter.csv');
}

Answer 6

Ok, this one parses a single line of your CSV (usually you take the first) and throws Exceptions if multiple delimeters are possible or no one matches. According to that, it also looks that the delimeter you want to test against is not in a quoted string or escaped.

    public function getDelimiter(string $content, $throwExceptionOnNonUnique = true, $expectSingleColumn = false): string
    {
        // Would be cleaner if you pass the delimiters from outside
        // as also the order matters in the special case you've got something like "a,b;c"
        // and you don't throw the exception - then the first match is preferred
        // But for StackOverflow I put them inside
        $delimiters = ["\t", ";", "|", ","];
        $result = ',';
        $maxCount = 0;

        foreach ($delimiters as $delimiter) {
            // Impress your code reviewer by some badass regex ;)
            $pattern = "/(?<!\\\)(?:\\\\\\\)*(?!\B\"[^\\\"]*)\\" . $delimiter . "(?![^\"]*\\\"\B)/";
            $amount = preg_match_all($pattern, $content);

            if ($maxCount > 0 && $amount > 0 && $throwExceptionOnNonUnique) {
                $msg = 'Identifier is not clear: "' . $result . '" and "' . $delimiter . '" are possible';
                throw new \Exception($msg);
            }

            if ($amount > $maxCount) {
                $maxCount = $amount;
                $result = $delimiter;
            }
        }

        // If nothing matches and you don't expect that just the CSV just
        // consists of one single column without a delimeter at the end
        if ($maxCount === 0 && !$expectSingleColumn) {
            throw new \Exception('Unknown delimiter');
        }

        return $result;
    }

P.S: Also unit tested - but I don't want to paste 100+ lines of the tests here ;)

Answer 7

Here's the shortest version to detect the CSV delimiter by using the SplFileObject class with the method getCsvControl and making use of the array dereference.

There's a catch though, the below function it will work only if the delimiter was set manually using the setCsvControl() function, otherwise, use one of the top rated answers.

// SplFileObject::getCsvControl — Get the delimiter, enclosure and escape character for CSV
function detectDelimiter($csvFile){
    if(!file_exists($csvFile) || !is_readable($csvFile)){
        return false;
    }   
    $file = new SplFileObject($csvFile);
    return $file->getCsvControl()[0]; 
}