| 4007 |
chandransh |
1 |
#!/usr/bin/python
|
|
|
2 |
|
|
|
3 |
'''
|
|
|
4 |
Tool to convert CSV files (with configurable delimiter) to Excel spreadsheets.
|
|
|
5 |
|
|
|
6 |
This is an adaptation of the script present at
|
|
|
7 |
http://sujitpal.blogspot.com/2007/02/python-script-to-convert-csv-files-to.html
|
|
|
8 |
|
|
|
9 |
The CSV to excel conversion logic has been preserved but the parsing of options
|
|
|
10 |
has been moved to the more recent optparse.
|
|
|
11 |
|
|
|
12 |
Created on 08-Nov-2011
|
|
|
13 |
|
|
|
14 |
@author: Chandranshu
|
|
|
15 |
'''
|
|
|
16 |
import optparse
|
|
|
17 |
import csv
|
|
|
18 |
import string
|
|
|
19 |
import sys
|
|
|
20 |
import os.path
|
|
|
21 |
from pyExcelerator import Workbook
|
|
|
22 |
|
|
|
23 |
def openExcelSheet(outputFileName):
|
|
|
24 |
"""
|
|
|
25 |
Opens a reference to an Excel WorkBook and Worksheet objects
|
|
|
26 |
"""
|
|
|
27 |
workbook = Workbook()
|
|
|
28 |
worksheet = workbook.add_sheet("Sheet 1")
|
|
|
29 |
return workbook, worksheet
|
|
|
30 |
|
|
|
31 |
def writeExcelHeader(worksheet, titleCols):
|
|
|
32 |
"""
|
|
|
33 |
Write the header line into the worksheet
|
|
|
34 |
"""
|
|
|
35 |
cno = 0
|
|
|
36 |
for titleCol in titleCols:
|
|
|
37 |
worksheet.write(0, cno, titleCol)
|
|
|
38 |
cno = cno + 1
|
|
|
39 |
|
|
|
40 |
def writeExcelRow(worksheet, lno, columns):
|
|
|
41 |
"""
|
|
|
42 |
Write a non-header row into the worksheet
|
|
|
43 |
"""
|
|
|
44 |
cno = 0
|
|
|
45 |
for column in columns:
|
|
|
46 |
worksheet.write(lno, cno, column)
|
|
|
47 |
cno = cno + 1
|
|
|
48 |
|
|
|
49 |
def closeExcelSheet(workbook, outputFileName):
|
|
|
50 |
"""
|
|
|
51 |
Saves the in-memory WorkBook object into the specified file
|
|
|
52 |
"""
|
|
|
53 |
workbook.save(outputFileName)
|
|
|
54 |
|
|
|
55 |
def renameOutputFile(outputFileName, fno):
|
|
|
56 |
"""
|
|
|
57 |
Renames the output file name by appending the current file number to it
|
|
|
58 |
"""
|
|
|
59 |
dirName, baseName = os.path.split(outputFileName)
|
|
|
60 |
rootName, extName = os.path.splitext(baseName)
|
|
|
61 |
backupFileBaseName = string.join([string.join([rootName, str(fno)], '-'), extName], '')
|
|
|
62 |
backupFileName = os.path.join(dirName, backupFileBaseName)
|
|
|
63 |
try:
|
|
|
64 |
os.rename(outputFileName, backupFileName)
|
|
|
65 |
except OSError:
|
|
|
66 |
print "Error renaming output file:", outputFileName, "to", backupFileName, "...aborting"
|
|
|
67 |
sys.exit(-1)
|
|
|
68 |
|
|
|
69 |
def getDefaultOutputFileName(inputFileName):
|
|
|
70 |
"""
|
|
|
71 |
Returns the name of the default output file based on the value
|
|
|
72 |
of the input file. The default output file is always created in
|
|
|
73 |
the current working directory. This can be overriden using the
|
|
|
74 |
-o or --output option to explicitly specify an output file
|
|
|
75 |
"""
|
|
|
76 |
baseName = os.path.basename(inputFileName)
|
|
|
77 |
rootName = os.path.splitext(baseName)[0]
|
|
|
78 |
return string.join([rootName, "xls"], '.')
|
|
|
79 |
|
|
|
80 |
def main():
|
|
|
81 |
parser = optparse.OptionParser()
|
|
|
82 |
parser.add_option("-t", "--title", dest="title_present",
|
|
|
83 |
action="store_true",
|
|
|
84 |
help="If set, the first line is the title line")
|
|
|
85 |
parser.add_option("-l", "--lines", dest="lines",
|
|
|
86 |
type="string",
|
|
|
87 |
help="Split output into files of LINE lines or less each.",
|
|
|
88 |
metavar="LINE")
|
|
|
89 |
parser.add_option("-s", "--sep", dest="sep",
|
|
|
90 |
type="string",
|
|
|
91 |
help="Use SEP as a record separator rather",
|
|
|
92 |
metavar="SEP")
|
|
|
93 |
parser.add_option("-o", "--output", dest="outputFileName",
|
|
|
94 |
type="string",
|
|
|
95 |
help="Create the output file with name NAME",
|
|
|
96 |
metavar="NAME")
|
|
|
97 |
parser.set_defaults(title_present=False, lines=-1, sep="\t", outputFileName="")
|
|
|
98 |
parser.set_usage("%prog [options] csvfile")
|
|
|
99 |
(options, args) = parser.parse_args()
|
|
|
100 |
if len(args) != 1:
|
|
|
101 |
parser.error("Please at least provide the input file name")
|
|
|
102 |
|
|
|
103 |
inputFileName = args[0]
|
|
|
104 |
try:
|
|
|
105 |
inputFile = open(inputFileName, 'r')
|
|
|
106 |
except IOError:
|
|
|
107 |
print "File not found:", inputFileName, "...aborting"
|
|
|
108 |
|
|
|
109 |
titlePresent, linesPerFile, sepChar, outputFileName = options.title_present, options.lines, options.sep, options.outputFileName
|
|
|
110 |
if outputFileName == "":
|
|
|
111 |
outputFileName = getDefaultOutputFileName(inputFileName)
|
|
|
112 |
|
|
|
113 |
workbook, worksheet = openExcelSheet(outputFileName)
|
|
|
114 |
fno = 0
|
|
|
115 |
lno = 0
|
|
|
116 |
titleCols = []
|
|
|
117 |
reader = csv.reader(inputFile, delimiter=sepChar)
|
|
|
118 |
for line in reader:
|
|
|
119 |
if lno == 0 and titlePresent:
|
|
|
120 |
if len(titleCols) == 0:
|
|
|
121 |
titleCols = line
|
|
|
122 |
writeExcelHeader(worksheet, titleCols)
|
|
|
123 |
else:
|
|
|
124 |
writeExcelRow(worksheet, lno, line)
|
|
|
125 |
lno = lno + 1
|
|
|
126 |
if (linesPerFile != -1 and lno >= linesPerFile):
|
|
|
127 |
closeExcelSheet(workbook, outputFileName)
|
|
|
128 |
renameOutputFile(outputFileName, fno)
|
|
|
129 |
fno = fno + 1
|
|
|
130 |
lno = 0
|
|
|
131 |
workbook, worksheet = openExcelSheet(outputFileName)
|
|
|
132 |
inputFile.close()
|
|
|
133 |
closeExcelSheet(workbook, outputFileName)
|
|
|
134 |
if (fno > 0):
|
|
|
135 |
renameOutputFile(outputFileName, fno)
|
|
|
136 |
|
|
|
137 |
if __name__ == '__main__':
|
|
|
138 |
main()
|