CODEWITHSUNDEEP
pythonpython-3.xcsvxlsx
ck3mp
ck3mp

Reputation: 441

XLSX to CSV and add column

I have a function which takes an Excel file input and converts each tab to a CSV file. It works perfectly see below.

However I want to add a new column to each CSV file e.g every file with a column "Date" with todays date in it. My plan was to load the XLSX to a Dataframe and then add the column before writing to CSV however I was wondering if there is a more elegant solution as some of the Excel files can get into the hundreds of MBs?

def excel_to_csv(excel_file):
    print("Converting to CSV")
    with xlrd.open_workbook(excel_file) as wb:
        sheets = wb.sheets()
        for sh in sheets:
            save_file = f'{os.path.splitext(excel_file)[0]}_{sh.name}.csv'
            with open(save_file, 'w', newline="") as f:
                c = csv.writer(f)
                for r in range(sh.nrows):
                    print(sh.row_values(r))
                    c.writerow(sh.row_values(r))

Thanks,

Upvotes: 0

Views: 217

Answers (1)

Gnudiff
Gnudiff

Reputation: 4305

Just do:

from datetime import date
d=date.today().isoformat()

... and in your CSV writing loop do:

for r in range(sh.nrows):
    row=sh.row_values(r)
    row.insert(0,d)
    c.writerow(row)

Or obviously you can do row.append(d) or row.insert() in a different location, depending on which column you want the date to be in.

Upvotes: 2

Related Questions