Moving data from multiple csv files to xlsx files

GooseCake 0
Viewed 61

I have a folder that contains 2 more folders. Inside each folder is a csv and xlsx file.

Ex:

  • test (folder 1)

    • test.csv
    • test.xlsx

  • test2 (folder 2)

    • test2.csv
    • test2.xlsx

I have a working script that moves data from a csv file to a xlsx file.

Say ‘test.csv’ contains the following data:

A B
test.com yes
test.com/dl no
1.1.1.1 yes

The code below will move that data into test.xlsx:

from openpyxl import load_workbook
import csv

wb = load_workbook(“D:\\local\\test\\test\\test.xlsx”)
ws = wb.active
with open(“D:\\local\\test\\test\\test.csv”, ‘r’) as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save(“D:\\local\\test\\test\\test.xlsx”)

Is there an easy way to move all data from ‘test.csv’ to ‘test.xlsx’ and ‘test2.csv’ to ‘test2.xlsx’ at once? The names of the csv and xlsx files will not always be the same but the location will.

I have tried the following but it returns a traceback error:

from openpyxl import load_workbook
import csv

wb = load_workbook(“D:\\local\\test\\{}\\{}.xlsx”)
ws = wb.active
with open(“D:\\local\\test\\{}\\{}.csv”, ‘r’) as f:
    for row in csv.reader(f):
        ws.append(row)
wb.save(“D:\\local\\test\\{}\\{}.xlsx”)

Thanks!

xlsx csv openpyxl excel python
1 Answers

Assuming that the .xlsx files already exist and are empty, you can use the code below to copy the content of multiple .csv files to those .xlsx files (that have the same stem/filename).

import os
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

directory = r'D:\local\test'

for file in Path(directory).glob('*/*.csv'):
    df = pd.read_csv(file, encoding='utf-8-sig')
    excel_path = os.path.splitext(file)[0]+'.xlsx'
    wb = load_workbook(excel_path)
    ws = wb.active
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)
        wb.save(excel_path)
abokey0
Related