How do i read a date from an Excel Worksheet

General
1

I have 10 years worth of data in an excel spreadsheet to import. Once of the columns is formatted as date. When I ‘read this’ via XOJO it appears in scientific notation as underlying the correctly formatted ‘date’ is just the number/datevalue I assume. I want the date which ‘looks like’ an SQL date to export into my MySQL database date field as an sql date.

Any suggestions as to what ‘extra’ i need to do to accomplish this.

[code]
//excel session import
//read excel worksheet sessions into database

dim excel as new ExcelApplication
dim book as ExcelWorkbook
dim f as folderitem
dim dte as variant

excel.visible=true

f=SpecialFolder.Desktop.child(“modded.xls”)
book=excel.Workbooks.open(f.ShellPath)
dte=excel.Range(“A2”).value

// now how to dte A2 looking like and SQL Date? and not a number

msgbox(dte)

Exception err as OLEException
msgbox str(err)[/code]

2

Something like:

Dim d as new date
d.Year = 1900
d.month = 1
d.Day = 0 // which should back it up to Dec 31.

d.day = d.day + Floor(dte)

Dim s as string = d.SQLDate

3

Jym, thanks for the suggestion but it doesn’t seem to work. The value for example in cell ‘A2’ is 39695 (divided roughly by 365) gives 108 years or so. The actual date I want is 4th September 2008, so add 108 years t0 1900 would be about right your for the total number of days. When I add 39695 to the 1900/1/0, it gives me a date of 1st April 1829.

Baffled :frowning:

4

dim d as new date d.year = 1900 d.month = 1 d.day = 1 d.day = d.day + 39695 -2 msgbox d.SQLDate

5

Hi Paul,

Change the excel call from a value to a text. As an example: excel.Range(“A2”).value to excel.Range(“A2”).text. This keeps the date of 10-Nov-2013 to its text instead of showing 3.4668886401e+9.

Below is the modified code which works on Windows 8.1 with Xojo 2013 r3.2 :slight_smile:

[code]//excel session import
//read excel worksheet sessions into database

dim excel as new ExcelApplication
dim book as ExcelWorkbook
dim f as folderitem
dim dte as variant

excel.visible=true

f=SpecialFolder.Desktop.child(“modded.xls”)
book=excel.Workbooks.open(f.ShellPath)
dte=excel.Range(“A2”).Text // <—change from value to Text
//shows 10-Nov-13 as “10-Nov-13”

msgbox(dte)

Exception err as OLEException
msgbox str(err)[/code]

6

Thanks all, but Eugene nailed it. Nothing more complicated required in my case then to read the cell value as text;

[code]
//excel session import
//read excel worksheet sessions into database

dim excel as new ExcelApplication
dim book as ExcelWorkbook
dim f as folderitem

excel.visible=true

f=SpecialFolder.Desktop.child(“modded.xls”)

book=excel.Workbooks.open(f.ShellPath)

Dim row As New DatabaseRecord

row.Column(“Event_Date”) =excel.Range(“A2”).text
row.Column(“Event_Dayname”) = uppercase(excel.range(“C2”).text)
row.Column(“Event_Session”) = excel.range(“N2”).text

DB.InsertRecord(“sessions”, row)

If DB.Error Then
MsgBox("DB Error: " + DB.ErrorMessage)
End If

Exception err as OLEException
msgbox str(err)[/code]

7

I just noticed the Date serial for Windows is different then for a Mac by 4 years

8

Yes - epoch dates are different
Mac OS chose 1904 way back when - it was the first leap year in the 20th century and gawd knows there may be something else significant
MS used 1900
http://en.wikipedia.org/wiki/Epoch_(reference_date)#Notable_epoch_dates_in_computing