Solved

Put Import Errors into one table

Posted on 2004-09-07
8
303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 66

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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 66

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

632 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