Friday, October 4, 2013

Python - Convert csv to excel

This python snippet is useful to convert a csv file to excel file (.xlsx):

* Install openpyxl module:

$ pip install openpyxl

excelutil.py
========

import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter


def csv_to_excel(csv_path, delimiter, delimiter_txt, excel_path, excel_title):
csv_file = open(csv_path, 'rb')
csv.register_dialect(delimiter_txt, delimiter=delimiter)

reader = csv.reader(csv_file, dialect=delimiter_txt)

wb = Workbook()
ws = wb.worksheets[0]
ws.title = excel_title

for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell

wb.save(filename = excel_path)
csv_file.close()


For example, I want to convert mycsv.csv to myexcel.xlsx:

>>> from excelutils import csv_to_excel
>>> cp = 'mycsv.csv'
>>> de = ','
>>> detxt = 'comma'
>>> ep = 'myexcel.xlsx'
>>> title = 'Genius'
>>> csv_to_excel(cp, de, detxt, ep, title)


Or you can use xlsxwriter (for xlsx) and xlwt (for xls) modules: