Solved

Dynamic attribute names from Excel

  • 3 January 2017
  • 9 replies
  • 3 views
peteralstorp
Rank
Badge

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?

icon

Best answer by takashi 5 January 2017, 09:58

View original

9 replies

david_r
Rank
Userlevel 4

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.

peteralstorp
Rank
Badge

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?

 

 

david_r
Rank
Userlevel 4
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...
L
Badge

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).

takashi
Rank
Userlevel 2
Badge +17
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...
peteralstorp
Rank
Badge
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

 

 

takashi
Rank
Userlevel 2
Badge +17
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.

 

takashi
Rank
Userlevel 2
Badge +17
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.

 

peteralstorp
Rank
Badge
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

28,707
Posts
109,479
Replies
36,553
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.