Pandas.read_excel reads date into timestamp, I want a string

Question

I read a large Excel file into pandas using .read_excel, and the file has date columns. When read into pandas, the dates default to a timestamp. Since the file is large, I would like to read the dates as a string.

If that is not possible, then I would at least like to export the date back to Excel in the same format as it is in the original file (e.g. "8/18/2009").

My two questions are:

1. Can I avoid converting the Excel date into a timestamp in pandas?
2. If not possible, how can I write back the date in the original format efficiently?

Byword

"When read into pandas the date defaults to a timestamp or, at least, when I export it back to Excel." Which of the two is it? – Geriatrics

According to the comments in this question, there is no way to avoid converting Excel dates into timestamps: #34157330 – Geriatrics

You could try this: https://mcmap.net/q/162609/-faster-way-to-read-excel-files-to-pandas-dataframe – Geriatrics

The code "f.write(vbscript.encode('utf-8'))" from the third comment doesn't work in python 3. I put it in the 2to3 converter and it didn't make changes. Any suggestions? – Byword

What is the error message? – Geriatrics

It wasn't in binary, I changed "f = open('ExcelToCsv.vbs','w')" to "f = open('ExcelToCsv.vbs','wb')" – Byword

You could try to ask the author of the answer, by adding a comment to his answer. This is outside of my area of expertise unfortunately. – Geriatrics

The problem is that Excel doesn't store dates as strings, it stores them as numbers with a special format code. – Raptor

Answer 1

1. I am not sure how to read the date and not convert into timestamp using read_excel.
2. Because the date is already converted into datetime while reading it into a dataframe, here is how the date can be printed in the original format - I have used 'mm/dd/yyyy'.

import pandas as pd

df = pd.read_excel(
    "file_to_read.xlsx",
    sheet_name="sheetname",
)
writer = pd.ExcelWriter(
    "file_to_write.xlsx",
    engine="xlsxwriter",
    datetime_format="mm/dd/yyyy",
)
df.to_excel(
    writer,
    index=False,
    header=True,
    sheet_name="sheetname",
)

Bev

Answer 2

this is similar as issue here. Leave dates as strings using read_excel function from pandas in python

check the answers:

• Using converters{'Date': str} option inside the pandas.read_excel which helps.
  pandas.read_excel(xlsx, sheet, converters={'Date': str})
• you can try convert your timestamp back to the original format
  df['Date'][0].strftime('%Y/%m/%d')

Curtsy

Answer 3

I had the same problem. This is what solved the issue for me:

df = pd.read_excel(excel_link, sheet_name, dtype=str)

If you don't mind converting the df or entire column to string

Nourishing