CODEWITHSUNDEEP
pythoncsvxls
Satya
Satya

Reputation: 5907

joining two .xls file into a single .xls file with 2 sheets

what i have is two separate csv file and i need to convert them to .xls file and merged those .xls files into a single .xls file having two sub .xls files as two of its sheets(sheet1,sheet2).

what i come up to is successfull in creating two .xls files from .csv files.

for csvfile in glob.glob(os.path.join('.', 'D:\csvjoin\*.csv')):
   wb = xlwt.Workbook()
   ws = wb.add_sheet('data')
   with open(csvfile, 'r') as f:
       reader = csv.reader(f)
       for r, row in enumerate(reader):
           for c, val in enumerate(row):
               ws.write(r, c, val)
   wb.save(csvfile + '.xls')

But failed to join those two .xls into single file with two sheets. in joined .xls file i want SHEET1 = file1.xls and SHEET2 = file2.xls

please suggest.

Upvotes: 0

Views: 56

Answers (1)

Anton Protopopov
Anton Protopopov

Reputation: 31672

You could do it with pandas to_excel method of dataframes as follows:

import pandas as pd
df1 = pd.read_csv('path\\to\\file1.csv')
df2 = pd.read_csv('path\\to\\file2.csv')

writer = pd.ExcelWriter('path\\to\\output\\file.xls')
df1.to_excel(writer,'sheet1')
df2.to_excel(writer,'sheet2')
writer.save()

EDIT

or you could do it with a loop (works for pandas 0.17.0):

import pandas as pd
writer = pd.ExcelWriter('path\\to\\output\\file.xls')

i = 1
for csvfile in glob.glob(os.path.abspath('D:\csvjoin\*.csv')):
    df = pd.read_csv(csvfile)
    df.to_excel(writer, 'sheet%s' % i)
    i += 1

writer.save()

Upvotes: 1

Related Questions