How to batch convert .csv to .xls/xlsx

I am looking for a way to quickly convert a directory of .csv files into the .xls or .xlsx format (would be nice if I could do either/or).

Is there an easy way to do this or do I need to install a third party program?

Solution 1:

Assuming you like and have Python (for some reason), you could use this script I whipped up:

import os
import glob
import csv
import xlwt # from http://www.python-excel.org/

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    wb = xlwt.Workbook()
    ws = wb.add_sheet('data')
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, val in enumerate(row):
                ws.write(r, c, val)
    wb.save(csvfile + '.xls')

Ran in the directory with all the CSV files, it will convert them all and slap a ".xls" onto the end.

For Excel 2007+ (xlsx files) supporting up to about 1 Mrows:

import os
import glob
import csv
import openpyxl # from https://pythonhosted.org/openpyxl/ or PyPI (e.g. via pip)

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    wb = openpyxl.Workbook()
    ws = wb.active
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

Solution 2:

Here is a perl script that supposedly does it, but it does seem like a lot of work to do something that is already built into Excel.