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.

You cannot write to the file after you close() it, which the program is doing twice. Close it after you are finished writing the data from all the files. — jmcnamara
– jmcnamara, Commented Dec 16, 2018 at 15:25

caverac · Accepted Answer · 2018-12-16 12:34:16Z

3

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()

answered Dec 16, 2018 at 12:34

caverac

1,7072 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Excel Sheets from different files in Linux with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related