CODEWITHSUNDEEP
pythonexcelopenpyxlxlrd
Pexe
Pexe

Reputation: 91

How to read and extract data from Excel and paste it into an existing text in a text file using Python?

I want to automatically create several text files and in them include some information I have stored in one excel file. The excel file has a lot of information stored, one row with several cells for each subject and information from each subject is to be written in different text files. The data from the excel file is supposed to be pasted in between a text written in the text files (see code below).

I have already made a code that writes the other information I need in each text file, but I do not know how to get the data from excel to the text file:

import xlrd

file = open("testfile.txt", "w")

file.write("text1.\n")

file.write("text2\n")

file.write("text3\n")

file.write("text4\n")

wb = xlrd.open_workbook(r"C:Data.xls")

I do not know how to continue the code to get it to loop through the excel file, row by row, extract some of the data, paste it into the text in the text file, close the text file and open a new one and do the same.

So the text files should end up looking like this:

text1 copied data from excel1

text2 copied data from excel2

text3 copied data from excel3

etc...

Can someone help me? I am sorry if this is basic, I am new to python. Working in Python 3.4.1

Upvotes: 0

Views: 1111

Answers (1)

Stéphane
Stéphane

Reputation: 1344

I would do that:

import xlrd
xlsfilename='test.xlsx'
test = xlrd.open_workbook(xlsfilename)
number_subjetcs=50 # assuming it is known, otherwise you need a 'foreach line' loop
number_columns=3 # assuming it is known...
for row in range(number_subjetcs): 
    txtfilename = 'testfile' + str(row) + '.txt'
    with open(txtfilename, "w") as f:
        for col in range(number_columns):
            s1 = 'text' + str(col+1) + ' : '
            f.write(s1)
            # assuming there is only 1 sheet in the excel file
            val = test.sheets()[0].cell(row,col).value
            s2 = str(val) + '\n'
            f.write(s2)

Upvotes: 1

Related Questions