Excel can save a spreadsheet as a CSV file with just a few mouse clicks, but if there are hundreds of Excel files to convert to CSV, it takes several hours to click. Using the openpyxl module in Chapter 12, read all Excel files in the current working directory and output them to CSV file. An excel file may contain mu lt iple worksheets, and you must create a CSV file for each table. The file name of the CSV file should be < excel file name > < table title >. CSV, where < excel file name > is an excel file name without an extension (for example, 'spam'data', rather than 'spam'data. Xlsx'), and < table title > is a string in the Title Variable of the Worksheet object. The program will contain many nested for loops. The framework of the program looks like this:

for excelFile in os.listdir('.'):
	# Skip non-xlsx files, load the workbook object.
	for sheetName in wb.get_sheet_names():
		# Loop through every sheet in the workbook.
		sheet = wb.get_sheet_by_name(sheetName)

		# Create the CSV filename from the Excel filename and sheet title.
		# Create the csv.writer object for this CSV file.

		# Loop through every row in the sheet.
		for rowNum in range(1, sheet.get_highest_row() + 1):
			rowData = [] # append each cell to this list
			# Loop through each cell in the row.
			for colNum in range(1, sheet.get_highest_column() + 1):
				# Append each cell's data to rowData.

			# Write the rowData list to the CSV file.
			
		csvFile.close()

Download the ZIP file excel spreadsheets.ZIP from http://nostarch.com/automatestuff/ and extract the spreadsheets into the directory where the program is located. You can use these files to test your program.

Code

import csv, openpyxl, os

os.makedirs('resultCSV', exist_ok=True)

for excelFile in os.listdir('.\\excelSpreadsheets'):
	if not excelFile.endswith('.xlsx'):
		continue

	print('Converting file: ' + excelFile + '...')
	wb = openpyxl.load_workbook(os.path.join('.\\excelSpreadsheets' , excelFile))

	for sheetName in wb.sheetnames:
		sheet = wb[sheetName]
		csvFile = excelFile[:-5] + '_' + sheet.title + '.csv'
		print('CSV FILE: ' + csvFile)

		csvFileObj = open(os.path.join('resultCSV', csvFile), 'w', newline='')
		csvWriter = csv.writer(csvFileObj)

		for rowNum in range(1, sheet.max_row + 1):
			rowData = []
			for colNum in range(1, sheet.max_row + 1):
				rowData.append(sheet.cell(row=rowNum, column=colNum).value)
			csvWriter.writerow(rowData)
		csvFileObj.close()