Importing and Field Mapping (excel to access)

[dunlop407703]

Hi all,

I am currently in the process of importing a large back catalog of excel data into access so it can be managed better. The problem I have is that the excel field names do not match the access table field names, and more so the excel field names vary with nearly every excel sheet (very poor data control previously).

I have searched around and apparently there is no native way in access to map fields on import, if there is please tell me but I haven't been able to find a way.

What I have come across is a suggestion to import the data into a temporary new table, then use an append query to move the data into the correct table, followed by deleting the temporary new table. In theory this process makes sense to me however how to put it into practice is beyond me. Also I'm not sure but if this was the process would I have to re-write the append query to match the different field headers coming in every time, or can I map as I go with an append query?

What I envisage is a button on my main form to import data. When I hit it I get a select file dialog box, I select my file and it imports into a new table, the append querry automatically runs and appends the data to the write table, then another function runs to delete the temporary 'go between' table.

I have no idea how much / if any of this is possible, nut any feedback or discussion would be appreciated.

Cheers

 

[RCBricker]

Hi all,

I am currently in the process of importing a large back catalog of excel data into access so it can be managed better. The problem I have is that the excel field names do not match the access table field names, and more so the excel field names vary with nearly every excel sheet (very poor data control previously).

I have searched around and apparently there is no native way in access to map fields on import, if there is please tell me but I haven't been able to find a way.

What I have come across is a suggestion to import the data into a temporary new table, then use an append query to move the data into the correct table, followed by deleting the temporary new table. In theory this process makes sense to me however how to put it into practice is beyond me. Also I'm not sure but if this was the process would I have to re-write the append query to match the different field headers coming in every time, or can I map as I go with an append query?

What I envisage is a button on my main form to import data. When I hit it I get a select file dialog box, I select my file and it imports into a new table, the append querry automatically runs and appends the data to the write table, then another function runs to delete the temporary 'go between' table.

I have no idea how much / if any of this is possible, nut any feedback or discussion would be appreciated.

Cheers

Well you beat me to this weeks Mr Excel post.

I am having the same issue. I have a table that I created with the first import of a report from my client. The issue is that the # of fields change every month (growing and shrinking ::SMH) as well as new field names appearing at random months and old ones coming and going. Unfortunately, the client knows and is not doing anything to standardize the uploads.

My question, which might be what the OP is asking or might help them is:

Is there a way to upload the file to a temp table and then have vba match fields with the same name, and ADD fields that do not exist?

IF this solution is not along the OPs possible solutions, then I will gladly make a new thread. I do not want to hijack this thread. I posted as I thought my question and the OPs were similar enough that either question being answered would probably solve both issues.

 

[xenou]

I would prefer to change column names in Excel as that is much simpler than changing field names in Access. Then import. Either way, it really depends on how much variability you are talking about - this might be easy or very hard.

 

[dunlop407703]

I would prefer to change column names in Excel as that is much simpler than changing field names in Access. Then import. Either way, it really depends on how much variability you are talking about - this might be easy or very hard.

Thanks for the reply xenou, I've just bitten the bullet and made a few field name changes to my database and then started to manually go through the excel files and update.

 

[RCBricker]

Looks like OP as solved his issue, so instead of hi-jacking this thread I will start another.