Import Data from Another Application

How do I?
1

This is a request for advice rather than a technical question.

I have a working Access application, and am in the process of developing this in Five. I already have all required tables, but when it comes time to import live data from the Access application, I need to make sure it is being done properly. This includes all “main” tables and also join tables. Almost all tables have a GUID primary key.

Here is what I think needs to be done:

  1. Add an OldCode integer field to “main” tables in Five
  2. Add OldKey1 and OldKey2 fields to the join tables.
  3. Clear all “main” tables and join tables
  4. Export the “main” tables in Access by querying their data into Excel, adding the OldCode value from the auto-incrementing primary key in Access
  5. Import all “main” table data (obviously creating new GUID key)
  6. Repeat for join tables, omitting the 2 foreign key values (temporarily remove the required flag for those), and filling in OldKey1 and OldKey2 with those values.
  7. Write queries to update the join tables using joins with “main” tables to get their OldKeyValues, and replacing the foreign keys fields with the new GUID from the “main” tables.

I believe step 3 can be done by going into tables, selecting one, then clicking + to add a table, but instead let it redefine the selected table. Please verify.

Thanks in advance for any advice.

2

Hi Ron,

There’s a feature that lets you reference a foreign key when importing a CSV file into your tables.

For example:
Let’s say the Product table has the columns ProductKey and Name, and the ProductPrice table has ProductPriceKey, ProductKey, and Price.

If the product name is unique, you can use that to help with the import.

First, in the Product table, create an index called Name that points to the Name field. After that, you can import the data into the Product table as usual. If the primary key from the old system is different from Five’s format, just choose to have the primary key generated during import.

Then, in the ProductPrice CSV file, for the ProductKey column, you don’t need to enter the new primary key from the Product table. Instead, you can use @ProductName.

For example, if the product name is TV, you can simply put @TV. When Five imports the data, it will look up the Product table using the Name field and replace it with the correct ProductKey.

Please note that this only works if the Name index already exists. I don’t believe you can add an index after the table has been created.

If you’d like, you can create a simple test app like the example above and try this approach first.

This is probably the easiest option if you want to avoid using code (your explanation/example looks good to me). Another option would be to export the Testing and Production database, make the changes manually, and then import it again. However, I wouldn’t really recommend that, since a mistake could affect the application in that environment.

Regards,
Elton S

3

thanks for the answer, Elton.

I certainly agree that exporting, editing and importing won’t work. I only have one environment, DEV, but the main issue is that the Access database is full of current data, whereas the new five application has old, stale data. Good for development, but will need current Access data to import.

Your example is understood, but I’m not sure is describes my situation. Picture 2 primary tables, Members and Groups. Then a join table GroupMembers. GroupMembers contains foreign keys to Members and to Groups. But those GUID key values are no longer good once I clear and re-fill the 2 master tables, as they will have new unique GUID keys. It seems like adding new fields to all 3 tables, to contain the old integer keys will help with importing all 3 tables, then I think I must use code to query the join table and the 2 master tables with those joins and therefore obtain the new GUID keys to insert into the proper join table fields.

I like the example of using @TV, and didn’t realize that would work, but not sure this technique will work for a join table scenario, since the join table doesn’t have a unique field to tie it to the master table, they way your example seems to.

Please correct me if I’m not properly understanding how to apply your suggestion to a join table scenario.

4

Hi Ron,

The example referring to a foreign key should also work for a join table.
You just need to add an index on each parent table and a @valueName in the Join table.

Regards,
Elton S

5

Thanks for the quick reply. Just want to make sure I understand this. I’m comfortable using my original idea, but it is a bit more cumbersome. So allow me to make sure I get what you are saying, using my tables.

  1. You said the new index can’t be added after the fact. Are you sure? I can’t go into the Members table and add a SortName index after the fact? If needed, I can first clear the data then add the new index.

  2. My master tables are Members and StudyGroups. They have primary key GUIDs of MembersKey and StudyGroupsKey respectively. Join table is GroupMembers and has GroupMembers GUID primary and MemberKey, StudyGroupKey GUID foreign keys. If I CAN add a SortName index to Members and a GroupName index to StudyGroups, then when I write the query to export the join table records from Access, instead of including the actual MemberKey and StudyGroupKey in the export, I put a literal like “@” + the SortName value in the MemberKey field of the extracted data, and ditto for the StudyGroupKey field? (“@” + GroupName value)? Then it will properly import with no further coding or updating? And should I put those literal values right in the export columns that would normally be the foreign key values before importing the CSV data?

  3. Fields like SortName in the Members table are calculated fields, from LastName + ", " + FirstName. Can this still work if I can add an index for the master table name fields (I know that normally the calculated fields are only updated during form interaction)?

If you could answer these 3 questions, I can try to clear my data then import from the access database.