Excel Formula to create SQL Script for a Values Table

,

Here is an excel formula that generates a sql script from an excel table named "Table1" which is the default table name in excel.

It is an array formula so don't forget to hit ctr+shift+enter to make it work.

="SELECT "&CHAR(10)&" ["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"]"&"
FROM
(VALUES "&TEXTJOIN(",",FALSE,IF(COLUMN(Table1)=1,CHAR(10)&"('","'")&SUBSTITUTE(Table1,"'","''")&IF(COLUMN(Table1)=COLUMNS(Table1),"')","'"))&") t1 (["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"])"

So if your Table1 looks like the below.

NumValNotes
6TheBeware
7Quick
8BrownThe Jabberwock
9Fox
10Jumpedmy son
11Over
12The
13Lazy\**\
14Dog/td>
15Didn't?
16HeWith teeth that bite
17Doand claws that catch
18Well

And you enter the script you will get some SQL that looks like this (it will have double quotes around the whole thing but that is easy enough to remove)

SELECT 
 [Num],[Val],[Notes]
FROM
(VALUES 
('6','The','Beware'),
('7','Quick',''),
('8','Brown','The Jabberwock'),
('9','Fox',''),
('10','Jumped','my son'),
('11','Over',''),
('12','The',''),
('13','Lazy','\**\'),
('14','Dog',''),
('15','Didn''t','?'),
('16','He','With teeth that bite'),
('17','Do','and claws that catch'),
('18','Well','')) t1 ([Num],[Val],[Notes])

I hope this helps. I think the formula is fairly self explanatory and obviously it doesn't handle typing but it allows you to throw an excel table into a sql editor and play with the data nice and quickly.

 

="SELECT "&CHAR(10)&" ["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"]"&"
FROM
(VALUES "&TEXTJOIN(",",FALSE,IF(COLUMN(Table1)=1,CHAR(10)&"('","'")&SUBSTITUTE(Table1,"'","''")&IF(COLUMN(Table1)=COLUMNS(Table1),"')","'"))&") t1 (["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"])"

Rate

5 (1)

Share

Share

Rate

5 (1)