Monkeybread Software - MBS FileMaker Plugin: FM.SQL.CSV

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: 9.3 9.4 9.5 10.0 10.1 10.2 10.3 10.4 10.5 10.6 Statistic FMM Blog

FM.SQL.CSV

Queries text of SQL result for CSV export.

Component	Version	macOS	Windows	Linux	Server	FileMaker 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. 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. 6 Optional

rowSeparator The row separator.
Default is CR. Char(13) Optional

colSeparator The column separator.
Default is semicolon. ";" Optional

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.	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.	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

Blog Entries

FileMaker Magazin

Ausgabe 3/2018, Seite 34, PDF

Release notes

Version 8.2
- Improved FM.SQL.CSV function to no longer quote numbers.
Version 7.0
- Added FM.SQL.Text and FM.SQL.CSV.

Created 8th January 2017, last changed 1st May 2020

FM.SQL.Avg - FM.SQL.Execute

Feedback: Report problem or ask question.