CODEWITHSUNDEEP
pythonexceldatecsvimport-from-csv
CharlieDeBeadle
CharlieDeBeadle

Reputation: 129

Change date format when importing from CSV

I have a CSV file where the date is formatted as yy/mm/dd, but Excel is reading it wrongly as dd/mm/yyyy (e.g. 8th September 2015 is read as 15th of September 2008).

I know how to change the format that Excel outputs, but how can I change the format it uses to interpret the CSV data?

I'd like to keep it to Excel if possible, but I could work with a Python program.

Upvotes: 0

Views: 1653

Answers (2)

pnuts
pnuts

Reputation: 59495

Option 3. Import it properly

Use DATA, Get External Data, From Text and when the wizard prompts you choose the appropriate DMY combination (Step 3 of 3, Under Column data format, and Date).

Upvotes: 4

Joran Beasley
Joran Beasley

Reputation: 114088

Option 1. change the format excel reads in

edit: a better method is suggested in the OP comments to accomplish this, I was not aware you could do that

it(excel) uses your windows settings

so you can go to

Control Panel > Clock, Language, Region > (under Region and Language) change the date,time or number format

and enter the appropriate format

Option 2. change the csv date format

from dateutil.parser import parse
with open("output.csv","wb") as fout:
    csv_out = csv.writer(fout)
    for row in csv.reader(open("input.csv","rb")):
        row[date_index] = parse(row[date_index]).strftime("%x")
        csv_out.writerow(row)

Upvotes: 1

Related Questions