importing from csv or Excel dilemma

[dysong3]

I am feeling pretty dumb about not know how to resolve this.  I have a Filemaker file that keeps a daily record of different ticket prices sold in our theatre.  We have just changed our ticketing system and now receive a daily csv and/or Excel file that summaries the number of different seats sold at different prices.  When I try to import this data into my pre-existing Filemaker file I am confronted with a "specify import order" window that has 7 "pages".  This means that the number of tickets sold is presented on 6 "pages" of the import order specification.  But I don't think there is a way of taking only the line of data from say page 2 into a given field, and then the same line of data from page 3 into another given field.  So how do I resolve this?

Billets_et_revenu_pa_1630482946864.csv

Edited September 9, 2021 by dysong3

[comment]

I am not sure I understand your dilemma. The example CSV file contains a table of 6 records (the first line contains field names), each having 6 fields of data.  The normal procedure would be to import the data into a table that has a corresponding target field for each of the 6 source fields, and have the import create 6 new records in the target table. 

You seem to want to map each record into different field, which is not possible - at least not by importing. You could import the data into a temp table, and then pick the values you want from there and transfer then to your "real" table. Or you could get the wanted data directly from the file, using the Read from Data File [] script step (this is only possible with a CSV file, not Excel).

However, before you do any of that, I would suggest you re-evaluate your structure. You seem to have 2 "alike" fields for different types of spectators. That would be a violation of database normalization - and I suspect this is also the cause of your current problem.

 

Edited September 9, 2021 by comment

[dysong3]

Thanks for your reply.  Maybe I can explain it better.  The CSV file has 6 records.  The data I need to obtain from the CSV file is the second last field of each of the 6 records.  In this case 7, 12, 8, 11, 7, 1.  In my Filemaker file, I have 6 number fields in each record that need to be fed these six different numbers which are used for counting each ticket price.  The only way I can think of resolving this for the moment is to separate the CSV file into 6 different files, and import them one at a time, but that seems ridiculously laborious. 

I don't think that my database is fundamentally unsound, as I have a different field and a different counting field for each ticket price.

So if I understand correctly, you would suggest creating a sort of  "buffer" file or table that would import the raw data and from which I could import it into my database?

 

[comment]

2 hours ago, dysong3 said:

I don't think that my database is fundamentally unsound, as I have a different field and a different counting field for each ticket price.

I tend to disagree. Of course, I only know the very little you've told us about your file - still, I believe you should have a record in a related  table for each one of the six values, with fields for the value and for the type. Then you will be able to simply import the given CSV into the child table. And you will only need one summary field instead of six.

 

2 hours ago, dysong3 said:

So if I understand correctly, you would suggest creating a sort of  "buffer" file or table that would import the raw data and from which I could import it into my database?

Almost. You wouldn't be able to import from the temp table. You would need to have the importing script loop over the imported records and transfer each record's value into the corresponding field in your "real" table.

IOW, you would have gone through the trouble of creating a sub-table anyway, but not enjoy the full advantage of having it.