The gist for this lambda function can be found here.

If you sometimes need to quickly put some Excel data into a SQL table or use the data in a CTE, you may have found yourself doing something like this:

Here’s a LAMBDA I’ve called SQLVALUES:

=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)&”)”))

This will:

  1. Wrap the tuple in parentheses
  2. Wrap text and dates in single-quotes
  3. Replace embedded single-quotes with escaped single-quotes
  4. Separate the columns with commas
  5. Format date-formatted cells as YYYY-MM-DD HH:mm:ss

If we’re inserting multiple values and our SQL database supports a list of tuples, we can also do this:

=LET(arr,A2:C6,BYROW(arr,SQLVALUES)&IF(LASTROW(arr),”;”,”,”))

Which is saying “Apply the SQLVALUES lambda to each row in arr. If the row of arr is the last row, put a semi-colon after it. Otherwise, put a comma after the row”.

LASTROW just takes an array and returns an array of TRUE/FALSE the same size as array. Here’s the LAMBDA for LASTROW:

=LAMBDA(d,ROW(d)=(ROWS(d)+MIN(ROW(d))-1))

You can now paste the data from the spreadsheet directly into your SQL editor.

I’m sure SQLVALUES is not perfect. I suspect there are edge cases it won’t cover, but hopefully it demonstrates a way to shortcut a task using array formulas and LAMBDA.

Do you have any suggestions for improvement to the SQLVALUES LAMBDA?

 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>