Looping import of excel columns?

[Coltom]

OK, to set the scene. I have a large excel file containing info’ on approx. 12,000 unique ‘DNA clones’ given in column 1 and labeled Clone. Each clone can contain anything from a few to 10 or so genes the IDs of which are in subsequent columns labeled Genes1, Genes2, etc. Each gene is present in at least one clone but may also be found in other, discrete clones. I have a CLONE table with a pk_clone_ID field for each clone, a GENE table with pk_gene_ID field for each gene, and a join table CLONEGENE with fk_gene_ID & fk_clone_ID. On the CLONE layout I have a portal to the pk_gene_ID & on the GENE layout a portal to the pk_clone_ID. Entry for both pk_gene_ID & pk_clone_ID is set to ‘Indexed, Can’t modify Auto, Always validate, Required Value, Unique’.

 

I have written scripts using Import to import the data into each table but I have to duplicate the Import step for each Genes column manually moving the pk_gene_ID target field to match the appropriate column. This works but it looks a bit ungainly as there are many Genes columns. Is there a way of using a loop step with a calculation after the Import step to move the target field to the next appropriate excel column? I've tried but can't see how to make it work.

[Techphan]

Could you post a simple example of your data (one with maybe 5-10 columns of Excel data) so we can get a better idea of what you are needing?

[Databasic]

Hello Coltom

 

I think you can do what you want by simply repeating the import script step and changing the worksheet column within the script step ( ie hardcoding the selected information ) . So if you have 10 columns you can do 10 imports just changing the target column in each step - do without dialog and its the same as a scripted loop ....

[Coltom]

hi Databasic

 

This is exactly what I did and, as you say, it works fine but I have a total of just over 60 columns to process this way and each succeeding Import cmd has to be 'hard-coded' by manually moving the target field. Hence my query as to whether an initial Import step could be looped with a modification that the data is taken from a specified column.

[Databasic]

I see the problem - but the way you described the excel layout in the first post i took it that the clone had a maximum of 10 or so genes and they were laid out across the columns. If this is basically a once off Perhaps you should convert the excel file to a filemaker file ( by opening it directly with FM ) and then see if you can loop through the new FM file records and parse each record into new records that can then be imported directly into your current file structure.. worth a look Cheers