Обработка ошибки #Num! Ошибка при импорте Excel в Access

Я'импортирую электронную таблицу из Excel 2003 в Access 2003 с помощью ADO (Provider = "Microsoft.Jet.OLEDB.4.0"). Однако у меня есть несколько текстовых записей в поле даты. Они передаются как значения #Num!, как и следовало ожидать.

Что я хотел бы сделать, так это обернуть поля с ошибками в нечто вроде IIf(IsDate(FIELDNAME),FIELDNAME,#1/1/1900#). Однако это все равно приводит к результату #Num!. Также как и проверка IsDate(), IsDate(CVar()), IsError(), IsError(CDat()), и VBAFunctionThatReturns0GivenAnyValue(FIELDNAME).

У кого-нибудь есть идеи, какую функцию-обертку я могу использовать, которая выполнит эту работу? Я более или менее в своем уме?

Обратите внимание: у меня нет никакого контроля над импортированными данными, поэтому я не могу исправить таблицу, как бы мне этого не хотелось. Я также хотел бы избежать специфического хакинга в самой функции импорта (например, использовать TransferSpreadsheet вместо нее). Цель состоит в том, чтобы использовать тот же подход к вводу данных, просто игнорировать те части, которые не работают.

Также следует отметить, что здесь уместно отбрасывать значения без даты. Импорт в виде текста - это то, чего я хотел бы избежать, поскольку это вызовет множество других ошибок в дальнейшем.

EDIT: Добавил условие, 2003. Я также должен отметить, что версия .Net framework, установленная в среде, не включает (или, похоже, не включает) провайдера ACE.12.0.

Edit 2: Как и просили, вот (слегка очищенная) версия SQL, который я выполняю. Обратите внимание, что это не точный запрос, но его выполнение на той же таблице вызывает те же проблемы:

SELECT 
    CStr(Nz([Notes], "")), 
    [ID],
    [Date]
INTO
    [;DATABASE=C:\DatabaseTarget.Mdb].[tblImport]
FROM
    [Excel 8.0;Database=C:\ExcelSheet.xls].[sheet1$]
WHERE
    [ID] IS NOT NULL

Проблему можно увидеть, убрав предложение INTO и посмотрев на значения для [Date]. Если любое из них равно #Num! Тогда даже выполнение следующих действий приводит к `#Num!`` для всех Test1-5, что скорее демонстрирует проблему..:

SELECT 
    CStr(Nz([Notes], '')), 
    [ID],
    CStr(Nz([Date],'')) As [Test1],
    IsError([Date]) As [Test2],
    IsDate([Date]) As [Test3],
    IsNull([Date]) As [Test4],
    IIf(True, 1, [Date]) As [Test5],
FROM
    [Excel 8.0;Database=C:\ExcelSheet.xls].[sheet1$]
WHERE
    [ID] IS NOT NULL
tobriand
Редактировал вопрос 14-го мая 2013 в 4:35
Программирование
ms-access excel ms-access-2003 ado
13-го мая 2013 в 1:54
30 просмотров

Проверка на IsNull(), похоже, должна работать. Для данных Excel...

ID  ActivityDescription ActivityDate
1   activity_1          2013-05-12
2   activity_2          2013-05-13
3   activity_3          2013-05-14
4   activity_4          2013-05-15
5   activity_5          2013-05-16
6   activity_6          2013-05-17
7   activity_7          2013-05-18
8   activity_8          2013-05-19
9   activity_9          2013-05-20
10  activity_10         2013-05-21
11  activity_11         2013-05-22
12  activity_12         2013-05-23
13  activity_13         oops!
14  activity_14         2013-05-25
15  activity_15         2013-05-26

...следующий код Access VBA...

Sub adoTest()
Dim con As ADODB.Connection, rst As ADODB.Recordset
Set con = New ADODB.Connection
con.Open _
        "Provider=Microsoft.JET.OLEDB.4.0;" & _
        "Data Source=C:\Users\Public\xlsTest.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=YES"";"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM [Sheet1$]", con
Do While Not rst.EOF
    Debug.Print _
            Format(rst("ID").Value, "00") & _
            "  " & _
            IIf(IsNull(rst("ActivityDate").Value), "", rst("ActivityDate").Value)
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
End Sub

...выдает следующее:

01  2013-05-12
02  2013-05-13
03  2013-05-14
04  2013-05-15
05  2013-05-16
06  2013-05-17
07  2013-05-18
08  2013-05-19
09  2013-05-20
10  2013-05-21
11  2013-05-22
12  2013-05-23
13  
14  2013-05-25
15  2013-05-26

Это показывает, что IsNull() распознает "плохое" значение даты как Null и подставляет вместо него текст ``.

В зависимости от контекста, функция Nz() также может оказаться полезной.

Gord Thompson
Редактировал ответ 13-го мая 2013 в 2:54
Комментарии (5)

Похоже, что ошибки #Num! возникают в провайдере до того, как они попадают в большинство подходов к ошибкам, которые можно использовать для обработки ошибок в запросах. Использование ADO, похоже, чревато неудачей.

Однако тестирование моего запроса в DAO (без пункта INTO, чтобы просто создать набор записей) дало корректные данные, поэтому я решил, что стоит посмотреть, может ли DAO справиться с запросом к источнику записей OLEDB. Оказалось, что может!

Поэтому окончательный код выглядит примерно так

On Error Resume Next
cmdFirstTry.Execute '' Includes fields which are pulled through as #Num! errors
Select Case Err.Number
    Case 0
        On Error Goto ErrorHandler
    Case &H80040E14
        '' Failed in this manner; Try with DAO
        On Error Goto ErrorHandler
        CurrentDb.Execute strSQL
    Case Else
        lngErrNum = Err.Number
        strErrDesc = Err.Description
        On Error Goto ErrorHandler
        Err.Raise lngErrNum, , strErrDesc
End Select

Для моих нужд это подходит, но я не буду принимать это как ответ в течение нескольких дней, на случай, если действительно существует элегантный подход.

Комментарии (2)