Python read datestring from excel file

Question

I have data in excel file with Column A as DateTime String and Column B with data. I want to read the excel file in Python and create dictionary of two elements. The first being a datetime vector and second being data.

The data in excel file looks as:

DateTime    Marks
1/1/10 23:00     38.86 
2/1/10 12:00     36.19 
1/1/10 13:00     35.10 
1/1/10 14:00     39.87 
5/1/10 15:00     39.48 
1/1/10 16:00     38.64 
1/1/10 17:00     39.19

I wrote the following snippet to start:

import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('C:\Users\JD\Documents\RandomNumber','DateTimeData.xlsx'))

But I have no clue what to do next?

have you tried "google" with "Python read datestring from excel file"? — Dirk Reichel
– Dirk Reichel, Commented May 1, 2016 at 1:09
From writing pypi.org/project/tabxlsx I know that Excel stores the date as simple number and it just the number_format to show them differently. — Guido U. Draheim
– Guido U. Draheim, Commented Aug 6, 2024 at 5:10

Sudipta Basak · Accepted Answer · 2016-05-01 02:22:46Z

1

import os
import xlrd
import datetime


excel = os.path.join(os.getcwd(), 'test.xlsx')
book = xlrd.open_workbook(excel, 'r')
data = book.sheet_by_name('Sheet1')

for row in range(data.nrows)[1:]:
    excel_date = data.row_values(rowx=row)[0]
    print datetime.datetime(* xlrd.xldate_as_tuple(excel_date, datemode=0))

answered May 1, 2016 at 2:22

Sudipta Basak

3,1592 gold badges21 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ola · Accepted Answer · 2016-05-01 01:15:25Z

1

Here is a hint anyway. Use a for loop to parse through the file.

datetime=[]
data=[]
for line in wb:
      datetime=line.split()[0:2]
      data=line.split().pop()
      strdate=' '.join(datetime)
      if 'DateTime' not in strdate:
        print strdate
        print data

This prints out date/time and the data. Hope this helps.

edited May 1, 2016 at 1:15

answered May 1, 2016 at 1:09

Ola

911 gold badge2 silver badges9 bronze badges

Collectives™ on Stack Overflow

Python read datestring from excel file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related