Skip to main content
Question

Running multiple SQL queries from excel file


tthach830
Participant
Forum|alt.badge.img+1

Hi,

 

I have 50 SQL queries stored in a excel file.

Column A: Name_SQL_Query

Column B: SQL_Query

I am having a hard time find ways to execute the query and export to a CSV file with Name_SQL_Query.

 

I set the csv writer to dynamic exposed. however, when i run the workspace i only repeated sql 6016 times (this is the number of rows I get when i execute the sql by itself). it seems like the first Query from the excel list is working when i sampled the first 1. However, I am getting the repeating of column A and B. 

How do i export the result straight from the SQLExecutor? 

 

3 replies

redgeographics
Celebrity
Forum|alt.badge.img+49

in broad terms each row from Excel should go into a SQLExecutor that runs that query and creates features from the result. You can then write that out to whatever format you like.

Are the 50 result sets expected to have different columns? If so, you’ll need to make sure to also read Schema features and to retain the original attributes so you can tell the sets apart.


geomancer
Evangelist
Forum|alt.badge.img+47
  • Evangelist
  • April 8, 2025

Maybe you can modify the best answer to this question to your needs.


takashi
Influencer
  • April 8, 2025

Hi ​@tthach830 ,

SQLExecutor creates schema feature(s), but “fme_feature_type_name” attribute in schema features and “fme_feature_type” attribute in data features both stores text string "SQLExecutor" by default.

If you would like to make destination csv file name the value of "Name_SQL_Query" with the dynamic schema mechanism, you will have to modify those attriute value appropriately.

A possible way is:

  • SQLExecutor : Set "Keep Initiator Attributes if Conflict" to the Combine Attributes parameter to propagate "Name_SQL_Query" attribute to output features, set "Schema and Data Features" to Features to Read parameter to read both schema features and data features.
  • Schema features from <Schema> port : Expose "Name_SQL_Query" attribute with AttributeExposer and then rename it to "fme_feature_type_name".
  • Data features from Result port : Rename "Name_SQL_Query" to "fme_feature_type".
  • In the CSV writer feature type, set general parameters like below. 
    • CSV File Name : fme_feature_type
    • Dynamic Schema Definition : <check>
    • Schema Sources : "Schema From Schema Feature"
    • Schema Definition Name : <Default from CSV File name above>

See also the screenshot below.

 


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