Daily Archives: March 23, 2016

CSV to Excel (.xlsx) Converter Python – xlsxwriter

CSV to Excel Converter program in Python

This program will convert .CSV ( Comma-Separated Values ) Files to Excel (.XLSX ) format using python XlsxWriter module. You can download the file directly from my GitHub page:

Below is the documentation as how the program works.

Step 1 – Required modules for the program

import csv, os
from glob import glob
from xlsxwriter.workbook import Workbook

Step 2 – This for loop will give you list of csv files in the specified directory. Change  ‘csvFiles/*.csv’ to whatever directory your files are in.

for csvfile in glob('csvFiles/*.csv'):

Step 3 – When you loop through the list you will get something like ‘csvFiles/report.csv’ as output if you print the variable csvfile. But we just want the file name without the full path so we are going to use os.path.basename(csvfile) which will give us ‘report.csv’ as output. Then we are going to split the file name and extension using the split function and we will get a list like [ ‘report’, ‘csv’ ]. Lastly we are going to use List Indices to access the first element in the sequence and store it in the variable name.

name = os.path.basename(csvfile).split('.')[-2]

Step 4 – Then we are going to make new instance of Workbook class and provide parameters to the constructor. Workbook constructor takes two parameters (filename, options). For complete list of options refer to the workbook class documentation . Then we are going to use the workbook object to add a new worksheet using the add_worksheet() method and store it in the variable worksheet.

workbook = Workbook('xlsxFiles/' + str(name) + '.xlsx', {'strings_to_numbers': True,
'constant_memory': True})
worksheet = workbook.add_worksheet()

Step 5 – Now we are going to open the csvfile in the read mode and get the reader object using csv.reader(f) and store it in the variable r. Once we have the reader object then we can loop through it using enumerate to get the row. Once we have row then we can loop through it using enumerate to get the columns. While we are inside the inner most loop we are going to use worksheet object to write data using the worksheet.write() method. write() method takes three + parameters worksheet.write(row, col, *args), for more information refer to the worksheet class documentation

with open(csvfile, 'r') as f:
r = csv.reader(f)
for row_index, row in enumerate(r):
for col_index, data in enumerate(row):
worksheet.write(row_index, col_index, data)

Step 6 – Finally we are going to close the Excel file using the close() method

print(" .CSV to .XLSX Conversion Successful")

If everything works you should see this message “.CSV to .XLSX Conversion Successful “. If you are still having problems making it work, go ahead and leave me a comment with description of the problem. Will try to get back to you asap 🙂