I have spreadsheets containing data I want to import to Access. The spreadsheets contain data item values, but I want to import them to a table based on a query, which contains only keys. How can I convert data item values to key values while importing?
data from excel (1 Viewer)
- Thread starter brocsman
- Start date
- Local time
- Today, 08:46
- Joined
- Feb 28, 2001
- Messages
- 27,175
I'm with Colin. I don't understand what you asked because of the way you asked it.
If I take the simplistic viewpoint, you do this import by first declaring the table and then identifying which fields will be indexes for table lookups, then just do the import.
However, that somehow doesn't seem consistent with the way you asked the question. So like Colin, I wonder what you really meant.
If I take the simplistic viewpoint, you do this import by first declaring the table and then identifying which fields will be indexes for table lookups, then just do the import.
However, that somehow doesn't seem consistent with the way you asked the question. So like Colin, I wonder what you really meant.
Apologies. I should have declared I’m a rank amateur.There may well be something obvious I’m missing.
I’m using a hand-me-down database with tables which have not been normalised. I’m trying to develop a replacement. So, let’s say I’m starting from scratch. I’ll make it very simple in the hope you’ll see what I’m trying to do. Let’s say I have a database with three tables, Name, Item, Location.
The Name table consists say of three entries:
1 Smith
2 Jones
3 Evans
The Item table shows:
1 Potatoes
2 Tomatoes
3 Onions
The Location table has:
1 Oldham
2 Bolton
3 Manchester
I create a query joining these called Transactions. I create a table with the same name based on the query.
Using an input form I can create a record if I sell potatoes to Smith in Oldham. The Transactions table now contains, 1,1,1.
Now I receive a spreadsheet from my salesman with a new sale. He sold tomatoes to Jones in Bolton. I want to append that transaction to my table. If I go to ‘import’, ‘external data’, and Excel, I can append to this table. What it imports are the values of the data items from the spreadsheet, in this case: Jones, Tomatoes, Bolton. What I want to append are the key equivalents: 2,2,2.
That is my question. How do I read the data from the spreadsheet and retrieve the keys for each data item, and then append those values to my table.
Hope that makes sense
I’m using a hand-me-down database with tables which have not been normalised. I’m trying to develop a replacement. So, let’s say I’m starting from scratch. I’ll make it very simple in the hope you’ll see what I’m trying to do. Let’s say I have a database with three tables, Name, Item, Location.
The Name table consists say of three entries:
1 Smith
2 Jones
3 Evans
The Item table shows:
1 Potatoes
2 Tomatoes
3 Onions
The Location table has:
1 Oldham
2 Bolton
3 Manchester
I create a query joining these called Transactions. I create a table with the same name based on the query.
Using an input form I can create a record if I sell potatoes to Smith in Oldham. The Transactions table now contains, 1,1,1.
Now I receive a spreadsheet from my salesman with a new sale. He sold tomatoes to Jones in Bolton. I want to append that transaction to my table. If I go to ‘import’, ‘external data’, and Excel, I can append to this table. What it imports are the values of the data items from the spreadsheet, in this case: Jones, Tomatoes, Bolton. What I want to append are the key equivalents: 2,2,2.
That is my question. How do I read the data from the spreadsheet and retrieve the keys for each data item, and then append those values to my table.
Hope that makes sense
- Local time
- Today, 08:46
- Joined
- Feb 28, 2001
- Messages
- 27,175
Got it, and I understand why the description was a bit off now. This is actually easy but isn't done in a single step.
You can import this by several steps, using an intermediate table that has three text fields and three numeric fields. I will assume that the names in your tables are of type ShortText and the keys for them are of type LONG (integer). I am going to make up some names for the fields, but it should be clear what I am doing here.
1. Import the spreadsheet to the Staging table (or any other name you like for it). You will import the text fields of the spreadsheet to three text fields in the table. The table layout might be done most easily if you have the three text fields in the table followed by the three numeric fields in positions 4, 5, and 6 of the table.
2. Now execute certain queries:
3. Now do the insert:
4. When done, erase the content (but NOT the structure) of the Staging table.
OK, now the bad news. You have to change a couple of names if you were giving us actual names of tables, because both Name and Item are reserved words in Access. You will have serious headaches if you don't change the names of those tables. Like, maybe instead of Name use Person and instead of Item use Product. Location is not a reserved word so that would be OK to keep as-is.
This is an example of the "divide and conquer" strategy. The big item is awfully complex but if the problem is divisible, sometimes the parts are trivially easy.
You can import this by several steps, using an intermediate table that has three text fields and three numeric fields. I will assume that the names in your tables are of type ShortText and the keys for them are of type LONG (integer). I am going to make up some names for the fields, but it should be clear what I am doing here.
1. Import the spreadsheet to the Staging table (or any other name you like for it). You will import the text fields of the spreadsheet to three text fields in the table. The table layout might be done most easily if you have the three text fields in the table followed by the three numeric fields in positions 4, 5, and 6 of the table.
2. Now execute certain queries:
Code:
UPDATE Staging INNER JOIN Name ON Staging.TextName = Name.TextName SET Staging.NameKey = Name.NameKey ;
UPDATE Staging INNER JOIN Item ON Staging.TextItem = Item.TextItem SET Staging.ItemKey = Item.ItemKey ;
UPDATE Staging INNER JOIN Location ON Staging.TextLocation = Location.TextLoction SET Staging.LocationKey = Location.LocationKey ;3. Now do the insert:
Code:
INSERT INTO Transaction (NameKey, ItemKey, LocationKey ) SELECT NameKey, ItemKey, LocationKey) FROM Staging ;4. When done, erase the content (but NOT the structure) of the Staging table.
Code:
DELETE * FROM Staging ;OK, now the bad news. You have to change a couple of names if you were giving us actual names of tables, because both Name and Item are reserved words in Access. You will have serious headaches if you don't change the names of those tables. Like, maybe instead of Name use Person and instead of Item use Product. Location is not a reserved word so that would be OK to keep as-is.
This is an example of the "divide and conquer" strategy. The big item is awfully complex but if the problem is divisible, sometimes the parts are trivially easy.
Fantastic. I can understand all of that. No problem with reserved words - I was just making something up to show what I was after. I'm a recorder for biological data records, and I get a lot of input in the form of spreadsheets (if I'm lucky), so it's important for me to be able to append rather than to put them in one at a time. I'll be back if I hit any more snags. Thanks.
- Local time
- Today, 08:46
- Joined
- Feb 28, 2001
- Messages
- 27,175
Happy to show you the way.
