Post reply

Excel Formula to create SQL Script for a Values Table

  • July 26, 2021 at 6:29 pm

    #3914037

    Comments posted to this topic are about the item Excel Formula to create SQL Script for a Values Table

  • July 28, 2021 at 8:01 am

    #3914563

    I just noticed that you need the table to start in column 1 for this to work properly. Although here is a version that works if the table starts at any column (it checks if the column is the min column in the table to add the first values bracket and checks if it is the last column (min column number + column count minus 1) to see if it needs the bracket at the end of the value group.

    ="SELECT "&CHAR(10)&" ["&TEXTJOIN("],[",FALSE,Table1[#Headers])&"]"&"

    FROM

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

  • April 7, 2022 at 8:10 am

    #4012067

    Hi, well for a beginner it is not so much self explanotory. I have Excel on French reginol settings, the formula generates an error. I maneged to correctly issue the formula up to (; instead of ,)

    ="Select " & " [" & TEXTJOIN("],[";FALSE;Table2[#Headers]) & "]" & " From (Values " which gives

                               Select [Num],[Val],[Notes] From (Values

    Can you please explain how we get the values and what the following 2 phrases mean

    & TEXTJOIN(",",FALSE,IF(COLUMN(Table2)=1,CHAR(10)

    and

    & "('","'")&SUBSTITUTE(Table2,"'","''")

     

    Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply