Script for importing a csv file whose column order keeps on changing

[nexgen]

I'm creating a script for importing a csv file. That file will get imported every week.

I don't want to present a dialog box to user for mapping field since they might make mistake.

However, there's a problem. The file's column order keeps on changing. So, I can't use the last import order.

Is there anyway I can import records on the basis of column title? I know there's matching field name option but the problem is the column title of the csv file are like "Refunds ($)" and I can't name the filed using special characters like (, $ in filemaker.

Any solution?

[comment]

1 hour ago, nexgen said:

The file's column order keeps on changing.

I find that very unusual. But if that's the situation, I would suggest you import into an intermediate table, with 2 sets of fields: a set of text fields for the initial importing and another set of fields that your import script will populate according to the value in the first record of the imported set. Then have the same script perform a second import into the real target table, using permanent mapping of the 2nd set of fields to the fields of the target table.

[nexgen]

20 minutes ago, comment said:

I find that very unusual. But if that's the situation, I would suggest you import into an intermediate table, with 2 sets of fields: a set of text fields for the initial importing and another set of fields that your import script will populate according to the value in the first record of the imported set. Then have the same script perform a second import into the real target table, using permanent mapping of the 2nd set of fields to the fields of the target table.

Can you please give me an example? I think I got somewhat you said but I couldn't understand it entirely.

Below is the part which I couldn't understand entirely:

Quote

with 2 sets of fields: a set of text fields for the initial importing and another set of fields that your import script will populate according to the value in the first record of the imported set.

 

[comment]

See if the attached demo helps. It assumes you have imported the data into the three grey fields (f1, f2 and f3). The script finds which of the three fields has the "Color" label in the first record, and populates the blue Color field with the data from that field.

RemapFields.fp7

[nexgen]

Excellent! Thanks.