Converting a folder of Excel files into CSV files/Merge Excel Workbooks

Using pywin32, this will find all the .xlsx files in the indicated directory and open and resave them as .csv. It is relatively easy to figure out the right commands with pywin32...just record an Excel macro and perform the open/save manually, then look at the resulting macro.

import os
import glob
import win32com.client

xl = win32com.client.gencache.EnsureDispatch('Excel.Application')

for f in glob.glob('tmp/*.xlsx'):
    fullname = os.path.abspath(f)
    xl.Workbooks.Open(fullname)
    xl.ActiveWorkbook.SaveAs(Filename=fullname.replace('.xlsx','.csv'),
                             FileFormat=win32com.client.constants.xlCSVMSDOS,
                             CreateBackup=False)
    xl.ActiveWorkbook.Close(SaveChanges=False)

I will give a try with my library pyexcel:

from pyexcel import Book, BookWriter
import glob
import os


for f in glob.glob("your_directory/*.xlsx"):
    fullname = os.path.abspath(f)
    converted_filename = fullname.replace(".xlsx", "_converted.csv")
    book = Book(f)
    converted_csvs = BookWriter(converted_filename)
    converted_csvs.write_book_reader(book)
    converted_csvs.close()

If you have a xlsx that has more than 2 sheets, I imagine you will have more than 2 csv files generated. The naming convention is: "file_converted_%s.csv" % your_sheet_name. The script will save all converted csv files in the same directory where you had xlsx files.

In addition, if you want to merge all in one, it is super easy as well.

from pyexcel.cookbook import merge_all_to_a_book
import glob


merge_all_to_a_book(glob.glob("your_directory/*.xlsx"), "output.xlsx")

If you want to do more, please read the tutorial

Look at openoffice's python library. Although, I suspect openoffice would support MS document files.

Python has no native support for Excel file.

Sure. Iterate over your files using something like glob and feed them into one of the modules you mention. With xlrd, you'd use open_workbook to open each file by name. That will give you back a Book object. You'll then want to have nested loops that iterate over each Sheet object in the Book, each row in the Sheet, and each Cell in the Row. If your rows aren't too wide, you can append each Cell in a Row into a Python list and then feed that list to the writerow method of a csv.writer object.

Since it's a high-level question, this answer glosses over some specifics like how to call xlrd.open_workbook and how to create a csv.writer. Hopefully googling for examples on those specific points will get you where you need to go.

You can use this function to read the data from each file

import xlrd

def getXLData(Filename, min_row_len=1, get_datemode=False, sheetnum=0):
  Data = []
  book = xlrd.open_workbook(Filename)
  sheet = book.sheets()[sheetnum] 
  rowcount = 0
  while rowcount < sheet.nrows:
    row = sheet.row_values(rowcount)
    if len(row)>=min_row_len: Data.append(row)
    rowcount+=1
  if get_datemode: return Data, book.datemode
  else: return Data

and this function to write the data after you combine the lists together

import csv

def writeCSVFile(filename, data, headers = []):
  import csv
  if headers:
    temp = [headers]
    temp.extend(data)
    data = temp
  f = open(filename,"wb")
  writer = csv.writer(f)
  writer.writerows(data)
  f.close()

Keep in mind you may have to re-format the data, especially if there are dates or integers in the Excel files since they're stored as floating point numbers.

Edited to add code calling the above functions:

import glob

filelist = glob.glob("*.xls*")
alldata = []
headers = []
for filename in filelist:
  data = getXLData(filename)
  headers = data.pop(0) # omit this line if files do not have a header row
  alldata.extend(data)

writeCSVFile("Output.csv", alldata, headers)