CSV from delimited SQL results

Tuesday 18th March, 2014

Here are a couple of useful little FileMaker custom functions for when you need to move data from the result of an ExecuteSQL query to CSV or JavaScript. Which in the current job, is something I will be probably be doing frequently.

First, a function that takes a delimited string, say

CityExpress|123||4.5|Yes

and converts it into a CSV string such as this,

'CityExpress',123,'',4.5,'Yes'

taking care to encode for “'” and non-numeric data.

/*
DelimitedStringToCSV ( String ; Delimiter )
*/

Case(
// return single quotes for empty string
String = "" ; "''" ;

// return single quotes comma for string beginning with '|'
Left ( String ; 1 ) = Delimiter ; "''," &
    DelimitedStringToCSV( Middle( String ; 2 ; 100000 ) ; Delimiter ) ;

// if we have further parts to go
PatternCount ( String ; Delimiter ) > 0 ;
    Let (
    [
      first = Left ( String ; Position ( String ; Delimiter ; 1 ; 1 ) - 1 );
      rest = Middle ( String ; Position ( String ; Delimiter ; 1 ; 1 ) + 1 ; 1000000 );
      encapsed = If (
        Filter ( first ; "0123456789." ) = first ;
        first ;
        "'"& Substitute( first ; [ "'" ; "\'" ] ) &"'"
      )
    ];
    encapsed & "," & DelimitedStringToCSV( rest ; Delimiter )
    );

// Tail-end charlie
If (
  Filter ( String ; "0123456789." ) = String ;
  String ;
  "'"& Substitute( String ; [ "'" ; "\'" ] ) &"'"
)

)

Next, a something that will turn a value list of such delimited data, into a JavaScript array. At some point, yes, I should probably make it more generic and allow object creation…

It takes some goobledigook like this,

StringListToJSArray ( "XYZ|231|229|.2974|23|131∂RJD|5737|8|.0712|12|12∂O'Bongo|4429|240|.0491||∂DEF|54432|128|.1476|231|2" ; "|" )

and turns it into the oh so more readable,

['XYZ',231,229,.2974,23,131],
['RJD',5737,8,.0712,12,12],
['O\'Bongo',4429,240,.0491,'',''],
['DEF',54432,128,.1476,231,2]

which will of course need to be enclosed in “[ ]” to make it a JavaScript array of arrays.

/*
StringListToJSArray ( String ; Delimiter )
*/

Case(
  // return empty string for empty list
  ValueCount( String ) = 0 ; "" ;

  // return single quotes comma for string beginning with '|'
  ValueCount( String ) = 1 ; "[" & DelimitedStringToCSV ( Substitute( String ; [∂ ; ""] ) ; Delimiter ) & "]";

  // if we have more than one value
  List( "[" & DelimitedStringToCSV ( Substitute( LeftValues( String ; 1 ) ; [∂ ; ""] ); Delimiter ) & "]," ; StringListToJSArray( MiddleValues( String ; 2 ; 10000 ) ; Delimiter ) )

)

Let me know when you find a bug!