excel_to_csv

#!/usr/bin/python

import os, csv, sys, Tkinter, tkFileDialog as fd, xlrd

# stop tinker shell from opening as only needed for file dialog
root = Tkinter.Tk()
root.withdraw()

def format_date(dt):
    yyyy, mm, dd = str(dt[0]), str(dt[1]), str(dt[2])
    hh, mi, ss = str(dt[3]), str(dt[4]), str(dt[5])

    if len(mm) == 1:
        mm = '0'+mm
    if len(dd) == 1:
        dd = '0'+dd

    if hh == '0' and mi == '0' and ss == '0':
        datetime_str = dd+'/'+mm+'/'+yyyy
    else:
        if len(hh) == 1:
            hh = '0'+hh
        if len(mi) == 1:
            mi = '0'+mi
        if len(ss) == 1:
            ss = '0'+ss
        datetime_str = dd+'/'+mm+'/'+yyyy+' '+hh+':'+mi+':'+ss

    return datetime_str

def xl_to_csv(in_path, out_files):
    # set up vars to read file
    wb = xlrd.open_workbook(in_path)
    base_name = out_files[0]
    sheet_no = 0

    for sheet in wb.sheets():

        print '\nGetting data from sheet:', sheet.name, '...'

        # set up vars to write file
        out_path = base_name + '_' + sheet.name + '.csv'
        if len(out_files) <= sheet_no:
            out_files.append(out_path)
        else:
            out_files[sheet_no] = out_path

        fileout = open(out_path, 'wb')
        writer = csv.writer(fileout, dialect='excel')

        # iterate through rows and cols
        row_cnt, col_cnt = sheet.nrows, sheet.ncols
        for r in range(row_cnt):

            # make list from row data
            row = []
            for c in range(col_cnt):

                # check data type and make conversions
                val = sheet.cell(r,c).value
                if sheet.cell(r,c).ctype == 2: # number data type
                    if val == int(val):
                        val = int(val) # convert to int if no decimal remainder
                elif sheet.cell(r,c).ctype == 3: # date fields
                    dt = xlrd.xldate_as_tuple(val, 0) # convert excel date to obj
                    val = format_date(dt)
                elif sheet.cell(r,c).ctype == 4: # boolean data types
                    val = str(bool(val)) # convert 1 or 0 to bool then string
                else:
                    try:
                        val = str(val)
                    except UnicodeEncodeError:
                        print '....encounted UnicodeEncodeError on row:', \
                              r+1, 'column:', c+1
                        print '....replacing unknown charachter with ?'
                        val = str(val.encode('ascii', 'replace'))

                row.append(val)

            # write row to csv file
            try:
                writer.writerow(row)
            except:
                print '...row failed in write to file:', row
                exc_type, exc_value, exc_traceback = sys.exc_info()
                lines = traceback.format_exception(exc_type, exc_value, exc_traceback)
                for line in lines:
                    print '!!', line

        print '...data written to:', out_path
        sheet_no += 1

    return out_files

def main():
    in_path, out_path = None, None
    out_files = []
    prog_name = sys.argv[0]

    # check if in_path and out_path are inlcuded as cmd line args...
    if len(sys.argv) > 1:
        in_path = sys.argv[1]
        if not os.path.exists(in_path):
            print 'Usage:', prog_name, '[<excel_file_path>] [<output_file_path>]'
            print 'cannot find the file provided for excel_file_path:\n', in_path
            sys.exit("Error - invalid excel_file_path arg")
        if len(sys.argv) > 2:
            out_path = sys.argv[2]
            if not os.path.exists(os.path.dirname(out_path)):
                print 'Usage:', prog_name, '[<excel_file_path>] [<output_file_path>]'
                print 'cannot find the directory provided in output_file_path:\n', out_path
                sys.exit("Error - invalid output_file_path arg")
    else:
        # set current working directory to user's my documents folder
        try:
            os.chdir(os.path.join(os.getenv('userprofile'),'documents'))
        except:
            pass

    # ask user for path to Excel file...
    while not in_path:
        print "Please select the excel file to read data from ..."
        try:
            in_path = fd.askopenfilename()
        except:
            print 'Error selecting file.'
        if not in_path:
            cont = raw_input('Do you want to continue? (Y|N): ').upper()[0]
            if cont == 'N':
                sys.exit("Error - unable to select input file")

    # get dir for output...
    if not out_path:
        out_path = os.path.dirname(in_path)

    # combine outpath directory with in_path filenmame (excl ext)...
    f_name = os.path.splitext(os.path.basename(in_path))[0]
    out_files.append(os.path.join(out_path,f_name))

    # call function to convert sheets to csv...
    out_files = xl_to_csv(in_path, out_files)

    # open new files...
    v_open = raw_input("\nOpen file(s) (Y/N): ").upper()
    if v_open == 'Y':
        for f in out_files:
            os.startfile(f)
    sys.exit()

if __name__ == '__main__':
    main()