3

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?

4
  • 1
    What OS are you using? Windows?
    – Undo
    Apr 14, 2014 at 16:24
  • Do you need this to be gratis or would you be willing to spend money on it (and if so, how much?) Apr 14, 2014 at 16:32
  • 1
    #4 sounds the most challenging - do you have any idea of how you want to tell it what data manipulation to do? For #2 do you have any examples of what formats you'd want in the XLS(X) & CSV file? Apr 14, 2014 at 16:35
  • 3
    Do you have experience as a programmer? This is not difficult (if you have programming experience), but does require a custom application.
    – panofish
    Sep 5, 2014 at 14:20

3 Answers 3

3

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)

1
  • 2
    With the advent of pandas, using Python for this kind of stuff is even easier.
    – John Y
    Jan 20, 2020 at 16:42
1

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

5
  • This is what I was thinking just with python or even powershell.
    – Grady D
    Apr 14, 2014 at 21:30
  • @aDroidman If you know matching libraries / tools for one or the other you should post an answer as at least powershell is far more lightweight then java (I don't think Java is the best tool for the job) Apr 15, 2014 at 5:45
  • Not sure if there are any libraries or tools. I was thinking more along the line of string comparison looking for a specific format of data or reading headers. Either way you do it you run into issues validating the data since there are multiple formats. You could create massive if/elseif/else statements if there is a way to tell what format the excel file is in and assuming there are only a limited number of formats.
    – Grady D
    Apr 15, 2014 at 11:13
  • @aDroidman That still requires you to transform that excel to CSV, and I doubt (though I haven't tested it) that excel itself is scriptable :) Apr 15, 2014 at 11:49
  • 1
    Hmmm true, also run into another road block due to multiple workbooks per file. Java might actually be the only logical way, maybe c++.
    – Grady D
    Apr 15, 2014 at 12:17
0

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:

Success

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.