The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

MBS Blog – 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.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy