Write data to excel from 2 files using Python

Question

I have two files having diff data. I want to write it to excel using python . I have given defined 4 columns Headlines.

Below is the snippet. I want both to club so that I do not have to define row and col two times . ABC.txt and LMN.txt both have data to write in xlsx.

    row=1
    col=0
    with open('ABC.txt','r') as f:
        for K in f:
            worksheet.write(row,col,K)
            row+=1
    row=1
    col=0
    with open('LMN.txt','r') as f:
        for X in f:
            worksheet.write_url(row  , col+1,  X)
            row += 1

         workbook.close()

Shovalt · Accepted Answer · 2016-04-24 14:18:58Z

1

You really should consider using a package like Pandas. This type of thing is ridiculously easy in pandas:

import pandas as pd

df = pd.DataFrame(columns=['col1', 'col2'])

df['col1'] = ['hello1', 'world1']
df['col2'] = ['hello2', 'world2']
print df

df.to_excel('output.xls', index=False)

Output:

     col1    col2
0  hello1  hello2
1  world1  world2

Notice that the columns can be passed as arrays, so you can build your arrays as you like and then place them into columns.

Edit:

In case you can't use Pandas (or won't, for some reason), you can use a low-tech solution such as zipping. Assuming you can get all of your columns ready, you can use good old zipping to transform them into rows:

col1 = ['hello1', 'world1', 'again1']
col2 = ['hello2', 'world2', 'again2']
col3 = ['hello3', 'world3', 'again3']

out = ''
for row in zip(col1, col2, col3):
    out += ','.join(row) + '\n'
print out

output:

hello1,hello2,hello3
world1,world2,world3
again1,again2,again3

Then you can write out to a file, say with a csv extension, which is readable by excel.

edited Apr 24, 2016 at 14:18

answered Apr 24, 2016 at 13:51

Shovalt

6,8362 gold badges39 silver badges53 bronze badges

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

2 Comments

RonyA Over a year ago

Looks Interesting. But looks like I cannot use Panda as per my code and for the broad purpose. But I'll surely give it a try .

Shovalt Over a year ago

I added another solution, not using Pandas, but bear in mind that you need to have all of the data in memory for this.

Burhan Khalid · Accepted Answer · 2016-04-24 14:10:11Z

0

If the files are too large, you can read them first, the combine the structures together and then write it out in one loop:

with open('ABC.txt', 'r') as f:
   cola = list(f)

with open('LMN.txt', 'r') as f:
   colb = list(f)

rows = zip(cola, colb)

for idx, row in enumerate(rows):
    worksheet.write(idx, 0, row[0])
    worksheet.write_url(idx, 1, row[1])

answered Apr 24, 2016 at 14:10

Burhan Khalid

175k20 gold badges255 silver badges292 bronze badges

1 Comment

RonyA Over a year ago

Actually the first file has data already in it. For the second one I have to create it and at the same time my code output(multilpe) will be written and at last the first file data and the data generated within 2nd one will be clubbed together in columns and rows in xlsx file that was created by the 2 nd process. Also your idea is good .I will try it today .

Collectives™ on Stack Overflow

Write data to excel from 2 files using Python

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related