I'm trying to concatenate a .xls file list into .csv:

import glob
import pandas as pd

file_xls = glob.glob(location   "\*.xls")
print(file_xls)
read_file = pd.read_excel(location   file_xls)

but I have an error about the concatenation of the list:

TypeError: can only concatenate str (not "list") to str

Is there a specific way to concatenate this list? Thanks in advance!

Goal: Fuse the the .xls files into .csv with the help of a list

CodePudding user response：

I think instead of concatenating strings, you should use os.path.join():

file_xls = glob.glob(location   "\*.xls")

Will return a list of all the file names in the specified root (defined by the user as location)

files = [pd.read_excel(os.path.join(location,x)) for x in file_xls]

Returns a list of all files as pandas DataFrames.

You can concat them by using pd.concat() and output them as csv with df.to_csv()

output = pd.concat(files)
output.to_csv(desired_output_path)

You can also single line absolutely everything with:

pd.concat([pd.read_excel(os.path.join(location,x)) for x in glob.glob(location   "\*.xls")]).to_csv(desired_output_path)

CodePudding user response：

thank you this method is functional, so now I would like to remove the old list of .xls files with os remove :

os.remove(file_xls)

but I have this error message :

TypeError: remove: path should be string, bytes or os.PathLike, not list

Thanks in advance for your answer !