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.

6

This topic has been a lower priority than getting my application to work properly, but it’s been 3 months since my last question, and I need to start working on this again.

I will try re-stating my understand of how to do this, and I hope you can validate this process. Assume my main tables, Members and InterestGroups, and the join table GroupMembers.

  1. Clear Members, InterestGroups and GroupMembers tables. Will five allow me to run a query in the designer “truncate table Members” or must I “Delete From Members”?
  2. Make sure Members and InterestGroups have an index for SortName and GroupName respectively. SortName is a calculated field. Will this still work?
  3. Import data from exported Access data for Members and InterestGroups
  4. Export my Access join table (GroupMembers), but in the 2 foreign key fields of the export, use “@” + the associated SortName (for example, @Mittelman, Ron and @Book Brothers)
  5. Import the join export into the GroupMembers table.

Please verify that this is the correct procedure, along with answering the bold questions.

Thanks so much for your help.

7

Hi Ron,

To help ensure your data migration goes smoothly, I’d recommend creating a simple test application with the same table relationships as your production application. This will give you a safe environment to practice the migration process and become familiar with the steps before importing data into the real application.

Regarding your questions:

1. Clearing and re-importing data

Back up the data from each table you are about to clear.

You can safely clear the Members, InterestGroups, and GroupMembers tables.

After that, you can re-import data into the main tables (Members and InterestGroups) using either the Query Designer or a CSV file. There’s no need to delete and recreate the forms unless your data structure has changed (for example, if you’ve added, removed, or renamed columns).

2. Setting up indexes for foreign key lookups

Make sure the Members and InterestGroups tables have the appropriate indexes configured to support CSV imports with foreign key lookups.

Note: The SortName field doesn’t need to be populated during this stage. The primary goal is to ensure that the join table contains the correct foreign key references.

If SortName is a calculated value, such as:

LastName + ", " + FirstName

you can populate it later by running an update query after the data has been imported.

I am assuming the SortName is not used as the index.

3. Importing the source data

Export the required data from your Access database, prepare it for import, and then import it into Five.

4. Importing the join table (GroupMembers ) using foreign key lookups

When importing CSV data with foreign key lookups, you can use the @ syntax to reference a value from another table and retrieve its primary key.

For example, the value “@Mittelman, Ron” will not work because everything after the @ must refer to a single indexed column. In this case, Five cannot determine that Mittelman belongs to LastName and Ron belongs to FirstName.

That’s why the column used for the lookup must contain unique values. In the response I shared previously, ProductName was unique, allowing me to reference it using @ProductName.

Please try to use another field that will be unique in each table (Members and GroupMembers).

My recommendation is to test this approach in a dummy application first. At the same time, feel free to post any specific questions you encounter during the process, and we’ll be happy to help you find the best solution.

Once you’ve successfully imported the data into your Development environment, we can guide you in exporting and migrating the data into the Production environment.

Regards,
Elton Santos

8

Thanks Elton,

I’m confused. The SortName column is a unique column with an index on it. It’s a calculated column containing last name plus ", " plus first name. My Access export will have the correct values in that column. So it will be populated into the Members table column. So it seems like the join table export will have “@Mittelman, Ron” in that foreign key column for that record. So if that column contains the proper SortName values, why won’t that @ notation in the join table foreign key column work? Does having a comma in the export file column cause an issue? If so, I could instead use the Full name column instead. For the lookup.

Instead of making a test application for this testing, I was thinking of exporting the application to an fdf file then restoring from that if the import blows things up. That seems easier than making a test application. What do you think about that idea?

Thanks…

Ron Mittelman

9

Hi Ron,

I will clarify my explanation using the scenarios below in case I have misunderstood your scenario:

Scenario 1:

Table Members has the fields MemberKey, Name(index added) and LastName.

When importing the data into the table GroupMembers by adding ‘@’ + Name into the field MemberKey (foreign key), the primary key of the field MemberKey will be replaced with the field Name.

Scenario 2:

Table Members has the fields MemberKey, SortName(index added), Name, and LastName.

When importing the data into the table GroupMembers by adding ‘@’ + SortName into the field MemberKey (foreign key), the primary key of the field MemberKey will be replaced with the field SortName.

Scenario 3:

Table Members has the fields MemberKey, Name, and LastName.

When importing the data into the table GroupMembers by adding ‘@’ + LastName + Name into the field MemberKey (foreign key), the primary key association won’t work because there is no unique field with an index to be linked with.

Having a comma ‘,’ should not cause any issue, and your approach to exporting the application as a safe backup and making all the database migrations is a much better solution. My advice was only to create a simple application with these three table structures for your tests, so you could work with a simple table structure before trying it in your final application.

Regards,

Elton S

10

Thanks Elton.

I accidentally created another topic about this, called Deleting Test Data from Tables.

Now that I realize the topic should be on this thread, can you please delete the other thread?

The issues I’m having are more complex than creating a small test database would help with. Some tables are child tables in relationships, and I thought deleting their data would help me be able to delet the data from the parent tables. but too many relationships. I tried deleting some data but then couldn’t delete other data because of other foreign key relationships. Is there a way to delete all data at once from all tables?

This must be solved before actually creating my import files from live Access data.

Thanks…

11

Hi Ron,

The safest way is to delete all the child tables and then their parents.

You can navigate to the Tables menu, select a table record and click the ‘Create Table’ button, which will force the recreation of the table based on the Five’s definitions. (This is the approach I would use).

Another way is to navigate to the Databases menu and click on the button ‘Reset Development Database’. However, this approach will delete the data from all tables at once, including the iUser table (so you won’t be able to log in unless you insert the data back). If you want to use this approach, make sure you back up all tables you want to restore data to. (I suggest you export your database and also export the table you want to restore the data into a CSV file via the Tables menu.

Regards,

Elton S

12

Thanks for the reply, Elton.

The challenge, as you mentioned, is to delete the data in the correct order.

If I have master - join - master, I believe I need to delete from the join table first, then the 2 master tables. Is this correct?

If I have table - support table (such as InterestGroups and GroupCategories, then GroupCategories is the master table, and InterestGroups is the child table. So I need to delete from InterestGroups then from GroupCategoriess because InterestGroups has a CategoryKey field which is a foreign key pointing to GroupCategories. Is this correct?

The problem I was having before was that the query definition screen wouldn’t let me type in Truncate table GroupCategories, even though I’ve deleted the InterestGroups records. ChatGPT informed me that Truncate Table seems to have more controls and preventative measures than Delete From. So I was able to complete my clearing of tables by using Delete instead of Truncate.

I don’t like the second option you just gave me. Can you please verify that as long as I delete table data in the proper order, that the CreateTable method is better than having to write SQL code to delete records for each table?

It seems that deleting records via the SQL window is safer, because if there is a foreign-key constraint that hasn’t been addressed, it gives me an error message telling me that.

Can you verify whether the CreateTable method will also do that, in case I am clearing the tables in the wrong order?

Update: I’ve been able to delete all of the data, and am now starting with the import phase. In my Members table, there is a DateAdded field, which normally gets set automatically when I add a record. Since that is a TimeStamp field, it forces the date in the CSV file to look like “yyyy-mm-ddThh:mm:ssZ”. But this forces the date to be 7-8 hours off if it assumes my supplied date is UTC, when my data in Access is actually local Pacific time. So the question is: Is there another code that I can use in place of the Z at the end, or must I calculate that UTC time based on the original date-time in Access? This would be difficult, since some dates will be daylight time and some won’t.

Thanks again…

13

Hi Ron,

You can use CreateTable in any order; just make sure you delete all dependent tables.
It is always advisable to do so in the right order. You can back up your application and give it a test. Let me know, please, if you have any questions.

Our TimeStamp types are based on UTC; that’s why we can handle different time zones.

My suggestion is to change the value of that single column to add/update the format, and then you can save a new CSV with this format before importing. (Perhaps you can use AI to help with it).

Meanwhile, you cna try this workaround:

  1. Import the CSV into Excel.
  2. Add a column that converts the local timestamp (or timestamp with offset) to UTC.
  3. Copy the converted values back into the original timestamp column.
  4. Save the file as CSV again
  5. Import the new CSV.

For example, if your CSV contains:

MemberID,DateAdded

1,2026-06-06T14:30:00-07:00

2,2026-12-15T16:45:00-08:00

You could convert them to:

MemberID,DateAdded

1,2026-06-06T21:30:00Z

2,2026-12-16T00:45:00Z

Regards,

Elton S

14

Thanks for the reply Elton

Not sure I understand your answer. Are you saying to alter the exported values in that column to actually be UTC before importing? Perhaps Excel can do that for me. Perhaps that column should be DateTime instead? I don’t remember why it was a Timestamp in the first place.

I will have the same issue in other tables. Maybe all timestamp fields should be changed to DateTime?

Thanks…

Ron Mittelman

15

Hi Ron,

I have updated my previous answer with a workaround regarding the field Timestamp.

Yes, you will face the same scenario for all timestamp columns.

I cannot advise you to replace your timestamp columns with a datetime. I would recommend investigating how this field is used within your application and considering your customers’ requirements.

A few examples of the difference between them:

Advantages of using DateTime:

Easier migration, No timezone conversions, Simpler CSV format

Disadvantages of using DateTime:

Timezone information is lost, Problems for global users, Daylight Saving Time ambiguity

Regards,

Elton S

16

Hi Ron,

Could you please share the latest version of your appliation and a copy of the CSV file(s) you are trying to import?

We want to investigate this scenario further and ensure that any changes will work.

You can share the information via OneDrive instead of sharing it here.

Regards,

Elton S

17

Thanks Elton,

I have solved this issue via a work-around. I created an Access function that takes the date and converts it to UTF date-time, then returns a timestamp-like value similar to:

2026-08-20T23:44:00.000Z

I call this function directly from my export query and that takes care of the timestamp fields.

There are still a couple of quirks related to this import subject.

1: When I imported the Members table, then imported the Profiles table, there are 2 fields in Profiles that reference foreign keys pointing to the Members table. That way, the PortfolioVP and PortfolioRegistrar fields in the Profiles table will have the pointers to the Members table. In the CSV, I put something like @John Doe and @Joe Blow in these csv fields. This worked like a charm.

However, when trying to import InterestGroups, I had problems. InterestGroups has a CategoryKey field, which uses a foreign key pointing to GroupCategories table. When importing the CSV file, it has fields containing @Miscellaneous for CategoryKey and @A for the ProfileKey field. Both of those values are alive and well in the parent tables, and they have a proper index on them (I think).

The import keeps having errors referencing trying to import with a null value in those fields.. See the _FiveExportGroupsTest.csv file on the OneDrive folder. It contains only 1 record to import. On suggestion from ChatGPT, I replaced the @ codes with the actual GUIDs for those fields, and the CSV imported perfectly. If you change the GUIDS to @Miscellaneous and @A respectively, it will fail. **Is there anything you can see that I did wrong for these tables?

2:

When adding a record from the UI, I got errors for the Week1 through Week5 fields, saying they can’t be null. If I turned them true then false in the UI then tried to save the record, it works fine. I looked at the field definitions for thes 5 booleans and they both default to false. I don’t understand this.

Thanks…

18

UPDATE:

It looks like the same thing is happening with my first join table.

Members table is a master table
InterestGroups table is a master table
GroupMembers is a join table, which contains “@xxx” for MemberKey, and “@xxx” for InterestGroupKey in the exported CSV file.

Both of these should be getting their values from the foreign key lookup, but when I try to import the join table it says something about the fields can’t be null.

This mirrors what happened with the InterestGroups table and its relationship to the GroupCategories table.

I thought I properly installed indexes on the master tables so the @ lookup could find the proper records.

I hope you can help figure out what is not working with this import-lookup process. I will copy the join table CSV file to the OneDrive folder.

Thanks…

19

UPDATE:

Same issue trying to import InterestGroupMembers table.

I’ve put 2 files in the OneDrive folder: InterestGroupMembers.csv and InterestGroupMembersTest.csv.

I get the same error, InterestGroupKey cannot be null.

I tried modifying the first record in InterestGroupMembers.csv, replacing the “@” codes with the actual GUID values from their respective tables.

Even though the first record was “correct”, I still get that error, probably on the second row. Apparently, all records must be found for the import to succeed.

InterestGroupMembersTest.csv has only 1 record. Again, I substituted actual GUIDS for @ codes, and this time the one record imported.

This shows me that the foreign key relationships are fine, but the @ lookups are not working. I hope you can help me with this.

If this cannot be fixed, I may still be able to import my Access data, but I would need to pre-condition the CSV files to replace the “@xxx” with actual GUIDS. Excel can do this, but I need to export the master tables from Five to CSV files, then open both master and join files in Excel and using lookup formulas, replace the “@xxx” codes with the actual GUID values.

This process would be very time-consuming. I hope we can figure out why the “@xxx” codes are not being looked up properly.

Thanks…

20

Hi Ron,

Thank you for sharing the files.

I have notified the development team about this scenario.

Meanwhile, I will perform more tests and keep you updated on any solution.

Regards,

Elton S