Python script to convert CSV files to Excel

I spent much of my last weekend generating large flat files of denormalized data from various data sources, and then converting it to Excel spreadsheets for human consumption. Although the process is quite simple, thanks to OpenOffice (I am a Linux user, in case you haven't guessed already), its a pain to have to navigate the GUI repeatedly to do this.

Looking around the web, I did not find a ready made script that would do what I wanted, but it seemed fairly simple to do, so I decided to do this rather than suffer through yet another GUI invocation just to convert my CSV file to Excel. To read and parse the file, I used Python's built in CSV module, and to write out the Excel spreadsheets, I downloaded and installed the pyExcelerator module. In addition, since my data files were rather large, I put in a splitting mechanism that would allow me to split my output into multiple Excel files of a specified file size. Here is the script.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131

#!/usr/local/bin/python # Tool to convert CSV files (with configurable delimiter and text wrap # character) to Excel spreadsheets. import string import sys import getopt import re import os import os.path import csv from pyExcelerator import * def usage(): """ Display the usage """ print "Usage:" + sys.argv[0] + " [OPTIONS] csvfile" print "OPTIONS:" print "--title|-t: If set, the first line is the title line" print "--lines|-l n: Split output into files of n lines or less each" print "--sep|-s c [def:,] : The character to use for field delimiter" print "--output|o : output file name/pattern" print "--help|h : print this information" sys.exit(2) def openExcelSheet(outputFileName): """ Opens a reference to an Excel WorkBook and Worksheet objects """ workbook = Workbook() worksheet = workbook.add_sheet("Sheet 1") return workbook, worksheet def writeExcelHeader(worksheet, titleCols): """ Write the header line into the worksheet """ cno = 0 for titleCol in titleCols: worksheet.write(0, cno, titleCol) cno = cno + 1 def writeExcelRow(worksheet, lno, columns): """ Write a non-header row into the worksheet """ cno = 0 for column in columns: worksheet.write(lno, cno, column) cno = cno + 1 def closeExcelSheet(workbook, outputFileName): """ Saves the in-memory WorkBook object into the specified file """ workbook.save(outputFileName) def getDefaultOutputFileName(inputFileName): """ Returns the name of the default output file based on the value of the input file. The default output file is always created in the current working directory. This can be overriden using the -o or --output option to explicitly specify an output file """ baseName = os.path.basename(inputFileName) rootName = os.path.splitext(baseName)[0] return string.join([rootName, "xls"], '.') def renameOutputFile(outputFileName, fno): """ Renames the output file name by appending the current file number to it """ dirName, baseName = os.path.split(outputFileName) rootName, extName = os.path.splitext(baseName) backupFileBaseName = string.join([string.join([rootName, str(fno)], '-'), extName], '') backupFileName = os.path.join(dirName, backupFileBaseName) try: os.rename(outputFileName, backupFileName) except OSError: print "Error renaming output file:", outputFileName, "to", backupFileName, "...aborting" sys.exit(-1) def validateOpts(opts): """ Returns option values specified, or the default if none """ titlePresent = False linesPerFile = -1 outputFileName = "" sepChar = "," for option, argval in opts: if (option in ("-t", "--title")): titlePresent = True if (option in ("-l", "--lines")): linesPerFile = int(argval) if (option in ("-s", "--sep")): sepChar = argval if (option in ("-o", "--output")): outputFileName = argval if (option in ("-h", "--help")): usage() return titlePresent, linesPerFile, sepChar, outputFileName def main(): """ This is how we are called """ try: opts,args = getopt.getopt(sys.argv[1:], "tl:s:o:h", ["title", "lines=", "sep=", "output=", "help"]) except getopt.GetoptError: usage() if (len(args) != 1): usage() inputFileName = args[0] try: inputFile = open(inputFileName, 'r') except IOError: print "File not found:", inputFileName, "...aborting" sys.exit(-1) titlePresent, linesPerFile, sepChar, outputFileName = validateOpts(opts) if (outputFileName == ""): outputFileName = getDefaultOutputFileName(inputFileName) workbook, worksheet = openExcelSheet(outputFileName) fno = 0 lno = 0 titleCols = [] reader = csv.reader(inputFile, delimiter=sepChar) for line in reader: if (lno == 0 and titlePresent): if (len(titleCols) == 0): titleCols = line writeExcelHeader(worksheet, titleCols) else: writeExcelRow(worksheet, lno, line) lno = lno + 1 if (linesPerFile != -1 and lno >= linesPerFile): closeExcelSheet(workbook, outputFileName) renameOutputFile(outputFileName, fno) fno = fno + 1 lno = 0 workbook, worksheet = openExcelSheet(outputFileName) inputFile.close() closeExcelSheet(workbook, outputFileName) if (fno > 0): renameOutputFile(outputFileName, fno) if __name__ == "__main__": main()

Based on my not finding completed examples for this kind of thing on the web, I can only assume that the script is probably too trivial for most seasoned programmers to consider contributing back to the community. However, based on the few questions I saw about whether such a thing exists, I would conclude that there are some programmers (like me) for whom this is sufficiently non-trivial to consider looking around before writing one themselves. If you are in the latter category, I hope you find this script useful.