Error Importing Excel data into Access

[Carlit007]

Hi I am totally new to Ms access and apparently I have been using the wrong application(Excel) to build my databases lol better late then never
so far I have learned the basics of creating both tables and form but am having issues when importing new data.

I am trying to import new data from an excel report to an existing table called tblMyProperty the issue I am having when using the import wizard is that the headers in the excel report contains a “.” At the end which I guess is a no no for access tables

unfortunately the report that I have to import frequently has headers such as;

Admin no.
serial No.

note: I have created all my fields in my access tables to match the data that gets imported from excel what I'm trying to avoid is having to manually remove illegal characters from the excel heathers file every time.

I would like to know if by using vba I could make a button in a form that lets me import data with a file open dialog that lets me choose the file to import and to what table to import to.
and upon import it clears any ilegal character from the excel headers so that it gets properly imported into the right field

thanks in advance

 

[alansidman]

Instead of importing your Excel Data, I suggest you link your excel table to Access.

 

[Carlit007]

Instead of importing your Excel Data, I suggest you link your excel table to Access.

Hi just curious how is this a better solution? I don't know if it helps but what I am trying to do is build database for equipment that I constantly have to conduct inventory of.
the excel files that I have to constantly import (once a week) may or may not have new equipment added to it everything is tracked by serial number (key)

I just want to hit a button have access prompt to fileopen a new excel report and analyze weather there's new equipment to add to my db based on serial number
this prevents me from having to manually enter a large quantity of new equipment one at a time into access since Im getting the data straight from the source which happens to export excel reports

Hope that explanation helps

 

[Joe4]

Importing data directly from Excel has always been a little tricky. One of the issues is Excel and Access "talk to each other", and try to figure out things for you, instead of letting you do it, and they sometimes "guess" wrong (especially when it comes to data type). I believe it looks at the first 10 rows of data to "guess" what the data type is. So sometimes, it picks the wrong things.

What I have often done in the past is export my Excel file to a text file and import that, where I can have full control over all the fields and data tyes.

However, if you have an Excel file that is constantly being updated, you can use the method that Alan described (linking the Excel file). Then any changes to the Excel file are automatically updated in Excel (since all the data is linked). If for some reason you need to keep the data separate (maybe once it is in Access, you may make edits to it that are not reflected on the Excel file), what you can do is link the Excel file to Access in a "temporary" table, and then do an Unmatched Query from the "temporary" table to your final table in Access, to locate all the new records. Then use an Append Query to add those new records to your final Access table (the "Unmatched" and "Append" Query can be one and the same).

On your final table, I would also recommend that you set primary key fields, which will prevent you from overwriting existing records in your final table.

 

[Micron]

@Carlit007: If you link, Access will probably replace period with #. That is what I'd do. To make the suggested data table, use the linked sheet in a make table query and you'll get your table quickly. Then close the mt query (if it worked, you don't even need to save it). Then open the new table in design and fix the data types and names if required. I would describe the method of preventing records as making sure you have unique indexes, not primary fields. While 2 or more fields can be used to make a composite primary key, it's better to have either one or more indexes or a composite index as well as one PK field in your data table.

Another way to get data into the table is to simply run an append query, and any attempt to append records that violate data integrity will be rejected. However, you need those unique indexes to prevent that. For instance, if the append contains work order numbers (WO) and your unique index is on that field, you will not append the same number again. You will get a message about the failure of the query to perform as Access thinks you intended. I mention this because it's easier than creating outer joins, not that it's a better way. Further to the the given example of WO index, if your index comprises WO and TaskNo fields, then you can append the same work order several times, but only where the combination of WO and task number to be appended does not already exist in your data table.

In your new table, there should be no spaces or special characters in any object name in Access (save for underscore, which I almost never use either). If you are not familiar with proper naming convention for Access, I suggest you research it. I'll also suggest that you research db normalization because those who switch from Excel to Access almost always start off with poorly designed tables and struggle thereafter. These apps look similar but you would do well to not use them they same way.

 

[Carlit007]

@Carlit007: If you link, Access will probably replace period with #. That is what I'd do. To make the suggested data table, use the linked sheet in a make table query and you'll get your table quickly. Then close the mt query (if it worked, you don't even need to save it). Then open the new table in design and fix the data types and names if required. I would describe the method of preventing records as making sure you have unique indexes, not primary fields. While 2 or more fields can be used to make a composite primary key, it's better to have either one or more indexes or a composite index as well as one PK field in your data table.

Another way to get data into the table is to simply run an append query, and any attempt to append records that violate data integrity will be rejected. However, you need those unique indexes to prevent that. For instance, if the append contains work order numbers (WO) and your unique index is on that field, you will not append the same number again. You will get a message about the failure of the query to perform as Access thinks you intended. I mention this because it's easier than creating outer joins, not that it's a better way. Further to the the given example of WO index, if your index comprises WO and TaskNo fields, then you can append the same work order several times, but only where the combination of WO and task number to be appended does not already exist in your data table.

In your new table, there should be no spaces or special characters in any object name in Access (save for underscore, which I almost never use either). If you are not familiar with proper naming convention for Access, I suggest you research it. I'll also suggest that you research db normalization because those who switch from Excel to Access almost always start off with poorly designed tables and struggle thereafter. These apps look similar but you would do well to not use them they same way.

@Micron thanks you for the advice I understand the advantages of linking instead of importing a little e better now I did spend all night researching about importing with query by using append I was able to get it to work when importing new records I need to work on getting all the fields besides the keys to populate into the existing data not sure If the headers have to always match or if I can reroute in the append to: field under query

I will definitely go and spend some time learning Db normalization because my way of thinking is still stuck on excel for the most part

 

[james_lankford]

I would like to know if by using vba I could make a button in a form that lets me import data with a file open dialog that lets me choose the file to import and to what table to import to.
and upon import it clears any ilegal character from the excel headers so that it gets properly imported into the right field

yes, just write an access script that copies the excel file. then edits the copy so that the column headers are more database friendly
Admin no. becomes admin_number and so on
and then import the copy
then delete the copy

 

[Micron]

Then there is automation. For that, I go to here

EXCEL Import And Export

 

[Carlit007]

Then there is automation. For that, I go to here

EXCEL Import And Export

@ Micron thanks for providing that link!
its like a treasure chest full of information for the functionalities I need for by DB many Thanks

 

[Micron]

U R welcome.