adding multiple csv to an excel file with keeping the names of these csvs files-CodePudding

I am trying to collect multiple csvs files into one excel workbook and keeping the names of csvs files on each sheet but the loop can not save the sheet for each step and I only get only the last sheet only ?

for i in range(0,len(dir)):

for filee in os.listdir(dir):
 if filee.endswith(".csv"):
    file_path = os.path.join(dir, filee)
    df = pd.read_csv(file_path, on_bad_lines='skip')
    df.to_excel("output.xlsx",sheet_name=filee, index=False)
    i=i 1

I have tried ExcelWriter but the file got error could anyone help to fix this problem Regards

CodePudding user response：

This code would produce a SyntaxError since the first for loop is not defined properly. However, assuming that it is an IndentationError and moving to the for-loop body.

In each .csv file, the for-loop reads that into a pandas.DataFrame and writes it into output.xlsx. Basically, you override the file in each iteration. Thus, you only see the last sheet only.

Please! have a look to this link:

With the following code, I first put all of them in df and then transfer them to the Excel file (in separate sheets).

import pandas as pd

df = {}
for i in range(1,5): 
  df[i] = pd.read_csv('sample_data/file' str(i) '.csv')

with pd.ExcelWriter('output.xlsx') as writer:  
  for i in range(1,5):
    df[i].to_excel(writer, sheet_name = str(i))

It works fine for me and I don't get any errors.

CodePudding user response：

You can use a dict comp to store all dfs and file names from each csv then pass it to a function. Unpack dict with a list comp and write to sheets.

from pathlib import Path

import pandas as pd


path = "/path/to/csv/files"


def write_sheets(file_map: dict) -> None:
    with pd.ExcelWriter(f"{path}/output.xlsx", engine="xlsxwriter") as writer:
        [df.to_excel(writer, sheet_name=sheet_name, index=False) for sheet_name, df in file_map.items()]


file_mapping = {Path(file).stem: pd.read_csv(file) for file in Path(path).glob("*csv")}
write_sheets(file_mapping)