Date field not pulling into CSV in Python

[83dons]

Hi I am using the following python code to convert a table to a CSV that has inverted commas round every field. It is working apart from for date fields where I get I get "Microsoft.OleDb.Date" instead of expected e.g. "01/02/2023". ANy ideas if this can be fixed using the below coding rather than have to create new columns for every date field of different type ie text instead of date. This converter should be able to process a date field I would have thought.

import pandas as pd
dataset.to_csv(
r"REMOVED",
sep=',',
quotechar='"',
quoting=1,
index=False,
na_rep='',
encoding='utf-8'
)

[burakkaragoz]

Hi @83dons ,

Looks like the issue is coming from how the date fields are being interpreted before the export. If you're seeing "Microsoft.OleDb.Date" in the CSV, it probably means the column is still in some kind of object or COM type and not properly converted to a datetime format in pandas.

Try explicitly converting the date columns before exporting:

dataset['YourDateColumn'] = pd.to_datetime(dataset['YourDateColumn'], errors='coerce')

You can apply this to all date columns if needed. Once they’re in proper datetime format, to_csv() should handle them correctly.

Let me know if you want help identifying which columns need conversion.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[83dons]

Hi @burakkaragoz if I add the following into Power BI I get a blank field for date of birth.

import pandas as pd
dataset['Date of Birth'] = pd.to_datetime(dataset['Date of Birth'], errors='coerce')
dataset.to_csv(
  r"REMOVED",
  sep=',',
  quotechar='"',
  quoting=1,
  index=False,
  na_rep='',
  encoding='utf-8'
)

I notice if I change the power BI column from Date type to Date/Time then run the csv function that pulls through but then its in the wrong format - 1975-11-25T00:00:00.0000000 it needs to be 25/11/1975

Any ideas?

[burakkaragoz]

Nice! That’s a solid way to ensure Power BI gets the date fields in the right format.

If you're working with real datasets where some dates might be missing or malformed, combining your approach with a fallback like this can help:

df['birth_date'] = pd.to_datetime(df['birth_date'], errors='coerce').fillna(pd.Timestamp('1900-01-01'))

This way, you keep the datetime format consistent and avoid None or NaT values that might confuse Power BI visuals or DAX logic.

Let me know if you’re also exporting this to CSV or loading it directly into Power BI — I can help fine-tune it based on the flow.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[83dons · Accepted solution]

I think it is actually easier to just create a new column of type text from the date column then remove the original and rename the new column. That then pulls through ok using the dataset to CSV function.