Importing Data from Excel deletes Date's

[ScardyBob]

Hello,

I am having trouble importing data from an Excel Worksheet. When I try
to import the data, everything works except certain columns that
contain dates, where it replaces the date with a null in the access
table. To import the data I do the following.

1. Create a table based on the excel spreadsheet setup with the
following query
sSQL = "INSERT INTO " & TblName & " SELECT * " _
& "FROM [Excel 8.0;HDR=Yes;Database="&ExcelFile&"].["&ExcelRange&"] "
& "WHERE False;"

2. I then correct any field data types.

3. Finally I use the same query above except without "WHERE False;"

I believe the problem arises because if a record has no date, then the
string " - - " is entered instead of just leaving the cell blank.
After doing some searching it appears that when access imports data, it
internally determines the data type from the first couple of records.
For the problem fields, the first records are " - - " instead of a
date in the form XX/XX/XXXX. This seems confirmed by the fact that the
first query makes those fields of type 'text' instead of 'date/time'
(which is why I have to correct those fields in part 2). Leaving those
fields as text in the Access table still replaces the dates with nulls,
but now leaves the " - - " records.

I found a solution by adding a " ' " in front of all of the records in
offending columns in excel, but the process is unbelievably slow since
I have ~70 columns with ~50,000 rows.

Is there a way to force Access to internally declare the problem
columns as 'date/time' type so that it will reject the " - - "
records instead of the date's?

 

[pietlinden]

what happens if you link to the table and try to sort on the date
field(s). Do they sort as if they were text or do they work correctly?

what happens if you use an import specification and use
transferspreadsheet?

 

[Keith Hutchison]

Hello,

I am having trouble importing data from an Excel Worksheet. When I try
to import the data, everything works except certain columns that
contain dates, where it replaces the date with a null in the access
table. To import the data I do the following.

1. Create a table based on the excel spreadsheet setup with the
following query
sSQL = "INSERT INTO " & TblName & " SELECT * " _
& "FROM [Excel 8.0;HDR=Yes;Database="&ExcelFile&"].["&ExcelRange&"] "
& "WHERE False;"

I normally import using VBA since this allows custom handling of each
record..
snippet

engine.date_.american = fields(Me.dateIndex)
Me.date_ = engine.date_.iso
If Me.importViaExcel Then
Me.hourMinute = fields(Me.hourMinuteIndex)
Else
Me.hourMinute = fields(Me.hourMinuteIndex) & ":00"
End If

If fields(Me.timeIndex) = "" Then
Me.time = ""
Else
parseTime = fields(Me.timeIndex)
Me.time = Right("0" & Hour(parseTime), 2) & ":" &
Right("0" & Minute(parseTime), 2) & ":" & Right("0" &
Second(parseTime), 2)
End If

Hope that helps you

Keith