DanR
asked on
Put Import Errors into one table
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
(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.
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.
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.
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.
http://office.microsoft.com/en-gb/assistance/HP051885461033.aspx
The second link (I get errors when I import a spreadsheet or text file.).
The second link (I get errors when I import a spreadsheet or text file.).
ASKER
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.
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.
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