Skip to main content
Solved

Dynamic attribute names from Excel

peteralstorp
Contributor
Forum|alt.badge.img

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?

Best answer by takashi

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.

 

View original
Did this help you find an answer to your question?

9 replies

david_r
Evangelist
  • david_rEvangelist
  • January 3, 2017

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
Contributor
Forum|alt.badge.img
  • peteralstorpContributor
  • Author
  • Contributor
  • January 3, 2017
david_r wrote:

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
Evangelist
  • david_rEvangelist
  • January 3, 2017
peteralstorp wrote:
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
Forum|alt.badge.img

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
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • January 4, 2017
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
Contributor
Forum|alt.badge.img
  • peteralstorpContributor
  • Author
  • Contributor
  • January 5, 2017
takashi wrote:
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
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • Best Answer
  • January 5, 2017
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
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • January 5, 2017
peteralstorp wrote:
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
Contributor
Forum|alt.badge.img
  • peteralstorpContributor
  • Author
  • Contributor
  • January 5, 2017
takashi wrote:
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


Helpful Members This Week

Community Stats

30,942
Posts
117,257
Replies
38,742
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

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