Skip to main content
  • EN

New year - new problems.

I have an excel file with fieldnames Layer 1, Layer 1, Layer 2, Layer 2. My second and third row of data consists of defining parameters per column that I need to decide how to route the actual data. These parameters can vary from sheet to sheet, and I need to handle multiple sheets with the same routine. 

And then I have my actual data values on row 4 and forth.

Example:
Layer 1, Layer 1, Layer 2, Layer 2
Stone, Stone, Clay, Clay
Time, Depth, Time, Depth
1, 0.5, 0.2, 0.3
0.4, 5, 0.2, 0.3

To be able to handle route the data correctly I need to create attribute names and then have these names apply to the actual data. 

Any ideas on how to solve this?

Lots of ways, but take a look at the SchemaMapper, it was made for this type of scenario.

It is a bit complicated to get the hang of, however, but there are several good tutorials.

Lots of ways, but take a look at the SchemaMapper, it was made for this type of scenario.

It is a bit complicated to get the hang of, however, but there are several good tutorials.

Thanks, David, I was afraid the SchemaMapper would come up! I tried it a few times but we're not the best of friends... I'm going to check out the tutorials (again!). Is there any one in particular that you feel might be a good start in this case?

 

 

Thanks, David, I was afraid the SchemaMapper would come up! I tried it a few times but we're not the best of friends... I'm going to check out the tutorials (again!). Is there any one in particular that you feel might be a good start in this case?

 

 

I'll admit I'm not a huge fan of the SchemaMapper either ;-)

 

Some options:

 

  • Use Joiner(s)
  • Excel reader with FeatureMergers
  • Excel reader with InlineQuerier
I'm sure there are more...

Hi @peteralstorp

you can specify what row contains attribute names (is it row 3?) If necessary, you can read the same Excel spreadsheet more than once - e.g. to access first 2 rows that can later be used for some kind of decision making (how to route your data).

Hi @peteralstorp, can you explain the requirement in little more detail?

 

  • Do the first three rows always contain elements that should be used to determine the meaning of each column?
  • Is the number of columns always four?
  • What attribute names do you need to create based on the first three rows? For example, you need to create "Layer 1 Stone Time", "Layer 1 Stone Depth", "Layer 2 Clay Time", and "Layer 2 Clay Depth" as attribute names for each column?
etc...
Hi @peteralstorp, can you explain the requirement in little more detail?

 

  • Do the first three rows always contain elements that should be used to determine the meaning of each column?
  • Is the number of columns always four?
  • What attribute names do you need to create based on the first three rows? For example, you need to create "Layer 1 Stone Time", "Layer 1 Stone Depth", "Layer 2 Clay Time", and "Layer 2 Clay Depth" as attribute names for each column?
etc...
Of course, takashi!

 

 

Although I solved this the ugly way (since the sheet is looked I setup the concatenated column names before hand and made them unvisible). So, if you don't have the time there's no problem.

 

 

Yes, the first three elements always need to be needed. After the first three columns the data block follows with 1-n rows.

 

 

And yes, for instance "Layer 1 Stone Time", that's what I need.

 

 

Thanks for helping, either way.

 

 

Peter

 

 

There may not be easy ways. The attached workspace example contains two workflows: one uses several transformers including AttributeExploder, Aggregator, FeatureMerger, and BulkAttributeRenamer; another uses just a single PythonCaller.

 

dynamic-attribute-names-from-excel.fmwt (FME 2016.1.3)

 

Hope this helps.

 

Of course, takashi!

 

 

Although I solved this the ugly way (since the sheet is looked I setup the concatenated column names before hand and made them unvisible). So, if you don't have the time there's no problem.

 

 

Yes, the first three elements always need to be needed. After the first three columns the data block follows with 1-n rows.

 

 

And yes, for instance "Layer 1 Stone Time", that's what I need.

 

 

Thanks for helping, either way.

 

 

Peter

 

 

There may not be easy ways. The attached workspace example contains two workflows: one uses several transformers including AttributeExploder, Aggregator, FeatureMerger, and BulkAttributeRenamer; another uses just a single PythonCaller.

 

dynamic-attribute-names-from-excel.fmwt (FME 2016.1.3)

 

Hope this helps.

 

There may not be easy ways. The attached workspace example contains two workflows: one uses several transformers including AttributeExploder, Aggregator, FeatureMerger, and BulkAttributeRenamer; another uses just a single PythonCaller.

 

dynamic-attribute-names-from-excel.fmwt (FME 2016.1.3)

 

Hope this helps.

 

Nice, Takashi! I like the python solution. Wish I could do more with Python myself. I can't seem to mark your answer as Correct. Maybe you need to post it as a reply?

 

 

Reply


Community Stats

30,354
Posts
115,362
Replies
38,091
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.