Question

Looping through csv file

  • 13 April 2018
  • 4 replies
  • 30 views
R
Badge

I have a csv file that is used as configuration for various database schema attributes. Against each layer name there are a a set of fields and a set of values that are valid for the field.

I have a workspace that can check if the database contains values that are outside the permissible range:

However, this only works for a single row. What I need to do is to loop through the csv file processing each row individually like a recordset.

Is this sort of behaviour possible in FME? I have seen some posts on looping but nothing that really fits what I am trying to do.

4 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @rohan, I think you can build WHERE clause and detect values out of range for each field if you use the SQLExecutor. e.g.

0684Q00000ArK11QAF.png

You can then analyze resulting records to get required result.

If you just need to know the number of values out of range for each field, this SQL statement could also be available.

SELECT count(*) AS num FROM my_table WHERE @Value(_where)

Note: SQL syntax may be slightly different depending on the database format. Modify appropriately for your actual database format if necessary.

G
Badge +3

in-range-inlinequerier.fmw

Your setup is basically correct.

Of course values may belong to multiple ranges.

Also if you explode your input example you either have 3 ranges or 10 values belonging to 3 ranges.

This works

R
Badge

Hi @rohan, I think you can build WHERE clause and detect values out of range for each field if you use the SQLExecutor. e.g.

0684Q00000ArK11QAF.png

You can then analyze resulting records to get required result.

If you just need to know the number of values out of range for each field, this SQL statement could also be available.

SELECT count(*) AS num FROM my_table WHERE @Value(_where)

Note: SQL syntax may be slightly different depending on the database format. Modify appropriately for your actual database format if necessary.

@takashi Thanks to both of you for your responses. I've been trying Takashi's example as it's a bit simpler.

 

Unfortunately, I'm not getting a usable result.

 

Here's my SQLExecutor:

 

0684Q00000ArMOYQA3.png

 

()(I have no idea why FME thinks there's a "col3" in the csv file.) I get 7 records, which seems correct. But when I look at the output in the data inspector, I don't seem to get anything I can actually use:

 

0684Q00000ArMAPQA3.png

I was hoping it would execute the SQL, but instead it's just giving it back to me in fragments.

 

Am I missing something here?

 

takashi
Rank
Userlevel 2
Badge +17

Hi @rohan, I think you can build WHERE clause and detect values out of range for each field if you use the SQLExecutor. e.g.

0684Q00000ArK11QAF.png

You can then analyze resulting records to get required result.

If you just need to know the number of values out of range for each field, this SQL statement could also be available.

SELECT count(*) AS num FROM my_table WHERE @Value(_where)

Note: SQL syntax may be slightly different depending on the database format. Modify appropriately for your actual database format if necessary.

Have you exposed column names with the 'Attributes to Expose' parameter in the SQLExecutor?

 

Select a record on the Table View in FME Data Inspector, and see the Feature Information window to check if columns of the record have been read correctly.

 

 

Reply


Community Stats

28,698
Posts
109,448
Replies
36,548
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.