GeezerVison is showing github.com — The CSV file delimiter found with csv.Sniffer() is interpreted incorrectly · Issue #119123 · python/cpython
The CSV file delimiter found with csv.Sniffer() is interpreted incorrectly #119123DescriptionBug report
Bug description:
Python script:
import csv
with open("file.csv") as f:
dialect = csv.Sniffer().sniff(f.read(), delimiters=",;")
print(dialect.delimiter)file.csv:
id;has_data;has_info;is_sort;a;b;c;d;cost;group;sub_group;merge
1;False;False;False;True;;;;62.25;['345'];['10'];UNKNOWN
2;False;False;False;True;;;;54.00;['235'];['256'];UNKNOWN
3;False;False;False;True;;;;237.00;['567'];['ALL'];UNKNOWN
4;False;False;False;True;;;;46.50;['112', '112'];['765', '766'];UNKNOWN
5;False;False;False;True;;;;237.00;['468'];['ALL'];UNKNOWN
6;False;False;False;True;;;;13.27;['510'];['23'];UNKNOWN
It outputs , instead ;
CPython versions tested on:
3.11
Operating systems tested on:
Windows
Linked PRs
Activityaisk commented on May 18, 2024 At first glance, the line:
4;False;False;False;True;;;;46.50;['112', '112'];['765', '766'];UNKNOWN
can to match the pattern in
|
for restr in (r'(?P<delim>[^\w\n"\'])(?P<space> ?)(?P<quote>["\']).*?(?P=quote)(?P=delim)', # ,".*?", |
, so the Sniffer thinks the delimiter is ,.
I think we can improve the Sniffer, but please note that we can't make it 100% accurate. rhettinger commented on May 21, 2024
I think we can improve the Sniffer, but please note that we can't make it 100% accurate.
Also, it is risky to change the Sniffer because tested and deployed code in the wild may rely on its current best guess. z-dava commented on May 21, 2024 kharvey2 commented on May 21, 2024 Hi! I was able to recreate the issue on Python 3.12.3 on a Mac, and it appears that the problem stems from the commas within the lists under the "group" and "subgroup" columns for the entry with id 4.
We can see why this happens, if we look closer at the Sniffer class,
class Sniffer:
'''
"Sniffs" the format of a CSV file (i.e. delimiter, quotechar)
Returns a Dialect object.
'''
def __init__(self):
# in case there is more than one possible delimiter
self.preferred = [',', '\t', ';', ' ', ':']
def sniff(self, sample, delimiters=None):
"""
Returns a dialect (or None) corresponding to the sample
"""
quotechar, doublequote, delimiter, skipinitialspace = \
self._guess_quote_and_delimiter(sample, delimiters)
if not delimiter:
delimiter, skipinitialspace = self._guess_delimiter(sample,
delimiters)
if not delimiter:
raise Error("Could not determine delimiter")
...When you run the sniff method, it first calls
guess_quote_and_delimiter(...). If this doesn't find a delimiter, it then runs guess_delimiter(). If both attempts fail, it raises the error "Could not determine delimiter."
When we pass in both , and ; into the sniff method and it runs guess_quote_and_delimiter(...), it will return , as the delimiter. This is because guess_quote_and_delimiter(...) is described as follows,
def _guess_quote_and_delimiter(self, data, delimiters):
"""
Looks for text enclosed between two identical quotes
(the probable quotechar) which are preceded and followed
by the same character (the probable delimiter).
For example:
,'some text',
The quote with the most wins, same with the delimiter.
If there is no quotechar the delimiter can't be determined
this way.
"""
...In the provided CSV file, we can see that that the only instances where identical quotes are followed by the same character involve commas. This is evident in the row with id 4, where "group" is ['112', '112'] and "subgroup" is ['765', '766']. Consequently, the sniff method identifies the comma as the delimiter.
If you were to only pass ; as a potential delimiter to the sniff function (csv.Sniffer().sniff(csv_content, delimiters=";")), it would first use guess_quote_and_delimiter(...) to attempt to detect the delimiter. Since there are no instances where their are two identical quotes which are preceded and followed by a ;, it is unable to find a delimiter so it then runs guess_delimiter(). Here, it recognizes ; as the delimiter because it's the character repeated consistently across lines.
You can also see that by removing the quotation marks in the row with id 4 (changing "group" from ['112', '112'] to [112, 112] and "subgroup" from ['765', '766'] to [765, 766]) and keeping the sniff line as dialect = csv.Sniffer().sniff(csv_content, delimiters=",;"), it will detect ; as the delimiter. aterrel commented on May 30, 2024 As @kharvey2 pointed out, the regular expression is responsible for choosing the wrong delimiter here.
The current parser has two methods for guessing what the delimiter. One based off statistics and one off some regular expressions. The fiest regilar expression will match the first delimiter that is next to a quote which breaks support for embedded lists.
A simple fix seems to add support for lists to the regular expressions. I can work on that aterrel commented on Jun 5, 2024 Perhaps a good solution to this problem would be to just remove all the quotes and enclosing square brackets. Then you don't have the problematic regular expression:
def remove_enclosed_parts(s):
# Define a regex pattern to match text within quotes and square brackets
pattern = r'(["\']).*?\1|\[.*?\]'
# Use a loop to repeatedly apply the regex until no more matches are found
while re.search(pattern, s):
s = re.sub(pattern, '', s)
return s
ws-garcia commented on Aug 17, 2024 aterrel commented on May 19, 2025 Seems this can be fixed by just removing lists from the data. Adding:
# Remove lists from the data, see issue 119123
data = re.sub(r'\[.*?\]', '', data)
seems to work. serhiy-storchaka commented on Jul 21, 2026 Fixed in 3.16 by the csv.Sniffer rewrite (gh-83273): the dialect is now guessed by parsing the sample with every plausible combination of parameters and choosing the most consistent one, so a comma inside ['112', '112'] is no longer counted as a delimiter. The sample from this issue is detected as ';' now, and there is a test for it.
Note that a single row containing such a list was enough to spoil the detection: without it the old code detects ';' too. The maintenance branches are still affected, and a targeted fix for them is welcome.
MetadataMetadataStatusShow more project fields
DevelopmentNo branches or pull requestsIssue actions
|