Add multiple csv in a single csv sheet in tabs using Pandas

Question

I have multiple csv files, lets say a.csv , b.csv . I want both a.csv and b.csv in a new c.csv . Both new csv should be in tabs inside as it is. I just want to add both csv in tabs . Is there any short way doing that in pandas. I have the below code with me but its not giving any output ,however its executing . Tried checking few posts in stackoverflow but somehow I did not got the wanted output. Please help me with some better solution.

folders=next(os.walk('.'))[1]
for host in folders:
    Path=os.path.join(os.getcwd(),host)                    
    all_files = glob.glob(os.path.join(Path, "*.csv"))     
    df_from_each_file = (pd.read_csv(f) for f in all_files)
    df   = pd.concat(df_from_each_file, ignore_index=True)
    df.to_csv('c.csv,' index=False)

" Both new csv should be in tabs inside as it is". what do you mean by this?? like two different sheets in a CSV? — Prakash Palnati, May 25 '18 at 05:55
Its like , We want to add two existing csv into a new csv in tabs. — steveJ, May 25 '18 at 05:57
Hope this URL helps you https://stackoverflow.com/questions/29615196/is-csv-with-multi-tabs-sheet-possible — Surya Tej, May 25 '18 at 06:02
It is useful but did not figured out from it. Looking for pandas solutions. — steveJ, May 25 '18 at 06:45
Are you looking for a ‘tab’ as in what you get in an Excel workbook? If so, you cannot do that with a CSV. — Superspork, May 25 '18 at 10:37
Can it be done - output as excel having both tha csv in tab, if possible convert input csv in excel at start of code only ? — steveJ, May 25 '18 at 10:43

Martin Evans · Accepted Answer · 2018-05-28T17:06:48.477

7

A CSV file is really only ever a single sheet. An Excel xlsx file though does support sheets. Pandas can be made to write directly to an Excel file using an ExcelWriter. Something like the following should do what you need:

import pandas as pd
import xlsxwriter
import glob
import os

writer = pd.ExcelWriter('multi_sheet.xlsx', engine='xlsxwriter')
folders = next(os.walk('.'))[1]

for host in folders:
    Path = os.path.join(os.getcwd(), host)

    for f in glob.glob(os.path.join(Path, "*.csv")):
        print(f)
        df = pd.read_csv(f)
        df.to_excel(writer, sheet_name=os.path.basename(f)[:31])

writer.save()

For every CSV file that is found in your folders, a new Excel sheet will be created. The name of the sheet is the name of the CSV file, e.g. a.csv would have the sheetname a.

Note: Excel has a restriction of 31 characters for a sheet name.

edited May 28 '18 at 17:06

answered May 27 '18 at 15:11

Martin Evans

45,791
17
81
97

Is it going to have two tabs in excel for the 2 cvs? I think it will have only one ?sorry for asking this I am outside I can only test after sometime . – steveJ May 27 '18 at 15:46
You will get one sheet per CSV file. – Martin Evans May 27 '18 at 15:49
Any way to have name more than 31 characters of sheet ? – steveJ May 28 '18 at 07:50
1

That appears to be a restriction in Excel. If you have filenames which are longer, you would at least need to use `[:31]` instead. I have updated the script. – Martin Evans May 28 '18 at 17:09

Add multiple csv in a single csv sheet in tabs using Pandas

1 Answers1