CODEWITHSUNDEEP
pythonxlwt
query
query

Reputation: 81

Excel sheet writing all data in one column

I'm having a problem writing my text to an excel sheet:

Here's my code:

import xlwt

wbk = xlwt.Workbook()
sheet = wbk.add_sheet('python')
row = 0 # row counter
col = 0

f = open('newfile.txt')
for line in f:
    L = line.split('\t')
    for c in L:
        sheet.write(row,col,c)
    row += 1
wbk.save('example12.xls')

And here's input.txt:

Ename   DCname  Competency  Effort

Eng01   DC1 SW  30  
Eng02   DC2 HW  30  
Eng03   DC3 ME  40  
Eng04   DC2 SW  20  
Eng05   DC3 FW  40  
Eng06   DC3 SW  35  
Eng07   DC1 HW  25  
Eng08   DC3 SW  30  
Eng09   DC1 HW  35  
Eng10   DC3 SW  20  
Eng11   DC1 HW  40  
Eng12   DC3 SW  40  
Eng13   DC1 HW  30  
Eng14   DC1 HW  30  
Eng15   DC3 FW  40

But input.txt is writing into only one column, how can I get it to write into different columns?

Upvotes: 1

Views: 2472

Answers (2)

Martin Evans
Martin Evans

Reputation: 46759

Assuming your input text file is separated with tabs, the following should work:

import csv

with open("input.txt", "r") as f_input, open("output.csv", "wb") as f_output:
    csv_input = csv.reader(f_input, delimiter="\t")
    csv_output = csv.writer(f_output)

    text = list(csv_input)
    csv_output.writerows(text)

This will give you a file that can be opened in Excel as follows:

Ename,DCname,Competency,Effort
Eng01,DC1,SW,30
Eng02,DC2,HW,30
Eng03,DC3,ME,40
Eng04,DC2,SW,20
Eng05,DC3,FW,40
Eng06,DC3,SW,35
Eng07,DC1,HW,25
Eng08,DC3,SW,30
Eng09,DC1,HW,35
Eng10,DC3,SW,20
Eng11,DC1,HW,40
Eng12,DC3,SW,40
Eng13,DC1,HW,30
Eng14,DC1,HW,30
Eng15,DC3,FW,40

If you want to create an XLSX file directly using openpyxl, the following can be used:

import csv  
from openpyxl.workbook import Workbook

with open("input.txt", "r") as f_input:
    csv_input = csv.reader(f_input, delimiter="\t")

    wb = Workbook()
    ws1 = wb.active
    ws1.title = "Locations"

    for row in csv_input:
        ws1.append(row)

    wb.save(filename="output.xlsx")

Upvotes: 0

SuperBiasedMan
SuperBiasedMan

Reputation: 9969

Your problem is here:

for line in f:
    L = line.split('\t')
    for c in L:
        sheet.write(row,col,c)
    row += 1

col never gets changed from 0 so it always writes to the same column. You could be incrementing it during that loop, but it's better to use enumerate. enumerate returns the index of each iteration of a loop, so you can count which number column you're on with it. Like this:

for line in f:
    L = line.split('\t')
    for i,c in enumerate(L):
        sheet.write(row,i,c)
    row += 1

i is the number of the column found in the line so it will write each piece of data out to the next column.

Upvotes: 1

Related Questions