0

I've written a python script that creates a new Excel file from an existing one by copying some columns only. For this, I've used tablib library, but I'm facing an error with columns that have dates as values.

In the original file the format is like this 11/23/2007, but in the output file is like this 39409.

I'm saving the excel file like this:

with open('output-2.xls', 'wb') as f:
    new_dataset.headers = headers
    f.write(new_dataset.export('xls'))

When I print the new_dataset the column has datetime.datetime(11, 23, 2007, 0, 0) object as values

So, how can I preserve the dates format in the output file?

Oussama He
  • 555
  • 1
  • 10
  • 31

1 Answers1

0

You can use a dict in your data to preserve this.

I'm doing this way to populate the tablib dataset:

datarows = [ {'column1': 1, 'column2': datetime.datetime(2021,10,1) } ]
new_dataset = tablib.Dataset()
new_dataset.dict = datarows  #<- here populate the dataset

using dict you not need to set the headers, and the dataset preserve the datatypes. And than, export:

with open('output-2.xls', 'wb') as f:
    f.write(new_dataset.export('xls'))

so the tablib will not put the quote when exporting a date.

The docs: https://tablib.readthedocs.io/en/stable/api/#tablib.Dataset.dict

Sérgio B.
  • 35
  • 6