Create Excel Sheets from different files in Linux with Python

Question

There are 2 txt file in a linux server. first data file:

      a;1234
      b;12334
      c;234234

second data file :

       a ; ass ; asfda
       b ; sdfq;  qwrwffsaa
       c ; asda ; qdasasd

What I try to make is to create a excel file with python which has 2 sheets. First sheet keeps first data file second one should keep second data file.

What I develop so far is:

#!/bin/python

import xlsxwriter
import smtplib
import datetime



now = datetime.datetime.now()
workbookname = 'Excel_'+now.strftime("%Y-%m-%d_%H:%M")+'.xlsx'
workbook = xlsxwriter.Workbook(workbookname)
worksheet = workbook.add_worksheet('Sheet1')
worksheet.write('A1', 'Hostname')
worksheet.write('B1', 'User Name')

worksheet2 = workbook.add_worksheet('User Privilege')
worksheet2.write('A1', 'Hostname')
worksheet2.write('B1', 'User Detail')
worksheet2.write('C1', 'Description')


with open('/tmp/file1.txt') as f:
    content = f.read().splitlines()

i = 0
while i < len(content):
    content2 = content[i].split(';')
    worksheet.write('A'+str(i+2), content2[0])
    worksheet.write('B'+str(i+2), content2[1])
workbook.close()

i = 0
while i < len(content):
with open('/tmp/file2.txt') as f:
content = f.read().splitlines()

    worksheet2.write('A' + str(i + 2), content2[0])
    worksheet2.write('B' + str(i + 2), content2[1])
    worksheet2.write('C' + str(i + 2), content2[2])
    i=i+1

workbook.close()

This script only works for the first sheet it does not write to second sheet.

Answer 1

With pandas this can be done in a couple of lines

import pandas

df1 = pandas.read_csv('file1.csv', sep = ';', header = None)
df2 = pandas.read_csv('file2.csv', sep = ';', header = None)

writer = pandas.ExcelWriter('output.xlsx')
df1.to_excel(writer, 'sheet 1')
df2.to_excel(writer, 'sheet 2')
writer.save()