0

I have this code;

from xlrd import open_workbook
import csv

wb = open_workbook('test.xlsm')

for i in range(2, wb.nsheets):
    sheet = wb.sheet_by_index(i)
    print (sheet.name)
    with open("data/%s.csv" %(sheet.name.replace(" ","")), "w") as file:
        writer = csv.writer(file, delimiter = ",")
        print (sheet, sheet.name, sheet.ncols, sheet.nrows)

        header = [cell.value for cell in sheet.row(0)]
        writer.writerow(header)

        for row_idx in range(1, sheet.nrows):
            row = [int(cell.value) if isinstance(cell.value, float) else cell.value
                   for cell in sheet.row(row_idx)]
            writer.writerow(row)

The code works fine however in the csv file I have excel date in multiple columns but the script converts everything as excel time, is there a way to specify that for columns A,D,F,E,G,H to convert the Excel date into normal mysql date?

martineau
  • 119,623
  • 25
  • 170
  • 301
Svan
  • 23
  • 5
  • xlrd is unmaintained, and should only be used if you absolutely must handle the old XLS format. – AMC Jan 18 '20 at 01:51
  • @AMC - I strongly disagree. `xlrd` is a mature package that works very well, and in some ways is better than `openpyxl`. There is no reason to avoid `xlrd`. – John Y Jan 22 '20 at 20:43
  • @JohnY _and in some ways is better than openpyxl_ How so? – AMC Jan 22 '20 at 20:53
  • I like `xlrd` better mainly because I prefer its API, though that is of course subjective. It is also smaller and simpler and doesn't introduce any dependencies. It is more efficient if you need to load all the data. `openpyxl` certainly has its advantages, and people should feel free to use either package. If you need to read and write the same workbook, it is hard to beat `openpyxl`. I rarely have to do this, and I prefer the writing API of `xlsxwriter` and the reading API of `xlrd`. – John Y Jan 22 '20 at 22:19
  • I have to admit the `xlrd` API is clunkier in some places, and extracting dates is chief among them. – John Y Jan 22 '20 at 22:50
  • Related: https://stackoverflow.com/questions/1108428/how-do-i-read-a-date-in-excel-format-in-python – John Y Jan 22 '20 at 22:50
  • @JohnY Do you not consider the fact that it's unmaintained a disadvantage? The description of the GitHub repo for xlrd is literally "Please use openpyxl where you can... http://www.python-excel.org/". – AMC Jan 23 '20 at 00:59
  • @AMC - Being unmaintained is a disadvantage, of course. That doesn't mean it is no longer a good package. It just means it is basically frozen in its current state. The recommendation on the GitHub repo is essentially just the "responsible" thing to say. Like cover-your-ass legalese. I'm *not* saying the recommendation is insincere. I believe the former maintainers genuinely *do* feel newcomers should be using `openpyxl` instead. But I think that is the recommendation precisely because of the maintenance issue, not because of any lack of quality in `xlrd`. – John Y Jan 23 '20 at 14:53

1 Answers1

0

I don't know about MySQL dates, but I'm sure you can figure out how to get those once you have Python dates. To get Python dates, use the xldate_as_datetime function from xlrd. Instead of

int(cell.value)

you need to use

xlrd.xldate_as_datetime(cell.value, wb.datemode)

for the values which you know are dates. According to your comment, you know which ones are dates. But if you didn't know beforehand, you could test the Excel cell type:

if cell.ctype == xlrd.XL_CELL_DATE:
    mydate = xlrd.xldate_as_datetime(cell.value, wb.datemode)

I didn't want to rewrite your code for you. You should be able to incorporate the information from this answer into your program.

John Y
  • 14,123
  • 2
  • 48
  • 72