Tool for converting Excel files to CSV

Question

We require a tool/program/utility that can do the following -

1. Scan folders to look for Excel files at regular intervals.
2. The Excel file might have different formats and the output has to be in a corresponding format in CSV file.
3. When an Excel file is found, read the file, do some data manipulation to achieve the desired format and then save the output as a CSV file at a desired network location.
4. Data manipulation can be of following kinds - ignore a particular column of the spreadsheet, perform a mathematical operation on two columns and save into a single column in CSV, concatenate two columns, etc.

Does anyone know if there is any tool to achieve the above requirement?

Answer 1

I would suggest using python with the xlrd library, the conversion could be as simple as:

from xlrd import open_workbook
import os
import sys

def xl2csv(filename):
    """ Convert an excel file to csv."""
    basename, ext = os.path.splitext(filename)
    wb = open_workbook(filename)
    for s in wb.sheets():
        print 'Sheet:',s.name
        outfile = open('%s_%s.csv' % (basename, s.name), 'w') 
        for row in range(s.nrows):
            values = []
            for col in range(s.ncols):
                values.append(s.cell(row,col).value)
            outfile.write(','.join(values))
            outfile.write('\n')
        outfile.close()
        print '%s Sheet %s done!' % (filename, s.name)
if __name__ == '__main__'
    for arg in sys.argv[1:]:
        xl2csv(arg)

You can be selective of columns etc. if you wish and os.walk is like magic for traversing your directory structure.

Update 2020

With the growing maturity of Pandas you will almost certainly find that it is even easier and faster to do the same sort of thing by using Pandas. (Thanks for john-y for reminding me of this answer)

Answer 2

You almost certainly will need to write such a tool yourself. While there are various libraries that allow you to interpret Excel files etc. a tool that does this specific thing must need to be created first.

If you decide to write such a tool, I can recommend the Apache POI Library to do the excel handling. It is a Java Library from the well known Apache Foundation. Java could handle the other tasks as well, but is really on the "heavy" side for such a small tool (seriously).

Other languages bring other tools and some tools may even have command line support so you can parse that excel from within a shell script (or batch if you are on windows).

Answer 3

Assuming you're running Windows, you could schedule (using windows scheduler) a Macro-enabled Excel file that would do all this work for you. Of course, you'll need to program what you want to do using VBA. It's not complicated, though:

• Run macro once an Excel book is opened
• Scan a folder using VBA
• Save Excel in CSV format
• Data manipulation will depend on what you need... but StackOverflow might have what you want to do as well :)

Success