Solved

Put Import Errors into one table

Posted on 2004-09-07
8
297 Views
Last Modified: 2008-02-01
I have a small VBA macro which imports data from over 350 text files into a table.  It seems to work fine, except that almost all the text files have at least one error in them, so I end up with over 300 tables of Import Errors.  What's the easiest way to make all those import errors go into one table?  I'll need to add a field to that table and populate it with the file that caused the error.
0
Comment
Question by:DanR
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 11999597
This behavior is by design, you can't change the name of the table that Import Errors go to.

What you can do, however, is use temporary tables (I prefix these with timp_), that have all of your fields as Text, and import all your tables into them.  Then you can run whatever queries/code you like to 'clean up' the data before appending them to the final destination table.

Hope this helps.
-Jim
0
 
LVL 2

Expert Comment

by:PendragonZero
ID: 12000178
Not sure how comfortable with code you are.
But after each import, I'd just check to see if an error occured and append them to an error table with the file name.

Let me know your setup, and I can write it for you.
Pen
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
ID: 12002133
on command button click

Private Sub Command1_Click()
Dim tbl As TableDef, db As Database, aSQL As String
Set db = CurrentDb

For Each tbl In db.TableDefs
    If tbl.Name Like ("*" & "ImportErrors") Then
    aSQL = "Insert Into tblErrors Select " & tbl.Name & ".* From " & tbl.Name & ""
    db.Execute (aSQL), dbFailOnError

   'delete import errors table
   DoCmd.DeleteObject acTable,  tbl.Name
    Else
   
    End If
    Next tbl

End Sub

this will copy all the importerrors to tblErrors


0
 
LVL 3

Author Comment

by:DanR
ID: 12002313
Thanks for the responses so far, but I don't think I was clear on the problem.  

I am only slightly interested in the content  of the Import Errors tables; it will be basically the same every import, and the errors are generally not critical.  Still, it would be nice to have the errors in a useful form.

But the real problem is that every time I import, I get 300 more useless tables.  What I want is to have the errors go only into a single table, not generate a separate table for each text file.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12002587
(AFAIK), you cannot avoid this, if there is an error Access generates the table for errors.

Here are the most typical reasons for encountering errors:

In a field, there is data that can't be stored in the data type that Microsoft Access has assigned to that field. For example, you might have accidentally included a text value in a field that should only contain numbers or dates. Or perhaps a row in your text file or spreadsheet contains summary information or extraneous characters.
If you think Microsoft Access assigned the correct data type for this field, edit your text file or spreadsheet to correct errors, and then import again. Otherwise, import again and specify the appropriate data type.

Microsoft Access has assigned an incorrect data type to a field. Microsoft Access assigns the data type for each field based on the data in the first row it imports. For example, if a field that contains mostly text values has a number in the first row, then, in an Access database, Access assigns the Number data type. In an Access project, Access assigns a data type for numeric data. In both cases, Access cannot import the rest of the records.
Import again and specify the appropriate data type when importing.

One or more rows in the text file or spreadsheet contain more fields than the first row. For example, the second row in a file might have an extra field delimiter character that is followed by a value that Microsoft Access can't fit in the new table.
Edit your text file or spreadsheet so that each row has the same number of fields, and import again.

The date fields that are imported from a Microsoft Excel spreadsheet are off by four years. Microsoft Excel for Windows uses the 1900 Date System, in which serial numbers range from 1 to 65,380, which correspond to the dates January 1, 1900 through December 31, 2078. However, Microsoft Excel for the Macintosh uses the 1904 Date System, in which serial numbers range from 0 to 63,918, which correspond to the dates January 1, 1904 through December 31, 2078.


0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12002605
What you are asking for is not possible.  Microsoft dicates that all import errors be designated in a signle format with a prefix of 'ImportErrors' and a suffix of the file name you are importing, and developers can't change that.

The best you can hope for, if your problem is defined as having 300+ useless tables, would be Capricorn's code, which at least automates the moving of all records from the 300+ tables into one table, then delete all 'ImportErrors' tables, so you in effect have one table with all errors.  From there, you can build a form on top of it for easier viewing.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12002628
http://office.microsoft.com/en-gb/assistance/HP051885461033.aspx

The second link (I get errors when I import a spreadsheet or text file.).
0
 
LVL 3

Author Comment

by:DanR
ID: 12006529
Thanks to all for the input.  I must accept that those tables will be created.  I know what causes all the errors, and I am intrigued by jimhorn's suggestion of a pre-import and cleanup (though I won't have time to look at that for a while), so I gave him half the points.

capricorn1 gave me a way to create a single table with all the errors, which will be useful in the future, so half the points to her/hem.

The rest of you have my thanks.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now