[FIXED] Concatenate a .xls file list into .csv

Issue

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

Solution

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)

Answered By - Celius Stingher