Python : Saving all CSV files in a directory to a python list and then appending it to a combined excel sheet

Question

I am trying to read all files in a directory and then combine each of those csv files to a single excel sheet. Here is my code so far

import pandas as pd
import datetime as dt
import os
import glob

#set Path to Test Directory
os.getcwd()
mwd = os.chdir('Test')

#Create a list with all CSV Files
files = os.listdir(mwd)

#Create a blank dataframe
combined = pd.DataFrame()

for file in files:
    df=pd.read_csv(files)
    combined = combined.append(df,ignore_index = TRUE)

combined.to_excel('testing.xlsx' , index = False)

When running the code , I get the following error

  File "C:\Users\x\Documents\automation\Testing.py", line 19, in <module>
    df=pd.read_csv(files)
  File "C:\Users\x\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\util\_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\x\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 680, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "C:\Users\x\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 575, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "C:\Users\x\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 934, in __init__
    self._engine = self._make_engine(f, self.engine)
  File "C:\Users\x\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 1233, in _make_engine
    raise ValueError(msg)
ValueError: Invalid file path or buffer object type: <class 'list'>

Brian · Accepted Answer · 2022-07-20 07:29:59Z

1

Should be pd.read_csv(file) and not pd.read_csv(files)

Another suggestion for handling the files.. Instead of files = os.listdir(mwd), you could do something like...

Get the file names files = [file for file in os.listdir(mwd) if file.endswith('.csv')]

Get the file paths files = [os.path.join(mwd, file) for file in os.listdir(mwd) if file.endswith('.csv')]

answered Jul 20, 2022 at 7:29

Brian

7474 gold badges17 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python : Saving all CSV files in a directory to a python list and then appending it to a combined excel sheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related