csv - Read File headers and delimiters using Python

Question

I am reading all the files from a given folder (contains Dir, Sub dir and files of type .csv, .txt ..)

I need to get the following information into an output file in the following format:

FileLocation, FileName, Delimiter, Columns 

(All columns needed in a cell separated by delimiter)

I am using the following script which works fine except delimiter. I have tried using csv.sniffer but it does not work.

import sys,os,csv

ofilew = open('D:OutputFile/Columns_Info.csv', 'w')
ofile = open('D:OutputFile/Columns_Info.csv', 'a')

root = 'D:UnZipFiles'
path = os.path.join(root)

columninfo = 'FolderLocation, FileName, Delimiter, Columns' + '
'
ofilew.write(columninfo)

for r,d,f in os.walk(path):
    for file in f:
        fullfilepath = os.path.join(r,file)
        with open(fullfilepath,'r') as f:
            columninfo = f.readline()
            columninfo = columninfo.replace(",", ";")

            output = file +','+ columninfo
            outputfinal = r + ',' + output

            ofile.write(outputfinal)

Answer 1

The following approach should work for you, it uses Python's csv.sniffer feature to attempt to determine the correct dialect to use for reading the file. This also contains the delimiter that is used.

import os, csv

header_output = ['FolderLocation', 'FileName', 'Delimiter', 'Columns']
path = r'D:UnZipFiles'

with open(r'D:OutputFileColumns_Info.csv', 'wb') as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(header_output)

    for root, folders, files in os.walk(path):
        for file in files:
            full_file_path = os.path.join(root, file)

            with open(full_file_path, 'rb') as f_input:
                try:
                    dialect = csv.Sniffer().sniff(f_input.read(1024))
                    f_input.seek(0)
                    csv_input = csv.reader(f_input, dialect)
                    header_input = next(csv_input)
                    csv_output.writerow([root, file, dialect.delimiter] + header_input)
                except csv.Error as e:
                    print "{} - could not determine the delimiter".format(file)

As an alternative to csv.sniffer, you could devise your own, but the Python one is much more powerful than this:

def get_delimiter(file_name):            
    cols_found = []

    for delim in [',', ';', '|', '	']:
        with open(file_name, 'rb') as f_in:
            cols_found.append([len(next(csv.reader(f_in, delimiter=delim))), delim])

    if cols_found[-1][0] > 1:
        return sorted(cols_found)[-1][1]
    else:
        return None


print get_delimiter('my.csv')

This returns a possible delimiter by counting which delimiter results in the most columns in the first row. If only one column is found, it returns None to indicate no matching delimiter was found. It could instead raise an exception.