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:
- Wrap the tuple in parentheses
- Wrap text and dates in single-quotes
- Replace embedded single-quotes with escaped single-quotes
- Separate the columns with commas
- 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:
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:
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?