Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server (Not)   Client   Old   Guides   Examples
New in version: 6.0   6.1   6.2   6.3   6.4   6.5   7.0   7.1   7.2   7.3    Statistic  

FM.SQL.CSV

Queries text of SQL result for CSV export.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
FM FMSQL 7.0 Yes Yes Yes Yes Yes

MBS( "FM.SQL.CSV"; SQLref { ; firstRow; lastRow; firstCol; lastCol; rowSeparator; colSeparator } )

Parameters

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

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.

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


FM.RunScriptLater   -   FM.SQL.Execute

Feedback: Report problem or ask question.




Links
MBS Xojo Plugins