Jump to content
nexgen

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

Recommended Posts

nexgen    0

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?

Share this post


Link to post
Share on other sites
comment    1,392
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.

Share this post


Link to post
Share on other sites
nexgen    0
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.

 

Share this post


Link to post
Share on other sites
comment    1,392

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

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.