This gist contains several Excel lambda functions for preparing Excel data for use in SQL statements such as INSERT/VALUES and WHERE/IN

excel-lambda.SQL.PREP.txt

/*
SQL.TYPE

Returns an array of values from the set {"text","date","number"} indicating how a cell's value should be formatted for use in a SQL statement

Inputs:
	- in_list: a 1-dimensional array or range, which can be horizontal or vertical

Return:
  an array the same size as in_list, containing:
    "text" if the element is text
    "date" if the element is from a cell formatted as a date (i.e. LEFT(CELL("format",A1),1)="D")
    "number" otherwise
  
  If the input array is not 1-dimensional, returns NA()
*/

SQL.TYPE =LAMBDA(in_list,
  IF(
    AND(ROWS(in_list)<>1,COLUMNS(in_list)<>1),
    NA(),
    LET(
     _list,in_list,
     _txt,ISTEXT(_list),
     _formats,MAP(_list,LAMBDA(r,CELL("format",INDEX(r,1,1)))),
     _isdates,LEFT(_formats,1)="D",
     _out,MAKEARRAY(
            ROWS(_list),
            COLUMNS(_list),
            LAMBDA(r,c,
              IFS(
                INDEX(_txt,r,c),"text",
                INDEX(_isdates,r,c),"date",
                TRUE,"number"
              )
            )
          ),
     FILTER(_out,in_list<>"")
    )
  )
);


/*
SQL.LISTTYPE

The use for this function is to determine how an array should be formatted when creating an 'IN (list)' clause. Therefore, if the user selects data containing text, each element must be wrapped in single-quotes. If they contain dates, they must be formatted appropriately, otherwise they do not need single-quotes (they are a number). 

Applies the SQL.TYPE lambda to an array, then returns the most strict type where text > date > number. The assumption here is that if the user has selected data of mixed types, then we should choose the most strict format. In this case, wrapping a date or a number in single-quotes will not necessarily cause an error in the SQL engine when passing something like "IN ('text','12345')" to a sql statement, whereas failing to wrap a text value in single-quotes will definitely cause an error. This will fail: "IN (text,12345)"

Inputs:
	- in_list: a 1-dimensional array or range, which can be horizontal or vertical

Return:
  a single value of the most strict type present in the list as determined by SQL.TYPE (defined above)
  
  If the input array is not 1-dimensional, returns NA()
*/


SQL.LISTTYPE =LAMBDA(in_list,
  LET(
    _list,SQL.TYPE(in_list),
    _has,LAMBDA(x,y,NOT(ISERROR(XMATCH(x,y)))),
    _listerror,OR(ISERROR(_list)),
    _hastxt,_has("text",_list),
    _hasdate,_has("date",_list),
    _out,IFS(_listerror,NA(),_hastxt,"text",_hasdate,"date",TRUE,"number"),
    _out
  )
);




/*
SQL.IN

Format a row of data for a SQL IN clause

Inputs:
	- in_list: a 1-dimensional array or range, which can be horizontal or vertical

Return:
	A string representation of a list suitable for use in a SQL IN clause, formatted according to the most strict type present in the input array as determined by SQL.LISTTYPE (defined above)
	
  If the input array is not 1-dimensional, returns #VALUE!
*/

SQL.IN =LAMBDA(in_list,
  LET(
    _list,IF(ROWS(in_list)=1,TRANSPOSE(in_list),in_list),
    _type,SQL.LISTTYPE(_list),
    _list_no_blank,FILTER(_list,NOT(ISBLANK(_list))),
    _list_modified,IFS(
                    _type="text","'"&SUBSTITUTE(_list_no_blank,"'","''")&"'",
                    _type="date",TEXT(_list_no_blank,"'YYYY-MM-DD HH:mm:ss'"),
                    TRUE,_list_no_blank
                   ),
    _out,"IN ("&TEXTJOIN(",",FALSE,_list_modified)&")",
    _out
  )
);




/*
SQL.VALUES

Format a row of data for a SQL VALUES clause.

Inputs:
	- t: a 1-dimensional horizontal array or range

Return:
	A string representation of a tuple suitable for use in a SQL INSERT statement.
	
	Suppose we have this array:
		{1,4/8/2022,1.569,,"Some text",56}
		
	Then SQLVALUES will return this in a single cell:
		(1,'2022-04-08 00:00:00',1.569,NULL,'Some text',56)
*/

SQL.VALUES =LAMBDA(t,
  LET(
    d,IFS(
      ISTEXT(t),"'"&SUBSTITUTE(t,"'","''")&"'",
      ISBLANK(t),"NULL",
      LEFT(MAP(t,LAMBDA(x,CELL("format",x))),1)="D",TEXT(t,"'YYYY-MM-DD HH:mm:ss'"),
      TRUE,t),
    "("&TEXTJOIN(",",FALSE,d)&")"
    )
);


/*
SQL.VALUESLIST

Applies SQL.VALUES to a range, prepends the VALUES keyword, appends a comma to the end of each tuple and a semi-colon to the end of the list

Inputs:
	- arr: an array or range

Return:
	A list of tuples suitable for a SQL INSERT/VALUES statement

*/

SQL.VALUESLIST =LAMBDA(arr,
  LET(
    _lastrow,LAMBDA(d,ROW(d)=(ROWS(d)+MIN(ROW(d))-1)),
    _firstrow,LAMBDA(d,ROW(d)=MIN(ROW(d))),
    IF(_firstrow(arr),"VALUES ","")&BYROW(arr,SQL.VALUES)&IF(_lastrow(arr),";",",")
  )
);