0

When exporting pandas dataframe to CSV file, the values in my date column are keep changing, how can I lock them into the format I want?

My date column is a string type in this format:

yyyy-mm-dd

However, when opened in excel after exporting, the dates show as:

m/dd/yy

How can I stop excel from auto-formatting my dates?

user7288808
  • 117
  • 2
  • 8
  • Maybe Converting the `datetime` column to `string` type before using `to_excel` can help?? – Nk03 Jun 21 '21 at 18:06
  • https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates ... [this answer](https://stackoverflow.com/a/51806894/9245853) might be helpful. – BigBen Jun 21 '21 at 18:07

1 Answers1

0

CSV is a string format, so you can only control the string format. Since the final format is excel use the excel format to export the dataframe. You can compare the following files in excel.

import pandas as pd

data = {'date': ['2021-01-30', '2021-01-01'],
       'date2': ['01/30/21', '01/01/01']}

df = pd.DataFrame(data)

df.to_csv('example1.csv')
df.to_excel('example1.xlsx’)
Nk03
  • 14,699
  • 2
  • 8
  • 22
Roy
  • 1
  • 1