excel vba - transform an sql

Question

Has anyone had any luck with a vba macro that would convert this input:

update my_table 
    set time = sysdate, 
    randfield1 = 'FAKE', 
    randfield5 = 'ME', 
    the_field8 = 'test' 
    where my_key = '84' 
    ;

into this output?

select count(*) from my_table
where (randfield1 <> 'FAKE'
or randfield5 <> 'ME'
or the_field8 <> 'TEST')
and my_key = '84';

update (what happens when using Remou's answer):

INPUT (what i have place in cell A1 of first sheet)-

update my_table  
    set time = sysdate,  
    randfield1 = 'FAKE',  
    randfield5 = 'ME',  
    the_field8 = 'test'  
    where my_key = '84'  
    ;

OUTPUT (what is generated in a1 of the 2nd sheet once the macro is run)-

SELECT Count(*) FROM  my_table
WHERE ()
)
)
)
)
)
)
)
randfield1 <> 'FAKE'
OR )
)
)
)
randfield5 <> 'ME'
OR )
)
)
)
the_field8 <> 'test')
)
)
)
)
AND my_key = '84'
;

Three questions on the same topic? stackoverflow.com/questions/7515490/…, stackoverflow.com/questions/7515945/… and this one? — Fionnuala
– Fionnuala, Commented Sep 22, 2011 at 17:08
@Remou - you can ignore the other qestions but there is a bounty available to sweeten the deal — toop
– toop, Commented Oct 5, 2011 at 11:01

Fionnuala · Accepted Answer · 2011-10-09 08:59:03Z

2

+50

I am still not sure what you want, but anyway:

Dim r As Range
Dim cl As Range
Dim s As String
Dim c As String
Dim arys As Variant
Dim i As Long, j As Long

''Assuming an existing worksheet
Set r = Sheet1.UsedRange
j = Sheet2.UsedRange.Rows.Count

For Each cl In r.Cells
    c = cl.Value

    ''Fake spaces
    Do While InStr(c, Chr(160)) > 0
        c = Replace(c, Chr(160), "")
    Loop

    ''Real spaces
    c = Trim(c)

    If c = ";" Then

        arys = Split(s, vbCrLf)

        For i = 0 To UBound(arys)
            Sheet2.Cells(j, 1) = arys(i)
            j = j + 1
        Next

        ''Layout
        j = j + 2

    ElseIf UCase(c) Like "UPDATE*" Then

        s = "SELECT Count(*) FROM " & Replace(c, "update", "", , , vbTextCompare)
        s = s & vbCrLf & "WHERE ("

    ElseIf UCase(c) Like "WHERE*" Then
        s = s & Replace(c, "where", "AND", , , vbTextCompare)
        s = s & vbCrLf & ";"

    ElseIf Left(UCase(c), 3) <> "SET" Then
        c = Replace(c, "=", "<>")

        If Right(c, 1) = "," Then
            s = s & Left(c, Len(c) - 1) & vbCrLf & "OR "
        Else
            s = s & c & ")" & vbCrLf
        End If

    End If
Next

edited Oct 9, 2011 at 8:59

answered Oct 5, 2011 at 12:25

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

toop Over a year ago

+1 almost there! seems to be adding additional right brackets: )'s. ie OR ). Also, is adding commas: ,s. Apart from that it matches the desired output mentioned in the question.

Fionnuala Over a year ago

@toop I have corrected the comma, which was an oversight, but I cannot see an extra bracket. What is your input and output?

toop Over a year ago

Please see my edited question - it has the input and output I'm getting when using your code.

Fionnuala Over a year ago

I have cut and pasted your input into a new Excel document and my macro into a new module and I get nothing like that. It seems you may have unexpected characters in your sheet. Do you have anywhere you can upload your workbook?

toop Over a year ago

It worked in a new workbook, must have been a problem with the old workbook. Anyway, just before I give you the bounty, can you make it support multiple queries. So if the input was 2 or more queries that the transformation would produce all of them?

|

onedaywhen · Accepted Answer · 2011-10-07 09:22:59Z

Well, this does at least work for your sample input...

Sub NotExtensibleInTheLeast()

  Dim sql As String

  sql = _
      "update my_table " & Chr$(10) & _
      "    set time = sysdate, " & Chr$(10) & _
      "    randfield1 = 'FAKE', " & Chr$(10) & _
      "    randfield5 = 'ME', " & Chr$(10) & _
      "    the_field8 = 'test' " & Chr$(10) & _
      "    where my_key = '84' " & Chr$(10) & _
      "    ;"

  Dim newSql
  newSql = sql
  newSql = Replace$(newSql, "where", ") and")
  newSql = Replace$(newSql, "update", "select count(*) from")
  newSql = Replace$(newSql, "set", "where (")
  newSql = Excel.Application.WorksheetFunction.Substitute(newSql, "=", Chr$(22), 5)
  newSql = Replace$(newSql, "=", "<>")
  newSql = Replace$(newSql, Chr$(22), "=")
  newSql = Replace$(newSql, ",", " or ")
  newSql = Replace$(newSql, "time <> sysdate or", vbNullString)

  MsgBox newSql

End Sub

Collectives™ on Stack Overflow

excel vba - transform an sql

update (what happens when using Remou's answer):

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

update (what happens when using Remou's answer):

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related