convert excel 2d array to 1d list after import

[daveinc]

Hi, I have a recurring import of Excel spreadsheets in .tab form where the data is in a 2d array with the first record being the job name in each column and the rows containing the data category in the first field and an amount for each column in each field after that. I do not know in advance how many columns are in the spreadsheet or what their values are. Ideally the job name would be another field in each row and every amount would have a category and job name in its row. Is there an easy way in FM to manipulate this arrangement and convert the 2d array into a list with the category, amount and job name listed for each record? The table 2d Excel array cannot be editied in Excel or any other program. All conversion must happen in FM12 after import. I would upload an example but apparently .tab files aren't allowed. Thanks in advance for any tips.

 

 

[Wim Decorte]

I'm having trouble visualizing this... can you zip the tab file and upload it, reduce it maybe to 5 or 10 records or so.  I think the upload issue was more size than file type.

 

The fact that you don't know how many columns there are makes it difficult because you can not dynamically create fields in FM (at least not easily).  So it would seem to me that you need to do some pre-processing outside of FM before bringing it in.  Is this Windows, Mac or both?

[daveinc]

Here it is Wim. It is a typical 2d array with x-axis and y-axis values. I need to make a list with the x-axis values included in each record. Thanks for having a look.SampleTab.zip

[Wim Decorte]

so each record in FM would be the equivalent of one cell in that table, with 3 fields:

 

 

51201 · Executive Producer, 0, 201SJ

 

51201 · Executive Producer, 0, X00TD

and so on...

 

Problem remains that you don't know how many columns there will be in any one import.  You could preset it to a hug number, say 1,000.  Although if the source is Excel 2033 it could never have more than 256 columns.  But say 1,000

 

You would then import your summary table into a FM table and loop through each cell and create a new record for it in your final FM table.  Not very pretty but entirely doable.

[daveinc]

Thanks for looking Wim. I was hoping someone had a trick up their sleeve for this to avoid the long looping script. I guess I will have to bite the bullet.