0

Having a .xls with the following structure

Matching Fields    Date          Person   
1                  2015-02-10    Bob      
2                  2015-01-27    Billy

I'm trying to convert it to .csv and I was having problems converting the dates, because for example the 2015-02-10 is saved as an float in the csv, not with the date format.

In order to save the date correctly, this is my code:

# -*- coding: utf-8 -*-
import xlrd
import csv

def csv_from_excel():
    wb = xlrd.open_workbook('C:/myfile.xls', encoding_override="cp1252")
    sh = wb.sheet_by_name('Sheet1')
    your_csv_file = open('C:/output.csv', 'wb')
    wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)

    for rownum in xrange(sh.nrows):
        print sh.row_values(rownum)[1]
        date = sh.row_values(rownum)[1]
        if isinstance( date, float) or isinstance( date, int ):
            year, month, day, hour, minute, sec = xlrd.xldate_as_tuple(date,0)
            py_date = "%04d-%02d-%02d" % (year, month, day)
            wr.writerow([py_date] + [unicode(val).encode('utf8') for val in sh.row_values(rownum)][1:])
        else:
            wr.writerow([unicode(val).encode('utf8') for val in sh.row_values(rownum)])

    your_csv_file.close()

csv_from_excel()

The output I get is:

"Matching Fields","Date","Person"
"2015-02-10","42031.0","Bob"
"2015-01-27","42031.0","Billy"

As you can see here are two problems:

  1. The first column, the values of Matching Fields, "1" and "2" are not showing.
  2. The "float format" of the date is still showing.

How can I solve both problems in order to get my desired output?

"Matching Fields","Date","Person"
"1","2015-02-10","Bob"
"2","2015-01-27","Billy"

PS: The reason of using [unicode(val).encode('utf8') for val in sh.row_values(rownum)] instead of just sh.row_values(rownum) is because I've some russian characters on my csv (which are not appearing on this example), so I need to convert each to utf8 with that code.

Avión
  • 7,963
  • 11
  • 64
  • 105
  • You are printing out column 1 2 times. Column 1 is the second column so you never iterate over column 0 (the First one) go to line 12 and 13. – Clijsters May 27 '16 at 09:17
  • `if isinstance( date, float) or isinstance( date, int ):` in this line, seems like the date will be getting read out as a string, not either `float` or `int` - thus you will always be in the `else` clause. – domoarigato May 27 '16 at 10:26
  • Check the answer in this link: [Convert Excel to CSV - Properly Convert Date Fields](https://stackoverflow.com/questions/26043370/convert-excel-to-csv-properly-convert-date-fields) – momen Oct 07 '17 at 11:35

1 Answers1

0

wr.writerow([py_date] + [unicode(val).encode('utf8') for val in sh.row_values(rownum)][1:]) else:

Says to write the date as the first column in the row, which I don't think is what you want. Then it looks like you split the row from the 2nd column until the end and convert them to UTF8 strings. Since the 2nd column is the date in the data, and that's stored as a float, looks like it's happily converting that to a string.

Ron Thompson
  • 1,086
  • 6
  • 12