Topics   All   MacOS (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Deprecated   Guides   Examples   Videos
New in version: 10.1   10.2   10.3   10.4   10.5   11.0   11.1   11.2   11.3   11.4    Statistic    FMM    Blog  

FM.SQL.CSV

Queries text of SQL result for CSV export.

Component Version macOS Windows Linux Server iOS SDK
FM FMSQL 7.0 ✅ Yes ✅ Yes ✅ Yes ✅ Yes ✅ Yes
MBS( "FM.SQL.CSV"; SQLref { ; firstRow; lastRow; firstCol; lastCol; rowSeparator; colSeparator } )   More

Parameters

Parameter Description Example Flags
SQLref The reference number returned by FM.SQL.Execute function. $SQLRef
firstRow The index of first row.
Default is 0.
0 Optional
lastRow The index of last row.
Default is FM.SQL.RowCount-1 if no value is given.
5 Optional
firstCol The index of first column.
Default is 0.
0 Optional
lastCol The index of last column.
Default is FM.SQL.FieldCount-1 if no value is given.
6 Optional
rowSeparator The row separator.
Default is CR.
Char(13) Optional
colSeparator The column separator.
Default is semicolon.
";" Optional

Result

Returns text or error.

Description

Queries text of SQL result for CSV export.
You can use this method to quickly get all fields in a given area as text.
If needed, we put values in quotes and escape quotes and newlines.

For numeric values we ask FileMaker to provide text representation, so FileMaker may use dot or comma as decimal divider depending on your locale.

Please use Text.WriteTextFile to write to text file if you need CSV export.

Examples

Query fields and export as text and CSV:

Set Variable [$sql; Value:MBS( "FM.SQL.Execute"; ""; "select * from Clients")]
If [MBS("IsError")]
    Show Custom Dialog ["SQL error"; $sql]
Else
    Show Custom Dialog ["SQL result"; "got " & MBS( "FM.SQL.RowCount"; $sql ) &" rows with each " & MBS( "FM.SQL.FieldCount"; $sql ) & " …"]
    # Write to text file
    Set Variable [$text; Value:MBS( "FM.SQL.Text"; $sql )]
    Set Variable [$desktopFolder; Value:MBS( "Folders.UserDesktop" )]
    Set Variable [$path; Value:MBS( "Path.AddPathComponent"; $desktopFolder; "fields.txt" )]
    Set Variable [$r; Value:MBS( "Text.WriteTextFile"; $text; $path; "UTF8")]
    # Write to text file
    Set Variable [$text; Value:MBS( "FM.SQL.CSV"; $sql )]
    Set Variable [$path; Value:MBS( "Path.AddPathComponent"; $desktopFolder; "fields.csv" )]
    Set Variable [$r; Value:MBS( "Text.WriteTextFile"; $text; $path; "UTF8")]
    # Cleanup
    Set Variable [$r; Value:MBS( "FM.SQL.Release"; $sql )]
End If

See also

Release notes

Blog Entries

FileMaker Magazin

Created 8th January 2017, last changed 4th June 2021


FM.SQL.Avg - FM.SQL.Execute

Feedback: Report problem or ask question.




Links
MBS Xojo PDF Plugins

Start Chat