How to debug/pinpoint error in DoCmd.TransferText?

[techcrium]

I use a piece of code to transfer Excel files into my MS Access database. I use the DoCmd.TransferText method to do it.

Sometimes, I get errors which I automatically get notified. However, it is a big excel file with alot of rows and columns. How do I pin point exactly which column or which row causes the error?

All I know so far is catch the error with Err.Desc or Err.Info or something like that but that doesn't tell me anything useful except "blahblah should be an integer but instead it is a text."

 

[mdlueck]

I know of no way to get further details from that Access API as to what row of data failed the export.

I would suggest searching your Integer (numeric) columns for non-numeric data... in the database table. Perhaps you have a text field mapping to an Integer spreadsheet column.

 

[Rx_]

Michael is correct, there is no details in the API itself.

Are you finding actual text where numeric is expected?
My process is to always "scrub" the Excel data before importing it.
There are other methods to import Excel into Access besides the TransferText.
It requires more code and planning.
If it is something that is not a one-time process, it might be worth looking into.
It involves opening up an Excel worksheet with objects, evaluating the data area and navigating a row/column at a time - validating the data type - then moving it to Access Table. This would allow a pinpoint of the issue.

 

[techcrium]

ok thanks.

I managed to find the error, but I was basically doing brute force:

I took my excel data, deleted half, tried to import it. If that didn't throw at error, I would take the half of the other half, and so on.

Apparently the error was that a number did not match table key constraints

e.g. Product ID is supposed to range from 0 - 80 but in my large excel file, a number 82 slipped inside. (perhaps it was an accidental error).

So, there is no other way of pin pointing the error other than changing my code or using brute force?

 

[Rx_]

It is not tough for you to create code that goes down the Excel Workbook rows and map the columns in Excel to the columns in your table.

This code example was one of those "oh by the way" task. It won't work for your exact situation, but can give you the idea of how much code is involved.
Some code not shown was designed to worm down a network directory folder and locate several thousand Excel Templates, validate them and import the data.

This code looks for a couple of key words in the Excel template to verify it is in fact part of the Excel template. Line 130
This code is shortened to only include the Flare table. But, the code looks into Excel to determine what template it has opened - then make a decision what table to open and import the data into.
At 240 it locates the Year (each worksheet was for a different year) and puts it in memory to use in the table later.

In the next module the Flare table is opened up. Line 70 to 100 is a static reading of the Limits (header info that is used for each detailed row).
Then line 120 to 160 are in a For X Loop (based on the last record in Excel found) Look at how it just iterates through the columns in Excel row by row and maps it over to a variable.
Line 170 to 290 (addnew) simply map the variable to the recordset field -then complete the transaction.

Sub HarvestFlare was just a prototype to pre-test the code is used in the module above.

The last function Function InsertIntoTableReturnAutoCount was just for my Quality Assurance - keeps track of the thousands of worksheets for a massive audit (that we passed two days after I imported all the data and built an application around it - WHEW! LOL).

In your case: If the data import activity is once a month, maybe the manual process is just fine.
This code was for a one time shot - for a metric ton of Excel Workbooks in different network folders that had 1 to many worksheets each.

Here is the conceptual code so you can make an assessment if it is something you want to try or not.

Answer to your question: THE API assumes data is scrubbed and matches your Table. You could use the API to import the data into a Temp-table with no restraints - then clean up the data in the temp table before appending it to the final table.
Or, you can modify some code such as the code shown below:

Good luck and best regards

Option Explicit
Option Compare Database
' //////////////   Used in Form ////////////////
' Suggestion - open the Immediate Window to see Debug.Print statements output
Public Sub ProcessExcelFile(sFile As String, sFileName As String)
      'sFile is the full path of the file, sFileName is just the filename
      Dim Objxl As Excel.Application
      Dim objWkb As Excel.Workbook
      Dim objSht As Excel.Worksheet
      Dim sA3Val As String
      Dim fieldArray() As String
      Dim SQLString As String
      Dim WorkSheetLastRow As Long
      Dim LastRowonColumn2 As Long
      Dim LastRowAutocount As Long
      Dim XLWellName As String ' cell A2
      Dim XLFormType As String ' cell A4
      Dim XLSheet1Year As String    ' cell A14 on flare
      Dim XLWorksheetCount As Integer
      Dim isFlare As Boolean
10    isFlare = False
20    On Error Resume Next
30    If Err.Number = 0 Then  'Excel was not open
40        Err.Clear
50        Set Objxl = New Excel.Application
60    End If
70    Objxl.Visible = False
80    Set objWkb = Objxl.Workbooks.Open(sFileName)
90        objWkb.Sheets(1).Activate
100       XLWorksheetCount = objWkb.Sheets.Count
110       LastRowonColumn2 = objWkb.ActiveSheet.Cells(objWkb.ActiveSheet.Rows.Count, 2).End(xlUp).Row
          'WorkSheetLastRow = LastRowonColumn2()  Flair is Max 44 where Emission is max 38
          ' Title is in B4 for both types
120       DoEvents
          
130               If LastRowonColumn2 > 46 Then  ' This  excel template never goes past 46
                      ' skip - junk excel sheets Check to see if the word "Monthy" is there befor starting
140               Else
                          ' Basic sheet information
150                       XLWellName = objWkb.ActiveSheet.Range("B2")
160                       XLFormType = objWkb.ActiveSheet.Range("B4")
170                           If Left(XLFormType, 7) <> "Monthly" Then
180                                           Objxl.Quit
190                                           Set objSht = Nothing
200                                           Set objWkb = Nothing
210                                           Set Objxl = Nothing
220                                       Exit Sub
                                          ' all the other non-report XL were missing this
230                           End If
                          
240                       If objWkb.ActiveSheet.Range("B12") = "Year" Then ' is this the template where B12 has the word "Year"  ??
250                           XLSheet1Year = objWkb.ActiveSheet.Range("B14")  ' does the word Year appear here?
260                           isFlare = True  ' decision on what table gets what data harvested
270                       Else
280                           XLSheet1Year = objWkb.ActiveSheet.Range("B10")
                              ' Emissions
290                       End If
                      
300                       DoEvents
310                       LastRowAutocount = InsertIntoTableReturnAutoCount("ExcelInventory", Trim(sFile), sFileName, LastRowonColumn2, XLWellName, XLFormType, XLSheet1Year, XLWorksheetCount)
                          ' Flare or Tank - some templates are Flare, the rest Tank - take data to proper table - Return Autocount number
320                       If isFlare Then
330                           FlareTable Objxl, LastRowAutocount
340                       Else
                              
350                       End If
360               End If
370               Objxl.Quit
380               Set objSht = Nothing
390               Set objWkb = Nothing
400               Set Objxl = Nothing
410               SQLString = ""
420   Exit Sub

End Sub

Public Sub FlareTable(Objxl As Excel.Application, ID_workbook As Long)      ' Flare
      Dim rst As DAO.Recordset, lngANumber As Long
      Dim WorkSheetCount As Integer
      Dim X As Integer
      Dim Y As Integer
      Dim Row As Integer
      Dim CalMonth As String
      Dim Throughput As Integer
      Dim NOx As Integer
      Dim CO As Integer
      Dim VOC As Integer
      Dim CalYear As Integer
      Dim BTURating As Integer
      Dim MolWeight As Double
      Dim VOCWeight As Double

10        WorkSheetCount = Objxl.Sheets.Count
          
20        For X = WorkSheetCount To 1 Step -1
30                Objxl.Sheets(X).Activate
40                Set rst = CurrentDb.OpenRecordset("Flare", dbOpenDynaset) ' defaults to dBopen table since it is local table
                  'InsertIntoTableReturnAutoCount = -99 ' use as error trap return value
50                Debug.Print "Add record for excel file name Flare " & ID_workbook - Foreign; Key
                          ' Read Flare Excel Worksheet
60                            Objxl.Range("A1").Select ' Starting point
                                'Debug.Print ActiveCell.Value & "  Start location "
70                            CalYear = Objxl.ActiveCell.Offset(13, 1).Range("A1").Value
80                            BTURating = Objxl.ActiveCell.Offset(13, 5).Range("A1").Value
90                            MolWeight = Objxl.ActiveCell.Offset(13, 7).Range("A1").Value
100                           VOCWeight = Objxl.ActiveCell.Offset(13, 9).Range("A1").Value
110                           For Row = 17 To 39 Step 2
120                                 CalMonth = Objxl.ActiveCell.Offset(Row, 1).Range("A1").Value
130                                 Throughput = Objxl.ActiveCell.Offset(Row, 3).Range("A1").Value
140                                 NOx = Objxl.ActiveCell.Offset(Row, 5).Range("A1").Value
150                                 CO = Objxl.ActiveCell.Offset(Row, 7).Range("A1").Value
160                                 VOC = Objxl.ActiveCell.Offset(Row, 9).Range("A1").Value
                                    ' if 2nd one is not null then
                                    'If Throughput < 1 Then Exit Sub   ' this doesn't work if records started mid year (e.g. Jan is blank)
                                    'Debug.Print CalYear; CalMonth; vbTab; Throughput; vbTab; NOx; vbTab; CO; vbTab; VOC; vbTab; Row
                                    ' Write above to a recordset
                              
                              ' Read one - then add new row
170                                   rst.AddNew
180                                       rst!ID_workbook = ID_workbook
190                                       rst!Workbooksheet = X
200                                       rst![CalYear] = CalYear ' NEED TO GET THIS
210                                       rst![CalMonth] = CalMonth          ' Jan, Feb, Mar
220                                       rst![Throughput] = Throughput
                                          'rst![Yearfield] = CalYear 'Yearfield          ' cell B4
230                                       rst![NOx] = NOx
240                                       rst![CO] = CO
250                                       rst![VOC] = VOC
260                                       rst![BTURating] = BTURating
270                                       rst![MolWeight] = MolWeight
280                                       rst![VOCWeight] = VOCWeight
                  
290                                   rst.Update
300                           Next Row
310        Next X
           
320               Debug.Print lngANumber & "   " & Err.Number
330               rst.Close
340               Set rst = Nothing
          
350   Exit Sub
ErrorTrap:
360   Debug.Print Err.Number & " " & Err.Description
End Sub

Sub HarvestFlare(Objxl As Excel.Application)
      Dim Row As Integer
      Dim CalMonth As String
      Dim Throughput As Integer
      Dim NOx As Integer
      Dim CO As Integer
      Dim VOC As Integer
10        Range("A1").Select ' Starting point
20        Debug.Print ActiveCell.Value & "  Start location "
30      For Row = 17 To 39 Step 2
40            CalMonth = ActiveCell.Offset(Row, 1).Range("A1").Value
50            Throughput = ActiveCell.Offset(Row, 3).Range("A1").Value
60            NOx = ActiveCell.Offset(Row, 5).Range("A1").Value
70            CO = ActiveCell.Offset(Row, 7).Range("A1").Value
80            VOC = ActiveCell.Offset(Row, 9).Range("A1").Value
              ' if 2nd one is not null then
90            If Throughput < 1 Then Exit Sub
100           Debug.Print CalMonth; vbTab; Throughput; vbTab; NOx; vbTab; CO; vbTab; VOC; vbTab; Row
              ' Write above to a recordset
110     Next Row

120   Exit Sub

End Sub

Public Function InsertIntoTableReturnAutoCount(LocalTableName As String, _
                                                OpenFilePath As String, _
                                                ExcelFileName As String, _
                                                LastRow As Long, _
                                                WellName As String, _
                                                FormType As String, _
                                                Yearfield As String, _
                                                WorksheetTotalCount As Integer) _
                                                As Long
      ' Builds Master Table - returns autocounter number for two tables (Tank and Flare)
      Dim rst As DAO.Recordset, lngANumber As Long
10    Set rst = CurrentDb.OpenRecordset("ExcelInventory") ' defaults to dBopen table since it is local table
20    InsertIntoTableReturnAutoCount = -99 ' use as error trap return value
30    Debug.Print "Add record for excel file name " & ExcelFileName
40    rst.AddNew
50        rst!FilePath = OpenFilePath
60        rst!ExcelFileName = ExcelFileName
70        rst![Worksheet Count] = WorksheetTotalCount ' CInt(objXL.Sheets.Count)
80        rst![WellName] = WellName           ' cell B2
90        rst![FormType] = FormType           ' cell B4
100       rst![Yearfield] = Yearfield          ' cell B4
110       rst![MaxRowNumber] = LastRow          ' Sheet 1 max row number should pre filter bigger than 44 rows
120   rst.Update
130   rst.Bookmark = rst.LastModified
140   lngANumber = rst!ID_workbook                ' return autocounter number
150   Debug.Print lngANumber & "   " & Err.Number
160   rst.Close
170   Set rst = Nothing
180   InsertIntoTableReturnAutoCount = lngANumber
190   Exit Function

End Function

 

[gemma-the-husky]

one way is to assume every column is text, and design a capture table to receive the data

import the raw data into the receiving table

then validate the data according to your rules.

---
alternatively, import the table normally - then test the field types that have been imported.


problems are genrerally caused when data is of the wrong type for the field - so you are probably looking for unexpected nulls

lack of control of data is a real problem of using spreadsheets