Easier way to convert a flat file?

[Valdrin]

Hi all,

I'm converting a spreadsheet into a nice relational filemaker pro DB, and was wondering if there was a better way to do it than the one I've come up with. My main concerns are the key fields for the company and employee tables. Each company has 1-100 employees and each row of the spread sheet has a company name and an employee name. Here is what I'm doing to convert it:

1) Set up CompanyID and EmployeeID fields to auto enter serial numbers and import the spreadsheet to a temporary conversion database.

2) Find and sort duplicates based on Company Name, then in list mode manually copy and paste the CompanyIDs so all the duplicates have the same serial number.

3) Import all the employees and pertinent data into the final database with the CompanyID as a foreign key.

4) Based on Company ID, manually delete all the duplicate records then import the Companies and pertinent data into the appropriate table of the final database.

The above works, but I was wondering, does anyone have a better way to do this type of conversion?

Thanks!

[comment]

I would do it this way:

1. In the Companies table, validate CompanyName as Unique, Validate always.

2. Import the spreadsheet into both tables (with auto-enter on, so that each record gets a serial ID).

3. Define a temporary relationship matching on CompanyName. Use this to populate the CompanyID foreign key field in the Employees table.

4. Switch the relationship to match on CompanyID.

[Valdrin]

Thanks Comment, I thought that there must be a better way to do this.