How to batch convert .csv to .xls/xlsx

Question

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?

Answer 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, col in enumerate(row):
                ws.write(r, c, col)
    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.

Answer 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.

Answer 3

There is a lot left unsaid in your question.

Assuming that your CVS files are in a directory structure similar to

c:\randompath\CSV\
    a.csv
    b.csv
    c.csv
      :
      :
    z.csv

and you want to end up with

c:\randompath\XLS\
    a.xls
    b.xls
    c.xls
      :
      :
    z.xls

I can think of three routes to go depending on the ratio of upfront work to clean-up work you are willing to do.

1. No Precoding: Use Windows Explorer to navigate to the CSV files use whatever method works best to select the files to be converted (lasso, ctrl+a, ctrl+click, shift+click) then right click one of the selected files and click on Open. This will open all the files in Excel. Then for each file you can select "File" and "Save as" and finally choose the new file format you want it saved to.
2. A Simple Batch File: That batch file could use wild cards and/or a for each loop structure to open each of the CSV files for you and then you could manually process them as before.
3. Create a VBA program within an Excel Spreadsheet: VBA could automatically opens each CSV file and then saves it to an Excel format. You could even add a simple message box that offers a choice of either xls or xlsx as each file is opened.

Note that I haven't written any code to do these things (yet) I am just offering ideas for a starting point. Perhaps if you could give more details about what you want you could get more detailed explanations for how to handle approach from me or another forum member.