I use the following code for merging data from multiple .csv files into one .xls file. Using this code, all the data are saved in one sheet, but I need to save each file's data in a separate sheet, but in the same file.
Would you help me work out how to do this please?
import glob
import csv
csvfiles=glob.glob('*.csv')
wf=csv.writer(open('output.xls', 'wb'))
for files in csvfiles:
rd=csv.reader(open(files, 'r'))
rd.next()
for row in rd:
wf.writerow(row)
I was running into encoding errors so I used the Unicode CSV reader found here https://docs.python.org/2/library/csv.html. This answer is for Python 2. If you need a Python 3 answer let me know.
import csv, codecs, cStringIO, glob, os
import xlsxwriter
class UTF8Recoder:
"""Iterator that reads an encoded stream and reencodes the input to UTF-8"""
def __init__(self, f, encoding):
self.reader = codecs.getreader(encoding)(f)
def __iter__(self):
return self
def next(self):
return self.reader.next().encode("utf-8")
class UnicodeReader:
"""A CSV reader which will iterate over lines in the CSV file "f",
which is encoded in the given encoding."""
def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
f = UTF8Recoder(f, encoding)
self.reader = csv.reader(f, dialect=dialect, **kwds)
def next(self):
row = self.reader.next()
return [unicode(s, "utf-8") for s in row]
def __iter__(self):
return self
wb = xlsxwriter.Workbook('output.xlsx')
for csvfile in csvfiles:
ws = wb.add_worksheet(os.path.split(csvfile)[-1][:30])
with open(csvfile,'rb') as f:
ur = UnicodeReader(f)
for r, row in enumerate(ur):
for c, col in enumerate(row):
ws.write(r, c, col)
wb.close()
Or you can just:
workbook = Workbook('../your_path/joined.xlsx')
counter = 0
for csv_file in glob.glob(os.path.join('../your_path/', '*.csv')):
sheet_name = 'Sheet_' + str(counter)
counter += 1
worksheet = workbook.add_worksheet(sheet_name)
with open(csv_file, 'rt', encoding='utf8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
workbook.close()
read_csvandto_excelcan both easily accomplish what you're asking.