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

Question

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

score 0 · Answer 1 · answered Jan 15 '23 at 00:12

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: Add worksheet to existing Excel file with pandas

score 0 · Answer 2 · answered Jan 15 '23 at 00:16

Usually, the problem is the type of the sheet name. For example in df.to_excel("Output.xlsx",sheet_name = '1') If I don't put the 1 in the quotation, I will get an error. It must always be of str type

For example, I have the following csv files in Google Collab files:

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.

score 0 · Answer 3 · answered Jan 15 '23 at 00:52

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)

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

3 Answers3