Read data from excel and write as CSV format to a file - Python

Question

I have a excel sheet which has 5 rows and 5 columns with the below values:

Time   Launch   Login    Password    Signout
00:01   1.26     1.56      5.24        12.3
00:02   1.22     1.55      5.34        2.35
00:03   1.36     1.53      1.24        2.13
00:05   1.46     1.26      2.24        1.32

How can I convert the above data to a csv format and write it to a text file. I'm able to read them from the excel but when I write, they all get combined.

Output Format:

Time,Launch,Login,Password,Signout
00:01,1.26,1.56,5.24,12.3
00:02,1.22,1.55,5.34,2.35
00:03,1.36,1.53,1.24,2.13
00:05,1.46,1.26,2.24,1.32

My Code:

workbook3 = xlrd.open_workbook('C:\Users\aa\Desktop\Report3.xls', logfile=open(os.devnull, 'w'))
worksheet3 = workbook3.sheet_by_index(0)
num_cols3 = worksheet3.ncols
num_rows3 = worksheet3.nrows
for row_index3 in range(0, num_rows3):
    for col_index3 in range(0, num_cols3):
        cell_val= worksheet3.cell(row_index3, col_index3).value
        with open("C:\Users\aa\Desktop\outputfile.txt", 'a') as f:
            f.write(cell_val)
print "Write Completed"

Kindly guide.

Maybe you should use ';' instead of ',' as a column separator? In several locales, this is the case when you open csv in Excel. — David Dale
– David Dale, Commented Nov 2, 2017 at 20:41
I'm reading the contents from the excel and freshly writing it to a text file, will even this make a difference ? I'm still stuck at converting them to a csv format. — sdgd
– sdgd, Commented Nov 2, 2017 at 20:44

Jack Homan · Accepted Answer · 2017-11-02 21:23:55Z

2

You could try using pandas. It makes what you wanna do super easy.

import pandas as pd
filename = "filename.xlsx"
data = pd.read_excel(filename).to_csv(filename.replace(".xlsx", ".csv"))

Alternatively it's way easier IMO to collect all the data in a 2d array. So something like

data = []
for row_index3 in range(0, num_rows3):
    row = []
    for col_index3 in range(0, num_cols3):
        row.append(worksheet3.cell(row_index3, col_index3).value)
    data.append(row)
with open("C:\Users\aa\Desktop\outputfile.txt", 'w') as f: 
    f.write("\n".join(','.join(map(str, row)) for row in data))
print "Write Completed"

edited Nov 2, 2017 at 21:23

answered Nov 2, 2017 at 21:01

Jack Homan

4132 silver badges6 bronze badges

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

9 Comments

sdgd Over a year ago

Thanks, I've got the solution. but one problem here is that a comma , is being present even after the last value of a row. Any idea how to over come it ?

Jack Homan Over a year ago

You could use ','.join on the row instead of just writing a comma after each value. I edited my answer to show you what I mean.

sdgd Over a year ago

Jack, if i want to write the converted file using pandas to a different location, how can I do it ?

Jack Homan Over a year ago

just replace the filename with the new path. Sorry if I didn't make that clear. data = pd.read_excel(filename).to_csv("C:\Users\aa\Desktop\outputfile.txt"). Documentation: pandas.pydata.org/pandas-docs/version/0.20.3/generated/…

sdgd Over a year ago

One last query, i see a fresh column getting added at the first with serial numbers. how can i remove that ?

|

Carlo Oliveira · Accepted Answer · 2017-11-02 20:44:00Z

0

Use module csv from python runtime https://docs.python.org/2/library/csv.html

import csv
with open('eggs.csv', 'wb') as csvfile:
    spamwriter = csv.writer(csvfile, delimiter=' ',
                            quotechar='|', quoting=csv.QUOTE_MINIMAL)
    spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
    spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])

answered Nov 2, 2017 at 20:44

Carlo Oliveira

11 bronze badge

1 Comment

sdgd Over a year ago

Thanks, I've got the solution. but one problem here is that a comma , is being present even after the last value of a row. Any idea how to over come it ?

sdgd · Accepted Answer · 2017-11-02 21:07:09Z

0

I was able to get an answer for this. Code below.

workbook3 = xlrd.open_workbook('C:\Users\aa\Desktop\Report3.xls', logfile=open(os.devnull, 'w'))
worksheet3 = workbook3.sheet_by_index(0)
num_cols3 = worksheet3.ncols
num_rows3 = worksheet3.nrows
f = open("C:\Users\aa\Desktop\outputfile.txt", 'a')
for row_index3 in range(0, num_rows3):
    for col_index3 in range(0, num_cols3):
        cell_val= worksheet3.cell(row_index3, col_index3).value
        f.write(cell_val+",")
    f.write("\n")       
print "Write Completed"

But one problem here is that a comma , is being present even after the last value of a row. Any idea how to over come it ?

answered Nov 2, 2017 at 21:07

sdgd

7332 gold badges17 silver badges40 bronze badges

Collectives™ on Stack Overflow

Read data from excel and write as CSV format to a file - Python

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related