csv.Sniffer().sniff() guesses "M" for the delimiter of the first dataset below. The same error occurs when the "," is replaced by "\t". However, it correctly guesses "," for the second dataset.

---Dataset 1----
Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
Sscanner ac15072911220.pdf,CM_15203,28714.32,MX Jan Feb,948198,,
Sscanner ac15072911221.pdf,CM 16148,15600,MX Unkwon,948199,,
Sscanner ac15072911230.pdf,CM 16148,33488,MX Cavalier,948200,Photos don't match the invoice
Sscanner ac15072911261.pdf,CM_14464,1713.6,MX Dutiful,948203,,
Sscanner ac15072911262.pdf,CM 16148,3114,MX Apr,948202,,
Sscanner ac15072911250.pdf,CM_14464,1232.28,MX Jan Feb,948208,,
Sscanner ac15072911251.pdf,CM_17491,15232,MX Unkwon,948207,,
Sscanner ac15072911253.pdf,CM_14464,11250,MX Cavalier,,,
Sscanner ac15072911253.pdf,CM_14464,11250,MX Dutiful,,,
Sscanner ac15072911253.pdf,CM_14464,11250,MX Apr,,,

--- Dataset 2---
Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
Sscanner ac15072911220.pdf,CM_15203,82.07,MX Jan Feb,948198,,
Sscanner ac15072911221.pdf,CM 16148,23.29,MX Unkwon,948199,,
Sscanner ac15072911230.pdf,CM 16148,88.55,MX Cavalier,948200,Photos don't match the invoice,
Sscanner ac15072911261.pdf,CM_14464,58.78,MX Dutiful,948203,,
Sscanner ac15072911262.pdf,CM 16148,52,MX Apr,948202,,
Sscanner ac15072911250.pdf,CM_14464,40.40,MX Jan Feb,948208,,
Sscanner ac15072911251.pdf,CM_17491,54.97,MX Unkwon,948207,,
Sscanner ac15072911253.pdf,CM_14464,4.08,MX Cavalier,,,
Sscanner ac15072911253.pdf,CM_14464,49.11,MX Dutiful,,,
Sscanner ac15072911253.pdf,CM_14464,18.28,MX Apr,,,

How are you calling the sniff() method? Note that it takes a sample of the CSV file. For example, this works for me:

>>> f = open("sniff1.csv")
>>> dialect = csv.Sniffer().sniff(next(open("sniff1.csv")))
>>> dialect.delimiter 
','
>>> dialect.lineterminator
'\r\n'

where sniff1.csv is your Dataset 1. (I think for reliable operation you really want your sample to be a multiple of whole lines.)

The sniffer actually changes its "mind" in the fourth line:

Python 3.4.0 (default, Jun 19 2015, 14:20:21) 
[GCC 4.8.2] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import csv
>>> csv.Sniffer().sniff("""\
... Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
... Sscanner ac15072911220.pdf,CM_15203,28714.32,MX Jan Feb,948198,,
... Sscanner ac15072911221.pdf,CM 16148,15600,MX Unkwon,948199,,
... """).delimiter
','
>>> csv.Sniffer().sniff("""\
... Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
... Sscanner ac15072911220.pdf,CM_15203,28714.32,MX Jan Feb,948198,,
... Sscanner ac15072911221.pdf,CM 16148,15600,MX Unkwon,948199,,
... Sscanner ac15072911230.pdf,CM 16148,33488,MX Cavalier,948200,Photos don't match the invoice
... """).delimiter
'M'

That line has only 5 commas while all others have 6. Unfortunately all lines contain exactly two "M"...

I should have probably pointed out that the Sniffer class is the unloved stepchild of the csv module. In my experience it is rarely necessary. You either:

* Are reading CSV files which are about what Excel would produce with its default settings

or

* Know just what your format is, and can define the various parameters easily

It's pretty rare, I think, to get a delimited file in some format which is completely unknown and which thus has to be deduced.

As Peter showed, the Sniffer class is also kind of unreliable. I didn't write it, and there are precious few test cases for it. One of your datasets should probably be added to the mix and bugs fixed.

I agree that the parameters are easily deduced for any one csv file after a
quick inspection. The reason I went searching for a good sniffer was that I
have ~2100 csv files of slightly different formats coming from different
sources. In some cases, a csv file is sent directly to me, other times it
is first opened in excel and saved, and other times it is copy-pasted from
excel into another location, yielding 3 variations on the formatting from a
single source. Multiply that by 8 different sources of data.

For hacking disparate data sources together, it is more interesting to have
a sniffer that works really well to distinguish among the most common
dialects of csv, than one that tries to deduce the parameters of a rare or
unknown format. I agree with you that it would be a rare case that the
format is completely unknown -- more likely, you know it is one of two or
three possible options and don't want to have to inspect each file to find
out which.

Unfortunately, trying to limit delimiters to some of the most common ones
using the delimiters parameter just raises an error:

Python 3.4.3 (v3.4.3:9b73f1c3e601, Feb 23 2015, 02:52:03)
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import csv
>>> csv.Sniffer().sniff("""\
... Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
... Sscanner ac15072911220.pdf,CM_15203,41.56,MX Jan Feb,948198,,
... Sscanner ac15072911221.pdf,CM 16148,41.50,MX Unkwon,948199,,
... Sscanner ac15072911230.pdf,CM 16148,6.42,MX Cavalier,948200,Photos
don't match the invoice
... Sscanner ac15072911261.pdf,CM_14464,0.06,MX Dutiful,948203,,
... Sscanner ac15072911262.pdf,CM 16148,88,MX Apr,948202,,
... Sscanner ac15072911250.pdf,CM_14464,94.08,MX Jan Feb,948208,,
... Sscanner ac15072911251.pdf,CM_17491,39.84,MX Unkwon,948207,,
... Sscanner ac15072911253.pdf,CM_14464,42.07,MX Cavalier,,,
... Sscanner ac15072911253.pdf,CM_14464,2.23,MX Dutiful,,,
... Sscanner ac15072911253.pdf,CM_14464,12.84,MX Apr,,,
... """).delimiter
'M'
>>> csv.Sniffer().sniff("""\
... Invoice File,Credit Memo,Amount Claimed,Description,Invoice,Message,
... Sscanner ac15072911220.pdf,CM_15203,41.56,MX Jan Feb,948198,,
... Sscanner ac15072911221.pdf,CM 16148,41.50,MX Unkwon,948199,,
... Sscanner ac15072911230.pdf,CM 16148,6.42,MX Cavalier,948200,Photos
don't match the invoice
... Sscanner ac15072911261.pdf,CM_14464,0.06,MX Dutiful,948203,,
... Sscanner ac15072911262.pdf,CM 16148,88,MX Apr,948202,,
... Sscanner ac15072911250.pdf,CM_14464,94.08,MX Jan Feb,948208,,
... Sscanner ac15072911251.pdf,CM_17491,39.84,MX Unkwon,948207,,
... Sscanner ac15072911253.pdf,CM_14464,42.07,MX Cavalier,,,
... Sscanner ac15072911253.pdf,CM_14464,2.23,MX Dutiful,,,
... Sscanner ac15072911253.pdf,CM_14464,12.84,MX Apr,,,
... """, delimiters=",\t|^").delimiter
Traceback (most recent call last):
  File "<stdin>", line 13, in <module>
  File
"/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/csv.py",
line 189, in sniff
    raise Error("Could not determine delimiter")
_csv.Error: Could not determine delimiter

On Tue, Aug 4, 2015 at 8:29 AM Skip Montanaro <report@bugs.python.org>
wrote:

>
> Skip Montanaro added the comment:
>
> I should have probably pointed out that the Sniffer class is the unloved
> stepchild of the csv module. In my experience it is rarely necessary. You
> either:
>
> * Are reading CSV files which are about what Excel would produce with its
> default settings
>
> or
>
> * Know just what your format is, and can define the various parameters
> easily
>
> It's pretty rare, I think, to get a delimited file in some format which is
> completely unknown and which thus has to be deduced.
>
> As Peter showed, the Sniffer class is also kind of unreliable. I didn't
> write it, and there are precious few test cases for it. One of your
> datasets should probably be added to the mix and bugs fixed.
>
> ----------
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue24787>
> _______________________________________
>

If you look at the algorithm it is doing some fancy things with metrics, but does have a 'preferred delimiters' list that it checks.  It is possible things could be improved either by tweaking the threshold or by somehow giving added weight to the metrics when the candidate character is in the preferred delimiter list.

We might have to do this with a feature flag to turn it on, though, since it could change the results for programs that happen to work with the current algorithm.

I've run the Sniffer against 1614 csv files on my computer and compared the
delimiter it detects to what I have set manually. Here are the results:

 Sniffer            Human,;\t\(blank)Error:)ceMpGrand TotalError rate,498  2
110  1   5122.7%; 1          10.0%\t3 922 69121  227105412.5%|   33
330.0%space    91   4  1435.7%Grand Total5011922351610221142271614
-Tiago

On Tue, Aug 4, 2015 at 3:51 PM R. David Murray <report@bugs.python.org>
wrote:

>
> R. David Murray added the comment:
>
> If you look at the algorithm it is doing some fancy things with metrics,
> but does have a 'preferred delimiters' list that it checks.  It is possible
> things could be improved either by tweaking the threshold or by somehow
> giving added weight to the metrics when the candidate character is in the
> preferred delimiter list.
>
> We might have to do this with a feature flag to turn it on, though, since
> it could change the results for programs that happen to work with the
> current algorithm.
>
> ----------
> nosy: +r.david.murray
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue24787>
> _______________________________________
>

Tiago, sorry, but your last post with results is completely unintelligible. Can you toss the table in a file and attach it instead?

Table attached.

-Tiago

On Wed, Aug 5, 2015 at 8:14 PM Skip Montanaro <report@bugs.python.org>
wrote:

>
> Skip Montanaro added the comment:
>
> Tiago, sorry, but your last post with results is completely
> unintelligible. Can you toss the table in a file and attach it instead?
>
> ----------
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue24787>
> _______________________________________
>

It seems the HTML file did not come through correctly. Trying a text
version, please view this in a monospace font:

        |   Sniffer
                                    |
Human   |   ,   |   ;   |   \t  |   \   |  space|Except |   :   |   )   |
c   |   e   |   M   |   p   |Total  |   %Error
---------------------------------------------------------------------------------------------------------------------------
    ,   |   498 |       |       |   2   |   1   |   10  |       |       |
1   |       |       |       |   512 |   2.7%
    ;   |       |   1   |       |       |       |       |       |       |
    |       |       |       |   1   |   0.0%
    \t  |   3   |       |   922 |       |   6   |   91  |   2   |   1   |
    |       |   2   |   27  |   1054|   12.5%
    |   |       |       |       |   33  |       |       |       |       |
    |       |       |       |   33  |   0.0%
space   |       |       |       |       |   9   |   1   |       |       |
    |   4   |       |       |   14  |   35.7%
---------------------------------------------------------------------------------------------------------------------------
Total   |   501 |   1   |   922 |   35  |   16  |   102 |   2   |   1   |
1   |   4   |   2   |   27  |   1614

On Thu, Aug 6, 2015 at 8:54 AM Tiago Wright <report@bugs.python.org> wrote:

>
> Tiago Wright added the comment:
>
> Table attached.
>
> -Tiago
>
> On Wed, Aug 5, 2015 at 8:14 PM Skip Montanaro <report@bugs.python.org>
> wrote:
>
> >
> > Skip Montanaro added the comment:
> >
> > Tiago, sorry, but your last post with results is completely
> > unintelligible. Can you toss the table in a file and attach it instead?
> >
> > ----------
> >
> > _______________________________________
> > Python tracker <report@bugs.python.org>
> > <http://bugs.python.org/issue24787>
> > _______________________________________
> >
>
> ----------
> Added file: http://bugs.python.org/file40138/csvsniffertest3.htm
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue24787>
> _______________________________________

I apologize, it seems the text table got line wrapped. This time as a TXT
attachment.

-Tiago

On Thu, Aug 6, 2015 at 12:22 PM Tiago Wright <report@bugs.python.org> wrote:

>
> Tiago Wright added the comment:
>
>
>

Your best bet is to attach an ascii text file as an uploaded file.

Yes, much better :)

I've run the Sniffer against the same data set, but varied the size of the
sample given to the code. It seems that feeding it more data actually seems
to make the results less accurate. Table attached.
On Thu, Aug 6, 2015 at 12:29 PM R. David Murray <report@bugs.python.org>
wrote:

>
> R. David Murray added the comment:
>
> Yes, much better :)
>
> ----------
>
> _______________________________________
> Python tracker <report@bugs.python.org>
> <http://bugs.python.org/issue24787>
> _______________________________________
>

Attached is a .py file with 32 test cases for the Sniff class, 18 that
fail, 14 that pass.

My hope is that these samples can be used to improve the delimiter
detection code.

-Tiago

Have you considered writing your own little sniffer? Getting it right for your actual data is usually easier to achieve than a general solution.

The following simplistic sniffer should work with your samples:

def make_dialect(delimiter):
    class Dialect(csv.excel):
        pass
    Dialect.delimiter = delimiter
    return Dialect

def sniff(sample):
    count, delimiter = max(
        ((sample.count(delim), delim) for delim in ",\t|;"),
        key=operator.itemgetter(0))
    if count == 0:
        if " " in sample:
            delimiter = " "
        else:
            raise csv.Error("Could not determine delimiter")
    return make_dialect(delimiter)

Tiago, If you want to follow that path we should take the discussion to the general python mailing list.