cbrownley / foundations-for-analytics-with-python

459 stars 681 forks source link

ValueError: time data '1/20/14' does not match format '%m/%d/%Y' #5

Open HeQYT opened 6 years ago

HeQYT commented 6 years ago

Hello: when I run(win8.1 64bit, Spyder 3.2.4, MySQL 5.7.20): E:>python 4db_mysql_load_from_csv.py supplier_data.csv it said: "ValueError: time data '1/20/14' does not match format '%m/%d/%Y'", but I changed "str(row[column_index]), '%m/%d/%Y'))" into "str('1/20/2015'), '%m/%d/%Y')) ", it can run , and the result is 👍 +---------------+----------------+-------------+------+---------------+ | Supplier_Name | Invoice_Number | Part_Number | Cost | Purchase_Date | +---------------+----------------+-------------+------+---------------+ | Supplier X | 001-1001 | 2341 | 500 | 2015-01-20 | | Supplier X | 001-1001 | 2341 | 500 | 2015-01-20 | | Supplier X | 001-1001 | 5467 | 750 | 2015-01-20 | | Supplier X | 001-1001 | 5467 | 750 | 2015-01-20 | | Supplier Y | 50-9501 | 7009 | 250 | 2015-01-20 | | Supplier Y | 50-9501 | 7009 | 250 | 2015-01-20 | | Supplier Y | 50-9505 | 6650 | 125 | 2015-01-20 | | Supplier Y | 50-9505 | 6650 | 125 | 2015-01-20 | | Supplier Z | 920-4803 | 3321 | 615 | 2015-01-20 | | Supplier Z | 920-4804 | 3321 | 615 | 2015-01-20 | | Supplier Z | 920-4805 | 3321 | 615 | 2015-01-20 | | Supplier Z | 920-4806 | 3321 | 615 | 2015-01-20 | +---------------+----------------+-------------+------+---------------+ what's the matter? thanks.

sd

HeQYT commented 6 years ago

I've solved the problem, change capital Y to lowercase y: '%m/%d/%Y' ----> '%m/%d/%y'!

Aisuko commented 6 years ago

@HeQYT Hi, Why to do this ? Did you research the reason? I have the same issue. But in another function only '%m/%d/%Y' can be worked.

bnicholl commented 5 years ago

Use '%m/%d/%Y' when the year in the date looks like 6/15/2018. Use '%m/%d/%y' when the year in the date looks like 6/15/18

chenxuzhen commented 5 years ago

if you add a line of 'print(str(row[column_index]))' after 'a_date = datetime.date(datetime.strptime(\ str(row[column_index]), '%m/%d/%y')), you'll find out that the str function converts date format '1/20/2014' to '1/20/14'. That's why the lower case 'y' has to be used in this case. print is a good debugging tool for simple scripting. See my code:

!/usr/bin/env python3

import csv import MySQLdb import sys from datetime import datetime, date

Path to and name of a CSV input file

input_file = sys.argv[1]

Connect to a MySQL database

con = MySQLdb.connect(host='localhost', port=3306, db='my_suppliers', user='root', passwd='root@cxzsql') c = con.cursor()

Read the CSV file

Insert the data into the Suppliers table

file_reader = csv.reader(open(input_file, 'r', newline='')) header = next(file_reader) for row in file_reader: data = [] for column_index in range(len(header)): if column_index < 4: data.append(str(row[column_index]).lstrip('$')\ .replace(',', '').strip()) else: a_date = datetime.date(datetime.strptime(\ str(row[column_index]), '%m/%d/%y')) print(str(row[column_index]))

%Y: year is 2015; %y: year is 15

        a_date = a_date.strftime('%Y-%m-%d')
        #print(a_date)
        data.append(a_date)
#print(data)
c.execute("""INSERT INTO Suppliers VALUES (%s, %s, %s, %s, %s);""", data)

con.commit() print("")

Query the Suppliers table

c.execute("SELECT * FROM Suppliers") rows = c.fetchall() for row in rows: row_list_output = [] for column_index in range(len(row)): row_list_output.append(str(row[column_index])) print(row_list_output)

Gztabo21 commented 4 years ago

same the problem

dnaveenit commented 4 years ago

Same error, the following error is occurred in some random systems even-though datetime utilities not used in the code.

Traceback (most recent call last): File "Test.py", line 192, in File "winappsinit.py", line 50, in search_installed File "winappsinit.py", line 42, in File "winappsinit.py", line 40, in File "winappsinit.py", line 186, in installed_application File "winapps_init.py", line 106, in File "_strptime.py", line 568, in _strptime_datetime File "_strptime.py", line 349, in _strptime ValueError: time data '12/11/2019' does not match format '%Y%m%d'

its-proHAcker commented 3 years ago

you have to just remove "/" from the String and put "," in it .. Boom

Code Are Here "import datetime as dt str = '01,01,2017' datetime_value = dt.datetime.strptime(str,'%d,%m,%Y') print(datetime_value)"

OutPut

2017-01-01 00:00:00