Create a new sheet in .xlsx file

Question

I'm working on a code to extract automatically my data from a .csv, and load it into a .xlsx in the format I need. I want to create a new sheet for every file i use and rename it. My code works with one file, but when i use it on my folder, it doesn't create any new sheet.

I already tried this solution but I didn't succeed to use it.

Here is my code :

import pandas as pd 
import numpy as np 
import datetime as dt 
from pathlib import Path  


pathList=sorted(Path('.').glob('*.csv'))
name=''
output="output/writer.xlsx"

def reader(file) : 
    return pd.read_csv(file,sep=";")

def extract(file,name): 

    file['dte']=pd.to_datetime(file['dte'],errors='coerce')
    file['year']=pd.DatetimeIndex(file['dte']).year

    df=file.set_index([file.groupby('year').cumcount(),'year']).unstack(1)
    df=df.sort_index(1,level=1)
    df.columns=[f"{x}_{y}" for x,y in df.columns]

    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    df.to_excel(writer,sheet_name=f"{name}")

    writer.save()

    return 

for path in pathList : 
    name=''
    readFile=path
    readName=str(readFile)
    print(readFile)
    for i in readName :
        if i=='.':
            break
        name=name+i
    print(name)
    pathFile=reader(readFile)
    extract(pathFile,f"{name}")

I already tried to store the names of my files in a list and create my new sheets in a loop but it doesn't allow list to name it.

If you want to reproduce it, here is two files from my folder : https://www.mediafire.com/file/mj9u4awael87bhc/ardentes.csv/file https://www.mediafire.com/file/xfliok17s35lm6e/bas_en_basset.csv/file

score 0 · Answer 1 · answered Apr 16 '21 at 13:23

Just use global writer and write.save() outside the loop

def extract(file,name): 
    global df
    file['dte']=pd.to_datetime(file['dte'],errors='coerce')
    file['year']=pd.DatetimeIndex(file['dte']).year

    df=file.set_index([file.groupby('year').cumcount(),'year']).unstack(1)
    df=df.sort_index(1,level=1)
    df.columns=[f"{x}_{y}" for x,y in df.columns]

    writer = pd.ExcelWriter(output, engine='xlsxwriter')

    for name in names:
        df.to_excel(writer,sheet_name=name)
 


for path in pathList : 
    name=''
    readFile=path
    readName=str(readFile)
    print(readFile)
    for i in readName :
        if i=='.':
            break
        name=name+i
    print(name)
    pathFile=reader(readFile)
    extract(pathFile,name)
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    for name in names:
        df.to_excel(writer,sheet_name=name)
writer.save()```

Create a new sheet in .xlsx file

1 Answers1