Transpose or rotate Data

[bac mac]

Metadata for our micrographs exports as a single .csv file that I can easily import into FM and contains two columns and about 100 rows.  The column headings are something like Fieldname and Value.  An abbreviated example would look something like:

Fieldname   Value

Filename   BCB6 60xz.oir

BitsPerPixel   12

channelLaserDataId#01   LD405

etc. etc.

I'd like to import the .csv, and rotate or transpose the table so that instead of 100 records, I end up with one record that has the following fields populated:

Filename=BCB6 60xz.oir

BitsPerPixel=12

channelLaserDataId#01=LD405

 

I already have a table generated from a transposition that I generated in Excel that has all 100 of the correct fields.  I'd rather not have to execute the Excel transposition.  It seems that I should be able to import the original .csv into a temporary table, export the information, then import it into the final table.

Is the goal possible?  Recommendations?

[comment]

I am not sure I understand the workflow here. Is the CSV file generated by exporting from Filemaker? Or do you just want to use Filemaker as an intermediate tool to convert a third-party CSV? If the former, the simpler solution IMHO would be to export the data the way you want it in the first place, instead of going around for another pass.

I am also having problem understanding the two formats that you show. Neither of them is CSV, and both appear to have 3 records. The only difference I notice is that the header line has been removed and the field separator (tab?) has been replaced by a = .

[bac mac]

The latter.  The .csv comes from the micrograph, not filemaker.

The first table shown is just showing the top few records from the metadata file and I left out the commas.

When I import the metadata .csv directly into filemaker, it ends up as numerous records with just the two fields Fieldname and Value.

I want to, with a Filemaker script, convert all of those records into a single record in a different table where the value for the field Filenane would be BCB6 60xz.oir, the value for the field BitsPerPixel would be 12, and the value for channelLaserDataId#01 would be LD405.

There would be about 100 different fields in the final record and that table is already created, but I'm having trouble figuring out how to easily populate it.

Thank you

[comment]

If you have successfully imported the data, then I believe you can export it directly in the desired format. However, I am still struggling to understand the desired output format. Please post an exact description or even better, attach a text file showing what you want.

 

 

 

[bac mac]

I've attached Test Metadata.fmp12

Table/Layout Raw Metadata=the direct import of the microscope capture metadata

Table/Layout Final Metadata=the way I want the data to appear.  (with the plan to have layouts display just the important parameters).

For this example, in excel, I copied the raw metadata excel and pasted with the transpose option, but I'd rather do this in Filemaker and not require lab members to conduct the excel transposition.

Thanks

Test Metadata.fmp12

[comment]

But what is the final goal here? Do you really need a table with 1238 fields? 

If you really needed to make such transposition, you would need a script to loop over the records in the source table and populate one field in the target table for each record in the source table. This could be done using Set Field by Name [] - provided that the Key values will always match exactly your field names. But why would you need such thing?

 

[comment]

41 minutes ago, comment said:

provided that the Key values will always match exactly your field names.

... which upon further inspection is not true for the first 74 fields.

 

[bac mac]

The goal is to have, in a single record, all of the values from the .csv file.  I am able to accomplish this is in excel, by copying all 1238 rows of the 2 columns and paste it into a new sheet with the transpose option.  I was disappointed that I can't figure out how to accomplish this in Filemaker.  I think the Set Field by Name that you suggested will actually work very well.  I'll test that and report.

[comment]

8 minutes ago, bac mac said:

The goal is to have, in a single record, all of the values from the .csv file.

I was under the impression that you only wanted to use Filemaker to convert the data to another format to be used elsewhere. I don't quite see how or why having a single record with so many fields would be useful (or more useful than the original key/value format). 

I also notice that many of the fields, or even subsets of fields, are numbered series - e.g. all contrast #01... #18, all gamma #01...#18, all shadow #01...#18, etc. I would expect these to form 18 records in a related table.

But, if you want, Set Field by Name [] should work fine for you if you do:

Substitute ( Trim ( $key ) ; "-" ; "_" )

beforehand.

 

[bac mac]

The Set Field by Name solved it and works, along with Trim for most, and I'll tweak some substitutes for the others.  I was able to loop through the 1238 imported records in a temporary import table placing the values needed in the fields in a single record in the desired table.

Thank you very much!