PHP - Best approach to detect CSV delimiter

问题:

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!

回答1:

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

回答2:

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

回答3:

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.

回答4:

None of this answers my use case. So I made a slight modification.

/**
    * @param string $filePath
    * @param int $checkLines
    * @return string
    */
   public function getCsvDelimiter(string $filePath, int $checkLines = 3): string
   {
      $delimeters =[',', ';', '\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;
}

回答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');
}

回答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 ;)