Importing Excel CSV Files With Repeating Fields

[aaapeter]

I have an excel csv file (with repeating fields) that I'd like to convert to Filemaker. Each row includes data for a specific customer order. While the majority of fields in a row are unique, the product fields have 20 occurrences. A new row is created by my website whenever an order is placed. I'd like to import this data into Filemaker in 3rd normal form (so that there a single record for each item ordered vs. using repeating data), so that I analyze the data (such as how many XL Blue Shirts were ordered on a given month).

What is the best way to convert the data from records using repeating fields to a file in 3rd normal format? Will I need to write a customer script to do this or are there commands or utilities that do this?

Thanks in advance,

Peter

[aaapeter]

I have an excel csv file (with repeating fields) that I'd like to convert to Filemaker. Each row includes data for a specific customer order. While the majority of fields in a row are unique, the product fields have 20 occurrences. A new row is created by my website whenever an order is placed. I'd like to import this data into Filemaker in 3rd normal form (so that there a single record for each item ordered vs. using repeating data), so that I analyze the data (such as how many XL Blue Shirts were ordered on a given month).

What is the best way to convert the data from records using repeating fields to a file in 3rd normal format? Will I need to write a customer script to do this or are there commands or utilities that do this?

Thanks in advance,

Peter

[aaapeter]

I have an excel csv file (with repeating fields) that I'd like to convert to Filemaker. Each row includes data for a specific customer order. While the majority of fields in a row are unique, the product fields have 20 occurrences. A new row is created by my website whenever an order is placed. I'd like to import this data into Filemaker in 3rd normal form (so that there a single record for each item ordered vs. using repeating data), so that I analyze the data (such as how many XL Blue Shirts were ordered on a given month).

What is the best way to convert the data from records using repeating fields to a file in 3rd normal format? Will I need to write a customer script to do this or are there commands or utilities that do this?

Thanks in advance,

Peter

[QuinTech]

Hi, Peter, and welcome to the Forums!

If i were you, i would do this as a series of 21 imports. The first import pulls in the customer-order-specific information (customerID, orderID, customerName, phone, etc.) and imports it into the Orders table. The second through 21st imports pull only product-specific data into the LineItems table: orderID (which you will, of course, need to tie the LineItem back to the Order) and ProductName.

The OrderID for each of the 2nd-21st imports will be from the same Excel column. Also, you will wind up with a lot of empty records in the LineItems table; the best way i can think of to undo this is at the end of the script that does all these imports, just find all records where ProductName is empty and delete them.

HTH,

Jerry

[QuinTech]

Hi, Peter, and welcome to the Forums!

If i were you, i would do this as a series of 21 imports. The first import pulls in the customer-order-specific information (customerID, orderID, customerName, phone, etc.) and imports it into the Orders table. The second through 21st imports pull only product-specific data into the LineItems table: orderID (which you will, of course, need to tie the LineItem back to the Order) and ProductName.

The OrderID for each of the 2nd-21st imports will be from the same Excel column. Also, you will wind up with a lot of empty records in the LineItems table; the best way i can think of to undo this is at the end of the script that does all these imports, just find all records where ProductName is empty and delete them.

HTH,

Jerry

[QuinTech]

Hi, Peter, and welcome to the Forums!

If i were you, i would do this as a series of 21 imports. The first import pulls in the customer-order-specific information (customerID, orderID, customerName, phone, etc.) and imports it into the Orders table. The second through 21st imports pull only product-specific data into the LineItems table: orderID (which you will, of course, need to tie the LineItem back to the Order) and ProductName.

The OrderID for each of the 2nd-21st imports will be from the same Excel column. Also, you will wind up with a lot of empty records in the LineItems table; the best way i can think of to undo this is at the end of the script that does all these imports, just find all records where ProductName is empty and delete them.

HTH,

Jerry