Solved

Import Excel Spreadsheet into Access via VBA without Error table

Posted on 2010-08-23
3
525 Views
Last Modified: 2013-11-27
I wrote code in VBA to import an excel spreadsheet into Access. Now because it's formatted in a weird way in excel, i'll have to clean the imported table. I get a table with the errors of the imported data. Is there a way to eliminate the error table from being created? I have no use for it

' IMPORT UNUM EXCEL SPREADSHEET AND NAME IT tblUNUMIntermittentLeaveSummaryReportTemp
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblUNUMIntermittentLeaveSummaryReportTemp", "C:\Documents and Settings\T94HGEY\Desktop\eLeave\UNUM\Intermittent_Leave_Summary_Report(1).xls"
0
Comment
Question by:flyguy80
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33505241
flyguy80,To avoid the errors, you should load first into a "staging" table in which all of the columns use the text data type.  You can then use queries and/or code to perform your data clean up and move the validated data to your permanent table(s).Patrick
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 500 total points
ID: 33506928
What I have found is that using the transfer spreadsheet method to import excel data into a new access table will cause the new table to be created with the field types matching the first row of data that in in the spreadsheet.
So say for arguments sake cell A1 in your spreadsheet is a date, then the first field in the new table will be set as a date field.
Then If Cell A2 in your spreadsheet is text, a data type conversion error will occur because access is trying to insert text into a field it has determined should be a date based on the first row of data and an import errors table will be created.
To avoid this you can
A: Create a blank table in Access with the appropriate data types for the data that is to be imported and import to that table
Or B: Ensure that the first row of data in your spreadsheet is of a type that can accept all the data in that column.
For example
If Cell B1 contains 1234 then that will be determined to be numeric and any text in column B below row 1 will generate an error
However if Cell B1 contains ABC then the corresponding field in access will be set as Text Data type into which 1234 can also exist without error.
Hope this helps
Leigh
 
0
 
LVL 33

Expert Comment

by:Norie
ID: 33512055
Wouldn't/couldn't the error table actually help you?

Either with cleaning things up or identifying if there is a problem with the import.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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