Imprort Error Table with Import from Excel

[4110]

Hi,
I import about 20 Excel spreadsheets into Access each day. The spreadsheets
are created elsewhere and submitted so I don't control them. The spreadsheet
names change but I have a table to manage that and the import process is
automated with code. Occasionally there is something in the data that causes
an import error table to be created. When I trace this back I find that it
is in a portion of the spreadsheet that doesn't supply actual data for
import. For example, I may be reading from the first and third column and
the error will be in the second column. Often the error is associated with
something that Excel has flagged. For example, today a submittal had '0765
in a cell. This was an Appropriation code and the leading zero was part of
the code. The submitter had entered the leading single quote so Excel
wouldn't delete the leading zero. Excel flagged it as number treated as text
and Access created an input error table for it.

Is there a way to have Access not create Error tables?

Thanks

 

[John Nurick]

If you're not interested in the Import Error tables, the simplest thing
is just to delete them.

BTW, which technique are you using to read from the first and third
columns but not the second?

 

[4110]

Hi John,

Thanks for your help. I have been deleting the import error tables but
there may be a dozen or more a day and it gets tedious.

Your second question was very perceptive and sent me on a useful search. I
am using information from the first and third column but I am reading the
entire spreadsheet (including the second column) with "DoCmd
TransferSpreadsheet acImport ..." . I have been reading some other answers
here and think that an approach that might sidestep the import errors might
be to link to the spreadsheet rather than import, ie "DoCmd
TransferSpreadsheet acLink ..."

The suggestions I read say
1 link
2 append the data
3 delete the link.

How do I delete the link?

Thanks,

David

 

[John Nurick]

You can always use a little VBA procedure to delete the ImportError
tables, e.g.

Public Sub DeleteImportErrorTables()
Dim j As Long

DoCmd.SetWarnings False
With CurrentDb.TableDefs
For j = .Count - 1 To 0 Step -1
If .Item(j).Name Like "*ImportErrors" Then
DoCmd.DeleteObject acTable, .Item(j).Name
End If
Next
End With
DoCmd.SetWarnings True
End Sub


If you link by using DoCmd.TransferSpreadsheet acLink, you're creating a
linked table and the only way to get rid of that is to delete it. You're
also likely to get a different crop of issues stemming from what I
suspect is the underlying problem, namely having a mix of text and
number values in the same column in Excel.

One way of minimising that problem is to import (not link) into an
existing table, whose field names match those in Excel and whose data
types are the ones you need. If you have a text field in Access, it's
less likely to be thrown by a mixture of text and number values coming
from Excel. If you link, or import to a new table, Access/Jet tries, to
assign field types and doesn't always get it right.

If you only want to import some columns, you can use a append query (in
SQL) that gets the data direct from Excel and appends it to your real
table. The syntax is along these lines:

INSERT INTO MyTable (XXX, YYY, ZZZ)
SELECT F1, F3, F5
FROM [Excel 8.0;HDR=NO;Database=C:\Folder\File.xls].[Sheet1$];

(XXX etc. are field names in Access; F1 gets column A, F3 gets column C
and so on.)

INSERT INTO MyTable
SELECT XXX, YYY, ZZZ
FROM [Excel 8.0;HDR=YES;Database=C:\Folder\File.xls].[Sheet1$];

(XXX etc. are field names in Access and the corresponding column
headings in Excel.)

.... [Excel 8.0;HDR=NODatabase=C:\Folder\File.xls].[Sheet1$A3:E99];
(specify range of cells)

.... [Excel 8.0;HDR=NODatabase=C:\Folder\File.xls].[MyRange];
(named range in Excel)

 

[4110]

Thanks John,

I followed your lead and just imported with no ImportError tables created.
And no, I didn't need the VBA routine to delete them. It was a clean import.

Thanks again,

David

You can always use a little VBA procedure to delete the ImportError
tables, e.g.

Public Sub DeleteImportErrorTables()
Dim j As Long

DoCmd.SetWarnings False
With CurrentDb.TableDefs
For j = .Count - 1 To 0 Step -1
If .Item(j).Name Like "*ImportErrors" Then
DoCmd.DeleteObject acTable, .Item(j).Name
End If
Next
End With
DoCmd.SetWarnings True
End Sub


If you link by using DoCmd.TransferSpreadsheet acLink, you're creating a
linked table and the only way to get rid of that is to delete it. You're
also likely to get a different crop of issues stemming from what I
suspect is the underlying problem, namely having a mix of text and
number values in the same column in Excel.

One way of minimising that problem is to import (not link) into an
existing table, whose field names match those in Excel and whose data
types are the ones you need. If you have a text field in Access, it's
less likely to be thrown by a mixture of text and number values coming
from Excel. If you link, or import to a new table, Access/Jet tries, to
assign field types and doesn't always get it right.

If you only want to import some columns, you can use a append query (in
SQL) that gets the data direct from Excel and appends it to your real
table. The syntax is along these lines:

INSERT INTO MyTable (XXX, YYY, ZZZ)
SELECT F1, F3, F5
FROM [Excel 8.0;HDR=NO;Database=C:\Folder\File.xls].[Sheet1$];

(XXX etc. are field names in Access; F1 gets column A, F3 gets column C
and so on.)

INSERT INTO MyTable
SELECT XXX, YYY, ZZZ
FROM [Excel 8.0;HDR=YES;Database=C:\Folder\File.xls].[Sheet1$];

(XXX etc. are field names in Access and the corresponding column
headings in Excel.)

.... [Excel 8.0;HDR=NODatabase=C:\Folder\File.xls].[Sheet1$A3:E99];
(specify range of cells)

.... [Excel 8.0;HDR=NODatabase=C:\Folder\File.xls].[MyRange];
(named range in Excel)

Hi John,

Thanks for your help. I have been deleting the import error tables but
there may be a dozen or more a day and it gets tedious.

Your second question was very perceptive and sent me on a useful search. I
am using information from the first and third column but I am reading the
entire spreadsheet (including the second column) with "DoCmd
TransferSpreadsheet acImport ..." . I have been reading some other answers
here and think that an approach that might sidestep the import errors might
be to link to the spreadsheet rather than import, ie "DoCmd
TransferSpreadsheet acLink ..."

The suggestions I read say
1 link
2 append the data
3 delete the link.

How do I delete the link?

Thanks,

David