This is like the reverse functionality of Text file to spreadsheet.
One or multiple
.xlsx files in the path of the script get opened and the content is split into multiple
For example, say you have two excel files in the folder:
The output created:
""" Reads in .xlsx files from path were the script is located. Then the data of each column is split into a .txt file """ import glob import openpyxl from openpyxl.utils import get_column_letter def get_text_filename(filename: str, column: int)->str: """ Creates a text filename based on .xlsx file filename and column """ return (filename.rstrip(".xlsx") + "_" + get_column_letter(column) + '.txt') def xlsx_to_txt(filename: str): """ Extract data from a .xlsx file in the script folder into multiple .txt files """ workbook = openpyxl.load_workbook(filename) sheet_names = workbook.sheetnames sheet = workbook[sheet_names] for column in range(1, sheet.max_column + 1): if sheet.cell(row=1, column=column).value: text_filename = get_text_filename(filename, column) with open(text_filename, mode='w') as textfile: for row in range(1, sheet.max_row + 1): if sheet.cell(column=column, row=row).value: textfile.writelines( sheet.cell(column=column, row=row).value + '\n') def spreadsheet_into_text(): """main logic for split spreadsheet data into multiple text files""" for filename in glob.iglob("*.xlsx"): xlsx_to_txt(filename) if __name__ == "__main__": spreadsheet_into_text()
I already incorporated some improvements from Text file to spreadsheet. I wonder how the code can get further improved.