Populating a Relational Database from available data files

[Raymack]

I  have contact information available in several csv files. Each record represents a family of two with two first names and one last name plus a single address, and perhaps an e-mail address. I want to populate a relational database with this raw data, which will then be edited and missing information added.

 

I’ve created two tables: FAMILIES and MEMBERS. One family can have many members.

 

In FAMILIES I treat each record as a family with a single physical address. For convenience, I show both family MEMBERS in a portal in the FAMILY file.

 

Available data from a “family” data file is imported into MEMBERS. Example of single import record: Bob & Alice Thompson at 3423 Main St. Lubbock, TX 75094, [email protected] (other data may be available). The address information is imported into temp fields in MEMBERS.

 

In FAMILIES, using the portal, the imported names are placed in separate MEMBER records, creating a 2nd MEMBER record. Of course, unique information for 2nd member, such as e-mail, is missing.

 

QUESTION 1: How do I script to get a FAMILY record created for each imported member? I can do it manually by entering the KEYFamilyP.

 

QUESTION 2:  I need to “lookup” the address info imported into MEMBERS temp fields and bring it into FAMILIES. I can’t get this to work….

 

QUESTION 3: Is there a better way to deal with populating a relational database from various datasets available?

 

[comment]

What exactly does the source data looks like?

 

[Raymack]

Well, I started with one csv file, as I described, but expect to get others with some differences, which I can deal with as I import them.

I meant to say, my structure is looking and working great doing manual input via the FAMILY, using the portal to create MEMBER records and populating the three PROFILE files from portals in MEMBERS.  Just wondering if there is a better way to IMPORT data...

[comment]

48 minutes ago, Raymack said:

Just wondering if there is a better way to IMPORT data...

I don't know how to answer that without seeing the data and understanding exactly how it's structured. It seems that there is a record for each family; in such case, you would want to import the data into what will eventually become the Families table, assign a unique FamilyID to each imported record, and then create a related child record for each member. 

Not sure why you need to do any manual input. All of this can be scripted (provided that the data is indeed structured - i.e. does not require human intervention in order to parse it correctly).

 

 

Edited October 20, 2022 by comment

[Raymack]

thought I told how data was structured? I considered importing to FAMILIES but didn't know how to get data into the portal for MEMBERS.  Same problem in reverse as importing into MEMBERS with address info in temp fields to then Lookup into FAMILIES! 

I tried importing from same csv data file, separately, into FAMILIES and then into MEMBERS, but couldn't figure out how to sync the files...

 

[comment]

2 hours ago, Raymack said:

thought I told how data was structured?

Describing your data is not nearly as useful as showing it. The devil is in the details.  If you can post a representative sample CSV comprising  2 or 3 families, then we can start having an intelligent discussion. I say "start", because even a larger example may not be enough; we need to know the rules  by which the provided data is structured.

Without this I am mostly guessing - which is not something I like to do.

 

Edited October 20, 2022 by comment

[Raymack]

Ordinary CSV file with data separated by commas, all text...

Tried this and it seems to work OK:

1. Added Serial No.s for FAMILY to my import file.

2. Imported into FAMILY and then in MEMBERS

thanks for your comments

[bcooney]

On 10/20/2022 at 12:39 PM, Raymack said:

In FAMILIES I treat each record as a family with a single physical address

That’s quite a presumption. 

[Raymack]

No, in this particular case it is always true.  There will only be ONE or TWO in a family.