Excel tip: from tabular data to SQL statements with CONCAT function

Sometimes, you have data in an Excel spreadsheet that needs to go into a SQL database. For instance, string values with an id number as a primary key. Surely, you could write some C# to import the data into your SQL database (SQL Server, SQLite, etc.), but that could be overkill.

Instead, you can use simple Excel string concatenation functions to craft an SQL INSERT or UPDATE statement directly in Excel, and then utilize cell values inside the data. Assume for example you had a table like this in Excel:

Column A      Column B
123	      ABC
234           BCD

With this data in place, you could create an Excel CONCAT function in the column C like this:

=CONCAT("UPDATE MyTable SET SomeCol = '"; B1; "' WHERE MyPrimaryKey = "; A1)

Here, in column C, Excel constructs an SQL UPDATE statement that sets SomeCol column's value to the value in cell B1, where the primary key of the table matches the value in A1.

Now, if you simply copy this formula over all rows of your data, you get a set of SQL statements you can directly run with the correct data.

Simple and efficient.