Export as SQL

[Quito]

Hi,

I receive Excel files weekly with some columns I need to clean up, in order to produce SQL queries.  I'm thinking of importing the Excel to FileMaker to have it generate the SQL queries.  The SQL script then connects to an AS400 i7 series, via DBeaver (using its freeware JDBC) and retrieves tabbed text, which is imported into FileMaker for analysis. A previous post resolved how to deal with fixed-length formats and how to export multiple records (rows) in a simple yet effective manner, and has taken a significant part of the burden away for me.  Here's the original post, with the solution given by Comment (EightCharFile2.fp7.zip )

The generated SQL needs a chunk of text at the beginning and at the end of the script.  For sake of brevity, one non-repeating Header and one non-repeating Footer for DBeaver to interact correctly with the AS400. Something like this:

-----

HEADER
(IRMHIS= 11631366 AND IRMDEP= 3590 AND IRMCIT BETWEEN 20190223 AND 20190225) OR
(IRMHIS= 17804117 AND IRMDEP= 3552 AND IRMCIT BETWEEN 20190400 AND 20190402) OR
(IRMHIS= 14058310 AND IRMDEP= 3552 AND IRMCIT BETWEEN 20190401 AND 20190403) OR
FOOTER

-----

Instead of this:

-----

HEADER
(IRMHIS= 11631366 AND IRMDEP= 3590 AND IRMCIT BETWEEN 20190223 AND 20190225) OR
FOOTER

HEADER

(IRMHIS= 17804117 AND IRMDEP= 3552 AND IRMCIT BETWEEN 20190400 AND 20190402) OR
FOOTER

HEADER

(IRMHIS= 14058310 AND IRMDEP= 3552 AND IRMCIT BETWEEN 20190401 AND 20190403) OR
FOOTER

-----

I can imagine that the last OR can be erased on the fly, as well.

I know I'm performing unnecessary steps, as the cleanest solution would be FileMaker interacting directly with the AS400, but for now the Excel2FileMaker_SQL-DBeaver2AS400 is how I'm authorized to proceed.

Best,

Daniel

Edited April 11, 2019 by Quito

[comment]

I don't see what your starting point is - i.e. what does the data received from Excel look like. 

I am also not sure what is the final product you need to get: you speak of a script to connect to another application - do you really need to export a file for this purpose? I would think that producing the string you show as the contents of a variable would be more useful for this purpose, wouldn't it? And it would be fairly trivial, too: if each line of the "body" is the contents of a calculation field in a single record, then setting a variable to =

List ( "HEADER" ; YourTable::sListOfText ; "FOOTER" )

where sListOfText is a summary field defined as List of the calculation field, would produce the string above. 

Even simpler, we could dispense with both the calculation field and the summary field and have the script build the variable line-by-line as it loops among the records of the found set. Then it would be equally trivial to exclude the final "OR" when it processes the last record - i.e. when Get ( RecordNumber ) = Get ( FoundCount ).

 

In case you really need the result as a text file, you could place it in a global field and use the Export Field Contents script step to produce the file. And you would probably want to use the TextEncode() function to place it into a global container field, to avoid the fixed UTF-16 encoding when exporting a text field.

 

[Quito]

1 hour ago, comment said:

I don't see what your starting point is - i.e. what does the data received from Excel look like. 

-- The Excel file is a plain table with the names of the fields on the first row and one record to each row. FileMaker has no problem whatsoever when importing the file.

 I am also not sure what is the final product you need to get: you speak of a script to connect to another application - do you really need to export a file for this purpose? I would think that producing the string you show as the contents of a variable would be more useful for this purpose, wouldn't it? And it would be fairly trivial, too: if each line of the "body" is the contents of a calculation field in a single record, then setting a variable to =

List ( "HEADER" ; YourTable::sListOfText ; "FOOTER" )

where sListOfText is a summary field defined as List of the calculation field, would produce the string above. 

-- Indeed, that would be useful too.  I'm currently generating a SQL script with a list of about 500 records a week.  10% of the retrievals fail due to multiple factors out of my control, so it would be interesting to be able to tweak and run the script again on a case-by-case basis.

Even simpler, we could dispense with both the calculation field and the summary field and have the script build the variable line-by-line as it loops among the records of the found set. Then it would be equally trivial to exclude the final "OR" when it processes the last record - i.e. when Get ( RecordNumber ) = Get ( FoundCount ).

-- I'm going to have to read <<a lot>> to grok this.

In case you really need the result as a text file, you could place it in a global field and use the Export Field Contents script step to produce the file. And you would probably want to use the TextEncode() function to place it into a global container field, to avoid the fixed UTF-16 encoding when exporting a text field.

-- Seems this is going to be my first option

Thank you very much!

 

 

Edited April 11, 2019 by Quito

[comment]

35 minutes ago, Quito said:

-- I'm going to have to read <<a lot>> to grok this.

Have a look at the attached demo.

 

36 minutes ago, Quito said:

-- Seems this is going to be my first option

If the goal is to produce an actual file, then my first choice would be to export as XML and use a custom XSLT stylesheet to transform the export into the desired format.

BuildQuery.fmp12

[Quito]

What an elegant solution!  Here's my Script Workspace with everything in it.

Thank you very much Comment, I really appreciate it.