« The VariantTypeString… | Home | FileMaker Magazin - M… »

Import CSV with Matrix functions

Let us show you how to use Matrix.CSVSplit function for a custom CSV import. For example you may have a text file and read it with our Text.ReadTextFile function. Here it is important to know the text encoding to expect. Usually nowadays everyone uses UTF-8 except if you get data from ancient database systems with some Latin 1 or Windows ANSI encoding. Once you have some text, you may want to normalize line endings with Text.ReplaceNewline function.

 

Next you call Matrix.CSVSplit function to split the CSV text. We can pass the semicolon as delimiter. If you don't specify one, we auto detect whether it is comma, semicolon or tab character. But you can pass any delimiter you like here, e.g. # character. Once import is done, we can use Matrix.Height and Matrix.Width functions to query the size of the matrix we got. First row is the name of the fields. You may use the fields listed in the CSV later or bring your own field list for the insert operation later. And as we don't like to insert the field names, we remove first row with Matrix.RemoveRow function.

 

The magic to do inserts into your table is done with our Matrix.InsertRecords function. It creates internally an SQL statement to for insert operations. Then it walks over the matrix and runs SQL statement to insert records. If everything is fine, the function returns OK. Finally we can release the matrix object with the Matrix.Release function. You can see the SQL statement by calling FM.ExecuteSQL.LastSQL function.


Here is the sample script:

 

# native file path

Set Variable [ $path ; Value: "/Users/cs/Desktop/test.csv" ] 

# read the file

Set Variable [ $text ; Value: MBS( "Text.ReadTextFile"; $path; "UTF-8") ] 

# change line endings to make sure it's ¶ for FileMaker

Set Variable [ $text ; Value: MBS( "Text.ReplaceNewline"; $Text; 1 ) ] 

# Split CSV

Set Variable [ $matrix ; Value: MBS( "Matrix.CSVSplit"; $text; ";") ] 

# query height

Set Variable [ $count ; Value: MBS( "Matrix.Height"; $matrix) ] 

Show Custom Dialog [ "Number of rows" ; $count ] 

# take first rows with field names

Set Variable [ $firstRow ; Value: MBS( "Matrix.GetRow"; $matrix; 0 ) ] 

Set Variable [ $r ; Value: MBS( "Matrix.RemoveRow"; $matrix; 0 ) ] 

Show Custom Dialog [ "Fields in CSV" ; $firstRow ] 

# you may use a different field list for FileMaker (or the one from CSV)

Set Variable [ $fields ; Value: "Name¶Price" ] 

# insert records to our Assets table

Set Variable [ $r ; Value: MBS( "Matrix.InsertRecords"; $matrix; Get(FileName); "Assets"; $fields) ] 

# free memory

Set Variable [ $r ; Value: MBS("Matrix.Release"; $matrix) ] 

 

For next plugin version we already got an improvement: While plugin version 10.5 can only do text fields in FileMaker, the next version will detect field type and convert data if needed.

Please do not hesitate to contact us if you have questions.

20 11 20 - 09:46