Python program to convert excel file to csv, issue in date column from excel

Question

I am new to python and uising below code to convert excel file to csv

Code is

#!/bin/env python
import xlrd
import csv
from os import sys

def csv_from_excel(file1):
    workbook = xlrd.open_workbook(file1)
    worksheet = workbook.sheet_by_name('sheet1')
    csv1 = open('test.csv', 'wb')
    wr = csv.writer(csv1,quoting=csv.QUOTE_ALL)

    for rownum in xrange(worksheet.nrows):
        wr.writerow([unicode(entry).encode("utf-8") for entry in worksheet.row_values(rownum)])
    csv1.close()


if __name__ == "__main__":
    csv_from_excel(sys.argv[1])

But column from excel with below values

Case    Code    Date    Amount
5428165773  UA02    4/23/2014    $(1,626.00)

showing as

'Case','Code','Date','Amount'
'5428165773','UA02',,'41752.0','-1626.0'

I also tried adding this but it didn't helped

dialect='excel', quotechar="'"

Check out the xldate_as_tuple function. This might help translate Excel's 'number of days since 1900' to something more meaningful. This Stackoverflow post might be helpful: http://stackoverflow.com/questions/1108428/how-do-i-read-a-date-in-excel-format-in-python — Alex Woolford, Oct 19 '14 at 03:00
Not a dupe (as it's Java) but the same issue as here http://stackoverflow.com/questions/19028192/converting-number-representation-of-date-in-excel-to-date-in-java (Excel storing dates as numbers) — Basic, Oct 19 '14 at 03:02
thanks Alex, i am trying rewriting my code with use xldate_as_tuple function — itsavy, Oct 20 '14 at 01:32

score 0 · Answer 1 · answered Oct 19 '14 at 03:10

Excel uses a floating point number that represents the amount of days since a fixed date. You can use the datetime module to calculate the date and create a string.

import datetime

exceldate = datetime.date(1899, 12, 30)

d = exceldate + datetime.timedelta(days=41752)

print d

datetime.date(2014, 4, 23)

new_date = '{}/{}/{}'.format(d.month, d.day, d.year)

score 0 · Answer 2 · answered Oct 19 '14 at 04:01

If you already installed pandas module, These code will read excel file and store as dataframe

import pandas as pd
xls = read_excel('path_to_file.xls')

Then

xls.to_csv('path_to_csv.csv')

will write dataframe into CSV

you can read more about this in,

http://pandas.pydata.org/pandas-docs/version/0.15.0/io.html#io-excel

http://pandas.pydata.org/pandas-docs/version/0.15.0/io.html#io-store-in-csv

score 0 · Answer 3 · answered Oct 19 '14 at 21:08

I think the following function is what you need and it also deals with datetime.time:

def xldate_to_python_date(value):
    """
    convert xl date to python date
    """
    date_tuple = xlrd.xldate_as_tuple(value, 0)
    ret = None
    if date_tuple == (0, 0, 0, 0, 0, 0):
        ret = datetime.datetime(1900, 1, 1, 0, 0, 0)
    elif date_tuple[0:3] == (0, 0, 0):
        ret = datetime.time(date_tuple[3],
                            date_tuple[4],
                            date_tuple[5])
    elif date_tuple[3:6] == (0, 0, 0):
        ret = datetime.date(date_tuple[0],
                            date_tuple[1],
                            date_tuple[2])
    return ret

Here is the documentation of xldate_as_tuple. The above function is referenced from here

And by the way, your csv_from_excel function can be rewritten as the following if you use my library pyexcel:

import pyexcel

def csv_from_excel(file1):
    excel_file = pyexcel.Reader(file1)
    csv_file = pyexcel.Writer("test.csv")
    csv_file.write_reader(excel_file)
    csv_file.close()

More documentation can be found on read-the-docs

Thanks chfw i tried installing pyexcel and then executing suggested code import pyexcel File "/usr/lib/python2.6/site-packages/pyexcel-0.0.5-py2.6.egg/pyexcel/__init__.py", line 14, in from .readers import Reader, BookReader, Book File "/usr/lib/python2.6/site-packages/pyexcel-0.0.5-py2.6.egg/pyexcel/readers.py", line 10, in from .iterators import SheetIterator File "/usr/lib/python2.6/site-packages/pyexcel-0.0.5-py2.6.egg/pyexcel/iterators.py", line 10, in import six ImportError: No module named six — itsavy, Oct 20 '14 at 01:31
@itsavy, Try pip install six to get the missing module. I will check later why six was missed. Six as a dependenncy was specified in setup.py. Did you install pyexcel using pip or you clone it and install from source? Which version of setuptools did you have? — chfw, Oct 20 '14 at 06:06

score 0 · Answer 4 · edited May 27 '16 at 08:28

yr, mth, dy, hr, min, sec =xlrd.xldate_as_tuple(entry, 0)

this is solving my problem

Thanks everyone and first of all thanks to Alex for very useful direction , right in first attempt

Updates: My code looks like this now, but I am stuck at writing row level output

#!/bin/env python
import xlrd
import csv
from os import sys

def csv_from_excel(file1):
    workbook = xlrd.open_workbook(file1)
    worksheet = workbook.sheet_by_name('sheet1')
    csv1 = open('test.csv', 'wb')
    wr = csv.writer(csv1,quoting=csv.QUOTE_ALL)
    for rownum in xrange(worksheet.nrows):
        if rownum > 2:
            i=0
            for entry in worksheet.row_values(rownum):
                i=i+1
                if i==3:
                    yr, mnth, dy, hr, min, sec =xlrd.xldate_as_tuple(entry, 0)
                    print str(mnth)+'/'+str(dy)+'/'+str(yr)
                    #wr.writerow(str(mnth)+'/'+str(dy)+'/'+str(yr))
                else:
                    print entry
                    #wr.writerow(unicode(entry).encode("utf-8"))
    your_csv_file.close()

if __name__ == "__main__":
    csv_from_excel(sys.argv[1])

Current output 5428165773 UA02 4/23/2014 -1626.0

You would understand i need above output as 5428165773,UA02,4/23/2014,-1626.0

Please comment

Update: This problem solved as well by using print in for loops, insteas of writerow, since it expects whole row

Thanks

Python program to convert excel file to csv, issue in date column from excel

Code is

4 Answers4