csv py readwriteinvestigate csv files describe a csv dialect this must be subclassed see csv excel valid attributes are delimiter quotechar escapechar doublequote skipinitialspace lineterminator quoting placeholders we do this for compatibility with py2 3 describe the usual properties of excelgenerated csv files delimiter quotechar doublequote true skipinitialspace false lineterminator rn quoting quoteminimal registerdialectexcel excel class exceltabexcel describe the usual properties of unixgenerated csv files delimiter quotechar doublequote true skipinitialspace false lineterminator n quoting quoteall registerdialectunix unixdialect class dictreader def initself f fieldnamesnone restkeynone restvalnone dialectexcel args kwds if fieldnames is not none and iterfieldnames is fieldnames fieldnames listfieldnames self fieldnames fieldnames list of keys for the dict self restkey restkey key to catch long rows self restval restval default value for short rows self reader readerf dialect args kwds self dialect dialect self linenum 0 def iterself return self property def fieldnamesself if self fieldnames is none try self fieldnames nextself reader except stopiteration pass self linenum self reader linenum return self fieldnames fieldnames setter def fieldnamesself value self fieldnames value def nextself if self linenum 0 used only for its side effect self fieldnames row nextself reader self linenum self reader linenum unlike the basic reader we prefer not to return blanks because we will typically wind up with a dict full of none values while row row nextself reader d dictzipself fieldnames row lf lenself fieldnames lr lenrow if lf lr dself restkey rowlf elif lf lr for key in self fieldnameslr dkey self restval return d classgetitem classmethodtypes genericalias class dictwriter def initself f fieldnames restval extrasactionraise dialectexcel args kwds if fieldnames is not none and iterfieldnames is fieldnames fieldnames listfieldnames self fieldnames fieldnames list of keys for the dict self restval restval for writing short dicts extrasaction extrasaction lower if extrasaction not in raise ignore raise valueerrorextrasaction s must be raise or ignore extrasaction self extrasaction extrasaction self writer writerf dialect args kwds def writeheaderself header dictzipself fieldnames self fieldnames return self writerowheader def dicttolistself rowdict if self extrasaction raise wrongfields rowdict keys self fieldnames if wrongfields raise valueerrordict contains fields not in fieldnames joinreprx for x in wrongfields return rowdict getkey self restval for key in self fieldnames def writerowself rowdict return self writer writerowself dicttolistrowdict def writerowsself rowdicts return self writer writerowsmapself dicttolist rowdicts classgetitem classmethodtypes genericalias class sniffer def initself in case there is more than one possible delimiter self preferred t def sniffself sample delimitersnone quotechar doublequote delimiter skipinitialspace self guessquoteanddelimitersample delimiters if not delimiter delimiter skipinitialspace self guessdelimitersample delimiters if not delimiter raise errorcould not determine delimiter class dialectdialect name sniffed lineterminator rn quoting quoteminimal escapechar dialect doublequote doublequote dialect delimiter delimiter csv reader won t accept a quotechar of dialect quotechar quotechar or dialect skipinitialspace skipinitialspace return dialect def guessquoteanddelimiterself data delimiters matches for restr in r pdelimwn pspace pquote pquote pdelim r n pquote pquote pdelimwn pspace r pdelimwn pspace pquote pquote n r n pquote pquote n no delim no space regexp re compilerestr re dotall re multiline matches regexp findalldata if matches break if not matches quotechar doublequote delimiter skipinitialspace return false none 0 quotes delims spaces 0 groupindex regexp groupindex for m in matches n groupindex quote 1 key mn if key quoteskey quotes getkey 0 1 try n groupindex delim 1 key mn except keyerror continue if key and delimiters is none or key in delimiters delimskey delims getkey 0 1 try n groupindex space 1 except keyerror continue if mn spaces 1 quotechar maxquotes keyquotes get if delims delim maxdelims keydelims get skipinitialspace delimsdelim spaces if delim n most likely a file with a single column delim else there is no delimiter it s a single column of quoted data delim skipinitialspace 0 if we see an extra quote between delimiters we ve got a double quoted format dqregexp re compile rdelimswquotesdelimsnquotesdelimsnquoteswdelims delim re escapedelim quote quotechar re multiline if dqregexp searchdata doublequote true else doublequote false return quotechar doublequote delim skipinitialspace def guessdelimiterself data delimiters data listfilternone data split n ascii chrc for c in range127 7bit ascii build frequency tables chunklength min10 lendata iteration 0 charfrequency modes delims start end 0 chunklength while start lendata iteration 1 for line in datastart end for char in ascii metafrequency charfrequency getchar must count even if frequency is 0 freq line countchar value is the mode metafrequencyfreq metafrequency getfreq 0 1 charfrequencychar metafrequency for char in charfrequency keys items listcharfrequencychar items if lenitems 1 and items00 0 continue get the mode of the frequencies if lenitems 1 modeschar maxitems keylambda x x1 adjust the mode subtract the sum of all other frequencies items removemodeschar modeschar modeschar0 modeschar1 sumitem1 for item in items else modeschar items0 build a list of possible delimiters modelist modes items total floatminchunklength iteration lendata rows of consistent data number of rows 100 consistency 1 0 minimum consistency threshold threshold 0 9 while lendelims 0 and consistency threshold for k v in modelist if v0 0 and v1 0 if v1total consistency and delimiters is none or k in delimiters delimsk v consistency 0 01 if lendelims 1 delim listdelims keys0 skipinitialspace data0 countdelim data0 countc delim return delim skipinitialspace analyze another chunklength lines start end end chunklength if not delims return 0 if there s more than one fall back to a preferred list if lendelims 1 for d in self preferred if d in delims keys skipinitialspace data0 countd data0 countc d return d skipinitialspace nothing else indicates a preference pick the character that dominates items v k for k v in delims items items sort delim items11 skipinitialspace data0 countdelim data0 countc delim return delim skipinitialspace def hasheaderself sample creates a dictionary of types of data in each column if any column is of a single type say integers except for the first row then the first row is presumed to be labels if the type can t be determined it is assumed to be a string in which case the length of the string is the determining factor if all of the rows except for the first are the same length it s a header finally a vote is taken at the end for each column adding or subtracting from the likelihood of the first row being a header rdr readerstringiosample self sniffsample header nextrdr assume first row is header columns lenheader columntypes for i in rangecolumns columntypesi none checked 0 for row in rdr arbitrary number of rows to check to keep it sane if checked 20 break checked 1 if lenrow columns continue skip rows that have irregular number of columns for col in listcolumntypes keys thistype complex try thistyperowcol except valueerror overflowerror fallback to length of string thistype lenrowcol if thistype columntypescol if columntypescol is none add new column type columntypescol thistype else type is inconsistent remove column from consideration del columntypescol finally compare results against first row and vote on whether it s a header hasheader 0 for col coltype in columntypes items if isinstancecoltype int it s a length if lenheadercol coltype hasheader 1 else hasheader 1 else attempt typecast try coltypeheadercol except valueerror typeerror hasheader 1 else hasheader 1 return hasheader 0 describe a csv dialect this must be subclassed see csv excel valid attributes are delimiter quotechar escapechar doublequote skipinitialspace lineterminator quoting placeholders we do this for compatibility with py2 3 describe the usual properties of excel generated csv files describe the usual properties of excel generated tab delimited files describe the usual properties of unix generated csv files list of keys for the dict key to catch long rows default value for short rows used only for its side effect unlike the basic reader we prefer not to return blanks because we will typically wind up with a dict full of none values list of keys for the dict for writing short dicts sniffs the format of a csv file i e delimiter quotechar returns a dialect object in case there is more than one possible delimiter returns a dialect or none corresponding to the sample escapechar _csv reader won t accept a quotechar of 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 no delim no space quotechar doublequote delimiter skipinitialspace most likely a file with a single column there is no delimiter it s a single column of quoted data if we see an extra quote between delimiters we ve got a double quoted format the delimiter should occur the same number of times on each row however due to malformed data it may not we don t want an all or nothing approach so we allow for small variations in this number 1 build a table of the frequency of each character on every line 2 build a table of frequencies of this frequency meta frequency e g x occurred 5 times in 10 rows 6 times in 1000 rows 7 times in 2 rows 3 use the mode of the meta frequency to determine the expected frequency for that character 4 find out how often the character actually meets that goal 5 the character that best meets its goal is the delimiter for performance reasons the data is evaluated in chunks so it can try and evaluate the smallest portion of the data possible evaluating additional chunks as necessary 7 bit ascii build frequency tables must count even if frequency is 0 value is the mode get the mode of the frequencies adjust the mode subtract the sum of all other frequencies build a list of possible delimiters rows of consistent data number of rows 100 minimum consistency threshold analyze another chunklength lines if there s more than one fall back to a preferred list nothing else indicates a preference pick the character that dominates creates a dictionary of types of data in each column if any column is of a single type say integers except for the first row then the first row is presumed to be labels if the type can t be determined it is assumed to be a string in which case the length of the string is the determining factor if all of the rows except for the first are the same length it s a header finally a vote is taken at the end for each column adding or subtracting from the likelihood of the first row being a header assume first row is header arbitrary number of rows to check to keep it sane skip rows that have irregular number of columns fallback to length of string add new column type type is inconsistent remove column from consideration finally compare results against first row and vote on whether it s a header it s a length attempt typecast
import re
import types
from _csv import Error, __version__, writer, reader, register_dialect, \
                 unregister_dialect, get_dialect, list_dialects, \
                 field_size_limit, \
                 QUOTE_MINIMAL, QUOTE_ALL, QUOTE_NONNUMERIC, QUOTE_NONE, \
                 QUOTE_STRINGS, QUOTE_NOTNULL, \
                 __doc__
from _csv import Dialect as _Dialect

from io import StringIO

__all__ = ["QUOTE_MINIMAL", "QUOTE_ALL", "QUOTE_NONNUMERIC", "QUOTE_NONE",
           "QUOTE_STRINGS", "QUOTE_NOTNULL",
           "Error", "Dialect", "__doc__", "excel", "excel_tab",
           "field_size_limit", "reader", "writer",
           "register_dialect", "get_dialect", "list_dialects", "Sniffer",
           "unregister_dialect", "__version__", "DictReader", "DictWriter",
           "unix_dialect"]

class Dialect:
    
    _name = ""
    _valid = False
    
    delimiter = None
    quotechar = None
    escapechar = None
    doublequote = None
    skipinitialspace = None
    lineterminator = None
    quoting = None

    def __init__(self):
        if self.__class__ != Dialect:
            self._valid = True
        self._validate()

    def _validate(self):
        try:
            _Dialect(self)
        except TypeError as e:
            
            raise Error(str(e))

class excel(Dialect):
    
    delimiter = ','
    quotechar = '"'
    doublequote = True
    skipinitialspace = False
    lineterminator = '\r\n'
    quoting = QUOTE_MINIMAL
register_dialect("excel", excel)

class excel_tab(excel):
    
    delimiter = '\t'
register_dialect("excel-tab", excel_tab)

class unix_dialect(Dialect):
    
    delimiter = ','
    quotechar = '"'
    doublequote = True
    skipinitialspace = False
    lineterminator = '\n'
    quoting = QUOTE_ALL
register_dialect("unix", unix_dialect)


class DictReader:
    def __init__(self, f, fieldnames=None, restkey=None, restval=None,
                 dialect="excel", *args, **kwds):
        if fieldnames is not None and iter(fieldnames) is fieldnames:
            fieldnames = list(fieldnames)
        self._fieldnames = fieldnames   
        self.restkey = restkey          
        self.restval = restval          
        self.reader = reader(f, dialect, *args, **kwds)
        self.dialect = dialect
        self.line_num = 0

    def __iter__(self):
        return self

    @property
    def fieldnames(self):
        if self._fieldnames is None:
            try:
                self._fieldnames = next(self.reader)
            except StopIteration:
                pass
        self.line_num = self.reader.line_num
        return self._fieldnames

    @fieldnames.setter
    def fieldnames(self, value):
        self._fieldnames = value

    def __next__(self):
        if self.line_num == 0:
            
            self.fieldnames
        row = next(self.reader)
        self.line_num = self.reader.line_num

        
        
        
        while row == []:
            row = next(self.reader)
        d = dict(zip(self.fieldnames, row))
        lf = len(self.fieldnames)
        lr = len(row)
        if lf < lr:
            d[self.restkey] = row[lf:]
        elif lf > lr:
            for key in self.fieldnames[lr:]:
                d[key] = self.restval
        return d

    __class_getitem__ = classmethod(types.GenericAlias)


class DictWriter:
    def __init__(self, f, fieldnames, restval="", extrasaction="raise",
                 dialect="excel", *args, **kwds):
        if fieldnames is not None and iter(fieldnames) is fieldnames:
            fieldnames = list(fieldnames)
        self.fieldnames = fieldnames    
        self.restval = restval          
        extrasaction = extrasaction.lower()
        if extrasaction not in ("raise", "ignore"):
            raise ValueError("extrasaction (%s) must be 'raise' or 'ignore'"
                             % extrasaction)
        self.extrasaction = extrasaction
        self.writer = writer(f, dialect, *args, **kwds)

    def writeheader(self):
        header = dict(zip(self.fieldnames, self.fieldnames))
        return self.writerow(header)

    def _dict_to_list(self, rowdict):
        if self.extrasaction == "raise":
            wrong_fields = rowdict.keys() - self.fieldnames
            if wrong_fields:
                raise ValueError("dict contains fields not in fieldnames: "
                                 + ", ".join([repr(x) for x in wrong_fields]))
        return (rowdict.get(key, self.restval) for key in self.fieldnames)

    def writerow(self, rowdict):
        return self.writer.writerow(self._dict_to_list(rowdict))

    def writerows(self, rowdicts):
        return self.writer.writerows(map(self._dict_to_list, rowdicts))

    __class_getitem__ = classmethod(types.GenericAlias)


class Sniffer:
    
    def __init__(self):
        
        self.preferred = [',', '\t', ';', ' ', ':']


    def sniff(self, sample, delimiters=None):
        

        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")

        class dialect(Dialect):
            _name = "sniffed"
            lineterminator = '\r\n'
            quoting = QUOTE_MINIMAL
            

        dialect.doublequote = doublequote
        dialect.delimiter = delimiter
        
        dialect.quotechar = quotechar or '"'
        dialect.skipinitialspace = skipinitialspace

        return dialect


    def _guess_quote_and_delimiter(self, data, delimiters):
        

        matches = []
        for restr in (r'(?P<delim>[^\w\n"\'])(?P<space> ?)(?P<quote>["\']).*?(?P=quote)(?P=delim)', 
                      r'(?:^|\n)(?P<quote>["\']).*?(?P=quote)(?P<delim>[^\w\n"\'])(?P<space> ?)',   
                      r'(?P<delim>[^\w\n"\'])(?P<space> ?)(?P<quote>["\']).*?(?P=quote)(?:$|\n)',   
                      r'(?:^|\n)(?P<quote>["\']).*?(?P=quote)(?:$|\n)'):                            
            regexp = re.compile(restr, re.DOTALL | re.MULTILINE)
            matches = regexp.findall(data)
            if matches:
                break

        if not matches:
            
            return ('', False, None, 0)
        quotes = {}
        delims = {}
        spaces = 0
        groupindex = regexp.groupindex
        for m in matches:
            n = groupindex['quote'] - 1
            key = m[n]
            if key:
                quotes[key] = quotes.get(key, 0) + 1
            try:
                n = groupindex['delim'] - 1
                key = m[n]
            except KeyError:
                continue
            if key and (delimiters is None or key in delimiters):
                delims[key] = delims.get(key, 0) + 1
            try:
                n = groupindex['space'] - 1
            except KeyError:
                continue
            if m[n]:
                spaces += 1

        quotechar = max(quotes, key=quotes.get)

        if delims:
            delim = max(delims, key=delims.get)
            skipinitialspace = delims[delim] == spaces
            if delim == '\n': 
                delim = ''
        else:
            
            delim = ''
            skipinitialspace = 0

        
        
        dq_regexp = re.compile(
                               r"((%(delim)s)|^)\W*%(quote)s[^%(delim)s\n]*%(quote)s[^%(delim)s\n]*%(quote)s\W*((%(delim)s)|$)" % \
                               {'delim':re.escape(delim), 'quote':quotechar}, re.MULTILINE)



        if dq_regexp.search(data):
            doublequote = True
        else:
            doublequote = False

        return (quotechar, doublequote, delim, skipinitialspace)


    def _guess_delimiter(self, data, delimiters):
        

        data = list(filter(None, data.split('\n')))

        ascii = [chr(c) for c in range(127)] 

        
        chunkLength = min(10, len(data))
        iteration = 0
        charFrequency = {}
        modes = {}
        delims = {}
        start, end = 0, chunkLength
        while start < len(data):
            iteration += 1
            for line in data[start:end]:
                for char in ascii:
                    metaFrequency = charFrequency.get(char, {})
                    
                    freq = line.count(char)
                    
                    metaFrequency[freq] = metaFrequency.get(freq, 0) + 1
                    charFrequency[char] = metaFrequency

            for char in charFrequency.keys():
                items = list(charFrequency[char].items())
                if len(items) == 1 and items[0][0] == 0:
                    continue
                
                if len(items) > 1:
                    modes[char] = max(items, key=lambda x: x[1])
                    
                    
                    items.remove(modes[char])
                    modes[char] = (modes[char][0], modes[char][1]
                                   - sum(item[1] for item in items))
                else:
                    modes[char] = items[0]

            
            modeList = modes.items()
            total = float(min(chunkLength * iteration, len(data)))
            
            consistency = 1.0
            
            threshold = 0.9
            while len(delims) == 0 and consistency >= threshold:
                for k, v in modeList:
                    if v[0] > 0 and v[1] > 0:
                        if ((v[1]/total) >= consistency and
                            (delimiters is None or k in delimiters)):
                            delims[k] = v
                consistency -= 0.01

            if len(delims) == 1:
                delim = list(delims.keys())[0]
                skipinitialspace = (data[0].count(delim) ==
                                    data[0].count("%c " % delim))
                return (delim, skipinitialspace)

            
            start = end
            end += chunkLength

        if not delims:
            return ('', 0)

        
        if len(delims) > 1:
            for d in self.preferred:
                if d in delims.keys():
                    skipinitialspace = (data[0].count(d) ==
                                        data[0].count("%c " % d))
                    return (d, skipinitialspace)

        
        
        items = [(v,k) for (k,v) in delims.items()]
        items.sort()
        delim = items[-1][1]

        skipinitialspace = (data[0].count(delim) ==
                            data[0].count("%c " % delim))
        return (delim, skipinitialspace)


    def has_header(self, sample):
        
        
        
        
        
        
        
        

        rdr = reader(StringIO(sample), self.sniff(sample))

        header = next(rdr) 

        columns = len(header)
        columnTypes = {}
        for i in range(columns): columnTypes[i] = None

        checked = 0
        for row in rdr:
            
            if checked > 20:
                break
            checked += 1

            if len(row) != columns:
                continue 

            for col in list(columnTypes.keys()):
                thisType = complex
                try:
                    thisType(row[col])
                except (ValueError, OverflowError):
                    
                    thisType = len(row[col])

                if thisType != columnTypes[col]:
                    if columnTypes[col] is None: 
                        columnTypes[col] = thisType
                    else:
                        
                        
                        del columnTypes[col]

        
        
        hasHeader = 0
        for col, colType in columnTypes.items():
            if isinstance(colType, int): 
                if len(header[col]) != colType:
                    hasHeader += 1
                else:
                    hasHeader -= 1
            else: 
                try:
                    colType(header[col])
                except (ValueError, TypeError):
                    hasHeader += 1
                else:
                    hasHeader -= 1

        return hasHeader > 0