Copy a column from a txt file to a column in Excel file, Python

Question

I am trying to copy the contents of first three columns of a txt file to the first three columns of a Excel file.

Here is my code:

import XlsxWriter
worksheet1 = workbook.add_worksheet()
worksheet1.write('A1', 'Time', bold);worksheet1.write('B1', 'User Value', bold);worksheet1.write('C1','Address', bold);worksheet1.write('D1', 'Serial Number', bold);
items = os.listdir(directory)
for FILE in items:
    if FILE.endswith('file.txt'):
            FileSelection = directory+'/' + FILE
            Array1 = []
            with open(FileSelection, 'r') as f:
                    for line in f:
                        valuesList = line.split('\t')
                        #print valuesList
                        Array1.append(valuesList)

        for j in range(len(Array1)):
            if j == 0:
                continue
            else:
                print Array1[j][0]
                worksheet1.write('A2:D2', Array1[j][0]) #I want to say, copy the columns A to D but start from the second raw

However it copies whole the txt array to first column of Excel file!

What do you mean by "column" of a txt file? Are columns separated by tabs? — brianpck
– brianpck, Commented Nov 10, 2016 at 17:20
Yes they are in form of table(each column is separated by a tab) — Kian
– Kian, Commented Nov 10, 2016 at 17:21
Are you writing to a CSV file (as in your title) or an Excel file (as in your code)? — brianpck
– brianpck, Commented Nov 10, 2016 at 17:23

user3684792 · Accepted Answer · 2016-11-10 17:30:12Z

1

It looks like the 'text file' you want is actually a csv, only with a different delimiter (This is a bit of a confusing convention). You can use the csv module to specify this:

>>> import csv
>>> with open('your file.txt', 'rb') as csvfile:
...     reader = csv.reader(csvfile, delimiter='\t')
...     for row in reader:
...         print ', '.join(row)

There is a similar csv writer module, or do you actually want an excel file?

I have also noticed in your code maybe valueslist should truncate the first 3 columns, as that is what you appear to want...

This is probably why you are getting all of the original file. So change

 valuesList = line.split('\t')

to

valuesList = line.split('\t')[:3]

edited Nov 10, 2016 at 17:30

answered Nov 10, 2016 at 17:22

user3684792

2,6112 gold badges20 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Copy a column from a txt file to a column in Excel file, 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