Question

Automatic Excel Reader for Batch Running

H

Hi there,

 

I'm dealing with data conversion from IFC to CityGML by FME, which is a pretty complex workspace. In the final output, I wanna merge IFC attributes in EXCEL format into the CityGML data. so the whole workspace contains 1 IFC Reader, 1 EXCEL Reader, 1 CityGML Writer to complete one model conversion.

 

It works fine if I set the EXCEL reader by selecting Individual Feature Types (select Single Merged Feature Type would fail, I'm not sure why).

Reader selecting Individual Feature Types 

However, as I have plenty of IFC models (as well as related EXCEL files) for conversion, I want to batch run the workspace using command lines. The issue is each of my EXCEL files have different file name, sheet name, attributes numbers & names, the EXCEL Reader could not automatically read the data if just by changing the EXCEL name. I've searched other posts and tried Generic Reader, Schema Reader, etc. it still doesn't work.

Merge EXCEL into CityGML 

Thanks in advance for any ideas/suggestions! :D

4 replies

jkr_wrk
Rank
Userlevel 3
Badge +18

"It works fine if I set the EXCEL reader by selecting Individual Feature Types (select Single Merged Feature Type would fail, I'm not sure why). "

 

Well, there could be an error log that tells us why. ;)

 

If I create an Excel FeatureReader and set it to read a User Parameter Based file I get the rows as features.

 

One of the issues with the Excel FeatureReader (or Reader) is that when there is data on row 1 it will use that as AttributeName. This is one of the few times a Generic Reader could help. Then the Attributes will be named col_1 col_2 etc.

 

When using the Generic Reader expose the following Attributes:

 

fme_feature_type (contains sheet name)

xlsx_row_id

col_1, col_2 etc...

 

 

 

Sample: Every Nth Feature (N=1)
H

"It works fine if I set the EXCEL reader by selecting Individual Feature Types (select Single Merged Feature Type would fail, I'm not sure why). "

 

Well, there could be an error log that tells us why. ;)

 

If I create an Excel FeatureReader and set it to read a User Parameter Based file I get the rows as features.

 

One of the issues with the Excel FeatureReader (or Reader) is that when there is data on row 1 it will use that as AttributeName. This is one of the few times a Generic Reader could help. Then the Attributes will be named col_1 col_2 etc.

 

When using the Generic Reader expose the following Attributes:

 

fme_feature_type (contains sheet name)

xlsx_row_id

col_1, col_2 etc...

 

 

 

Hi @jkr_da​ , great thanks for your reply.

 

Do you mean I could just replace Excel Reader with Generic Reader? It failed to dynamically read different Excel files if i just change the Excel Reader to Generic Reader (or Schema Reader) for this case. Also, I notice only fme_feature_type could be detected for Generic Reader, xlsx_row_id could be set for Excel Reader though. I'm not sure if additional steps should be done (e.g. expose schema) instead of changing the Reader only. As I'm newbee to deal with EXCEL in FME, any detailed illustrations would be much appreciated.

07201I've done dynamic related settings, not sure if I did correctly:

  1. EXCEL Reader setting07202
  2. Writer setting07203

 

jkr_wrk
Rank
Userlevel 3
Badge +18

Hi @jkr_da​ , great thanks for your reply.

 

Do you mean I could just replace Excel Reader with Generic Reader? It failed to dynamically read different Excel files if i just change the Excel Reader to Generic Reader (or Schema Reader) for this case. Also, I notice only fme_feature_type could be detected for Generic Reader, xlsx_row_id could be set for Excel Reader though. I'm not sure if additional steps should be done (e.g. expose schema) instead of changing the Reader only. As I'm newbee to deal with EXCEL in FME, any detailed illustrations would be much appreciated.

07201I've done dynamic related settings, not sure if I did correctly:

  1. EXCEL Reader setting07202
  2. Writer setting07203

 

Here is a working example

 

I included 2 FeatureReaders. First the Generic Reader and second the Excel Reader to show the differences between the readers.

 

What did you select in the FeatureMerger? Because you only exposed fme_basename, xlsx_row_id and number. Number could have data with the Excel Reader but will not contain any data with de Generic Reader. In that case you need to expose col_1 or one of the other column numbers, depending on the column containing the number.

 

And your data will probably not contain any other data, because the other data is not exposed.

Sample: Every Nth Feature (N=1)
H

Hi @jkr_da​ , great thanks for your reply.

 

Do you mean I could just replace Excel Reader with Generic Reader? It failed to dynamically read different Excel files if i just change the Excel Reader to Generic Reader (or Schema Reader) for this case. Also, I notice only fme_feature_type could be detected for Generic Reader, xlsx_row_id could be set for Excel Reader though. I'm not sure if additional steps should be done (e.g. expose schema) instead of changing the Reader only. As I'm newbee to deal with EXCEL in FME, any detailed illustrations would be much appreciated.

07201I've done dynamic related settings, not sure if I did correctly:

  1. EXCEL Reader setting07202
  2. Writer setting07203

 

Much appreciate for your detailed explanation and the workspace! I'll study it.

Reply


Community Stats

28,731
Posts
109,530
Replies
36,566
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.