dunlop407703
New Member
- Joined
- Oct 8, 2014
- Messages
- 24
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
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