Python CSV merge issue

Question

New to python and I am presently in the process of CSV merge using Python 3.7.

import pandas as pd
import os

newdir = 'C:\\xxxx\\xxxx\\xxxx\\xxxx'
list = os.listdir(newdir)  

writer = pd.ExcelWriter('test.xlsx')

for i in range(0,len(list)):
    data = pd.read_csv(list[i],encoding="gbk", index_col=0)
    data.to_excel(writer, sheet_name=list[i])


writer.save()

I try to result as below:

FileNotFoundError: [Errno 2] File b'a.csv' does not exist: b'a.csv'

The problem is all of not csv merge into one xlsx file. Please let me know solution.

user5386938 · Accepted Answer · 2020-01-07 03:02:30Z

1

os.listdir only returns the filenames. You'll need to prepend the folder name to the filename.

import pandas as pd
import os

newdir = 'C:\\xxxx\\xxxx\\xxxx\\xxxx'
names = os.listdir(newdir)

writer = pd.ExcelWriter('test.xlsx')

for name in names:
    path = os.path.join(newdir, name)
    data = pd.read_csv(path, encoding="gbk", index_col=0)
    data.to_excel(writer, sheet_name=name)

writer.save()

Note that I did not bother to check the rest of your code. Oh and please avoid using builtins to name your variables.

answered Jan 7, 2020 at 3:02

user5386938

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

1 Comment

Hsin Over a year ago

Hi, Justin, Thanks a lot :) :) :)

Collectives™ on Stack Overflow

Python CSV merge issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related