CODEWITHSUNDEEP
pythoncsvtimestamp
Rio
Rio

Reputation: 365

ValueError: time data '140120 1520' does not match format '%Y-%m-%d %H:%M:%S'

I got this error 

ValueError: time data '140120 1520' does not match format '%Y-%m-%d %H:%M:%S'

I had a csv file which I had to store in db. the data in csv file looks

# date   day  time   rec_no   tem        X        Z             G       

140120    20  1520   920      0.00       0.0    51           0.00         
140120    20  1521   921      37.73      46     -596.05      1.21     
140120    20  1522   922      31.11      42     31.4000      0.50      
140120    20  1523   923      0.00       0.0    -451.50      0.00         
140120    20  1524   924      0.00       0.0    -31.500      0.00

my problem is that I have to combine the first column (date) and 3rd column (time) and to convert it to timestamp according to epoch time before storing in MySQL database. the date format from sensor, which we get in csv file is 

140120 
14 represent 2014, 01 represent the month (January) and 20 represent day.

the time is 

1520
15 represent hour and 20 represent minute. and nothing for seconds 
so we can append 00 for seconds.

where day columns consists of same information as in date last 2 digits, so i neglect this column (day). So in order to convert into timestamp, I need to combine the date and time columns. and i did this and then rebuild the list as shown in my code.

I search over net and found different techniques to convert date and time format but no command or solution is according to my need. my code to get data for above csv file and to convert timestamp is as follow.

with open(item[1]) as f:
        lines = f.readlines()
    for k, line in enumerate(lines):
        if k >= (int(skip_header_line) + int(index_line_number)):
            data_tmp = line.split() 
            print data_tmp  # i got ['140120', '20', '1520', '920', '0.00', '0.0', '51', '0.00', '0', '0.00', '0', '0.00']
            newcolumn = data_tmp[0] + ' ' + data_tmp[2]
            data = [newcolumn] + data_tmp[3:] # re-build the list so we get 
                                              ['140120 1520', '920', '0.00', '0.0', '-31.50', '0.00', '0', '0.00', '0', '0.00']

            strDate = data[0].replace("\"", "")
            print strDate  # i got here  140120 1520
            timestamp = datetime.datetime.strptime(strDate,
                                                   '%Y-%m-%d %H:%M:%S')
            ts = calendar.timegm(timestamp.timetuple())
            data_buffer = []
            for val in data_tmp:
                if val == " ":
                    val = None
                    data_buffer.append(val)
                else:
                    data_buffer.append(float(val))
            cursor.execute(add_data, data_buffer)
            cnx.commit()

cursor.close()
cnx.close()

If somebody give me a hint or example to convert '140120 1520' to timestamp, I would extremely thankful because I had no clue how to deal with this problem. thanx

Upvotes: 2

Views: 1005

Answers (2)

Smart Manoj
Smart Manoj

Reputation: 5861

print datetime.datetime.strptime(strDate,'%y%m%d %H%M')

For more reference

Edit: update the full code

140120 1520 is not a float value

remove the space

Upvotes: 1

Tagc
Tagc

Reputation: 9072

Try '%y%m%d %H%M'. Something worth pointing out is that you need to use %y instead of %Y in order to match years without the century, like '14' for 2014.

from datetime import datetime

s = '140120 1520'
print(datetime.strptime(s, '%y%m%d %H%M'))

Output

2014-01-20 15:20:00

Upvotes: 2

Related Questions