« Formatted Text Contro… | Home | MBS Xojo Plugins, ver… »

CSV functions in MBS FileMaker Plugin

If you need to work with CSV data in FileMaker, the MBS FileMaker Plugin has four functions which may be interesting for you.

First, to get CSV data to export, you can use FM.SQL.Execute to run a SQL query. In the query, you can name the columns you need. With clever expressions you can cast them to data type you need, or concat values as needed. In the where clause you can select which data you need. For example this query looks up first and last name, includes an ID 123 and concats zip and city name to one field. Finally it does ordering with descending order:

$records = MBS("FM.SQL.Execute"; ""; "SELECT \"FirstName\", \"LastName\", '123', \"Zip\"+ ' ' + \"City\" FROM Addresses WHERE City=? ORDER BY LastName DESC"; "New York")

Than you have the records in a handle and with FM.SQL.Text function you can query all or just a part of the rows. You can configure which row and column separators to use. With Text.WriteTextFile you can write the text to a file if needed.

Second, when you have CSV data, you can use List.CSVSplit or QuickList.CSVSplit to split a line of CSV data and auto detect the delimiter. This allows you to process line by line in a text file and do something useful.

Third, to simply import CSV data into records, you can use our FM.InsertRecordCSV function. There we take a block of text to split it in lines and each line with the List.CSVSplit internally into the values. You pass in file name, table name and field names, so we can build the SQL functions for you and do the insert statements.
01 11 17 - 13:23