Creating csv files from Excel file

[azizrasul]

Many thanks, that worked.

[azizrasul]

Here's an alternative solution which works except that I get single blank lines in between the data rows! I've fiddled with the code to see why it's doing that, but nothing works.

[azizrasul]

Here's a solution (based on post #1) which kinda works (I get Sheet1.csv and Sheet2.csv corresponding to Sheet1 and Sheet2 respectively) except that I get single blank lines in between the data rows! I've fiddled with the code to see why it's doing that, but nothing works.

"file1.csv" doesn't get produced.

import csv
import openpyxl

def xls_to_csv(xls_name, csv_name) -> None:
    wb = openpyxl.load_workbook(xls_name)

    for sheet in wb.sheetnames:
        with open(f'{sheet.title()}.csv', 'w') as csv_file:
            writer = csv.writer(csv_file)
            xls_sheet = wb[sheet]
            maxRow = xls_sheet.max_row + 1
            maxCol = xls_sheet.max_column + 1
            headers = (xls_sheet.cell(row=1, column=col).value for col in range(1, maxCol))
            writer.writerow(headers)

            for r in range(2, maxRow):
                xls_row = (xls_sheet.cell(row=r, column=col).value for col in range(1, maxCol))
                writer.writerow(xls_row)

if __name__ == '__main__':
    import sys
    import pathlib

    xlsfile="D:/Power BI & Python/ExcelData3.xlsx"
    csvfile = "file1.csv"

    with pathlib.Path(xlsfile) as xls_file:
        if xls_file.is_file():
            xls_to_csv(xlsfile, csvfile)

[Larz60+]

You stated that the solution that In Post 30 works.
So why are you not using it?

[azizrasul]

It does work except that I get alternate blank lines that's all. I've fiddled with the code to get rid of the blank lines but can't figure it out.

[deanhystad]

Plus the solution presented in post 13 is superior.

[azizrasul]

Agreed, this is as much to do with trying to understand the code in post #33 and try to learn Python as I am a beginner.

[deanhystad]

Search for python csv writer blank row. This is a well known problem with an easy fix. When opening the csv file for writing, tell it to not add newlines when writing a line.

import csv
import openpyxl
from pathlib import Path

def xls_to_csv(xls_name, csv_name=None) -> None:
    # Get folder, partial filename and suffix for csv file(s).
    # filename will be partial filename_sheet name
    path = Path(xls_name) if csv_name is None else Path(csv_name)
    suffix = ".csv" if csv_name is None else path.suffix
    folder = path.parent
    name, *_ = path.name.split(".")

    wb = openpyxl.load_workbook(xls_name)
    for sheet in wb.sheetnames:
        csv_file = folder/f"{name}_{sheet}{suffix}"
        with open(csv_file, 'w', newline="") as csv_file:
            writer = csv.writer(csv_file)
            for row in wb[sheet].values:  # Get all the values in a row
                writer.writerow(row)

if __name__ == '__main__':
    import sys
    xls_to_csv(*sys.argv[1:])

file1.csv is not produced because you never use csv_name.

[azizrasul]

Thanks deanhystad, I learnt something today. The code worked.

[deanhystad]

Run code in the debugger. Set breakpoints and look at the objects. That is how I found that workbook sheet has a values property. Then I tried iterating and saw it return the values for a row. You will learn a lot about Python very quickly by running in interactive mode. Writing code and only seeing the end results isolates you, makes you rely too much on documentation.