I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I say
the .txt file is huge? Manually this would take at least one day worth of
work :)

Thanks in advance
Michael

Quite simple, try this for starters:


Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\Test.txt")

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


Function OpenTextFileToString(strFile As String) As String

Dim hFile As Long

hFile = FreeFile

Open strFile For Input As #hFile

OpenTextFileToString = Input$(LOF(hFile), hFile)

Close #hFile

End Function


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day worth
of work :)

Thanks in advance
Michael

You will need this if you have joined tables:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\Test.txt")

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day worth
of work :)

Thanks in advance
Michael

It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day worth
of work :)

Thanks in advance
Michael

RBS,
OK, I give up, just what is "OpenTextFileToString" ?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"RB Smissaert"
wrote in message
It probably will need a bit further cleaning up like this:

Sub GetTables()
Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i
End Sub

That might be enough and it will work.
RBS

Function OpenTextFileToString(strFile As String) As String

Dim hFile As Long

hFile = FreeFile

Open strFile For Input As #hFile

OpenTextFileToString = Input$(LOF(hFile), hFile)

Close #hFile

End Function


As mentioned in the first reply.


RBS


"Jim Cone" wrote in message
...

RBS,
OK, I give up, just what is "OpenTextFileToString" ?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"RB Smissaert"
wrote in message
It probably will need a bit further cleaning up like this:

Sub GetTables()
Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i
End Sub

That might be enough and it will work.
RBS

I just plain missed it.
Thanks.
Jim Cone



"RB Smissaert"
wrote in message
Function OpenTextFileToString(strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString = Input$(LOF(hFile), hFile)
Close #hFile
End Function

As mentioned in the first reply.
RBS

No trouble.
Unless there is some strange SQL I think it should work.

RBS

"Jim Cone" wrote in message
...

I just plain missed it.
Thanks.
Jim Cone



"RB Smissaert"
wrote in message
Function OpenTextFileToString(strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString = Input$(LOF(hFile), hFile)
Close #hFile
End Function

As mentioned in the first reply.
RBS

With the SQL cleaned up you might as well instead do:

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
'Debug.Print arr(i + 1)
End If
Next i


RBS


"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as = Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from Table1,
Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS


"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as = Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as = Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did I
say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

Yes, the one I posted yesterday, but will repeat again incase you can't see
it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as = Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did
I say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

Thank you so much, It worked just great.

-- Michalis

"RB Smissaert" wrote in message
...
Yes, the one I posted yesterday, but will repeat again incase you can't
see it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where
dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as =
Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel. Did
I say the .txt file is huge? Manually this would take at least one day
worth of work :)

Thanks in advance
Michael

No trouble, one day I may need it myself.

RBS


"Michael" wrote in message
...
Thank you so much, It worked just great.

-- Michalis

"RB Smissaert" wrote in message
...
Yes, the one I posted yesterday, but will repeat again incase you can't
see it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where
dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as =
Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel.
Did I say the .txt file is huge? Manually this would take at least
one day worth of work :)

Thanks in advance
Michael

Thanks! By the way I got your code and I wanted to extend it a little bit
further. Specifically I would like to be able to parse all tables that are
used to insert data into them as well.

Here is the sample text file I have

STRING STRING STRING select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table4.as = Table5.asas
INSERT INTO SomeOtherTable (ID1, ID2, ID3) VALUES (1111, 'EWEEE',
TO_DATE('15/10/2001 9:10:7', 'dd/mm/yyyy hh24:mi:ss')) : INSERT INTO
SomeOtherTable2(CCID, INTL_CCID2) VALUES (1, '1') Select * from Table1
where dsaf.fds=54324fsd.df dadasd select as, asas, from Table4, Table5 where
Table6.as = Table7.asas afdsafdsa fsdfdsfsa fdsafdsa


After parsing this sample text file above I should be able to produce the
following list of tables:


Table1
Table2
Table3
SomeOtherTable
SomeOtherTable2
Table1
Table4
Table5


I modified your code, however I still have some minor issues :)


"RB Smissaert" wrote in message
...
No trouble, one day I may need it myself.

RBS


"Michael" wrote in message
...
Thank you so much, It worked just great.

-- Michalis

"RB Smissaert" wrote in message
...
Yes, the one I posted yesterday, but will repeat again incase you can't
see it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where
dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as =
Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel.
Did I say the .txt file is huge? Manually this would take at least
one day worth of work :)

Thanks in advance
Michael

Thanks! By the way I got your code and I wanted to extend it a little bit
further. Specifically I would like to be able to parse all tables that are
used to insert data into them as well.

Here is the sample text file I have

STRING STRING STRING select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table4.as = Table5.asas
INSERT INTO SomeOtherTable (ID1, ID2, ID3) VALUES (1111, 'EWEEE',
TO_DATE('15/10/2001 9:10:7', 'dd/mm/yyyy hh24:mi:ss')) : INSERT INTO
SomeOtherTable2(CCID, INTL_CCID2) VALUES (1, '1') Select * from Table1
where dsaf.fds=54324fsd.df dadasd select as, asas, from Table4, Table5 where
Table6.as = Table7.asas afdsafdsa fsdfdsfsa fdsafdsa


After parsing this sample text file above I should be able to produce the
following list of tables:


Table1
Table2
Table3
SomeOtherTable
SomeOtherTable2
Table1
Table4
Table5


Thanks
Michael

"RB Smissaert" wrote in message
...
No trouble, one day I may need it myself.

RBS


"Michael" wrote in message
...
Thank you so much, It worked just great.

-- Michalis

"RB Smissaert" wrote in message
...
Yes, the one I posted yesterday, but will repeat again incase you can't
see it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have the
following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where
dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as =
Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel.
Did I say the .txt file is huge? Manually this would take at least
one day worth of work :)

Thanks in advance
Michael

however I still have some minor issues

What issues?

RBS


"Michael" wrote in message
...
Thanks! By the way I got your code and I wanted to extend it a little bit
further. Specifically I would like to be able to parse all tables that are
used to insert data into them as well.

Here is the sample text file I have

STRING STRING STRING select * from Table1 where dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table4.as = Table5.asas
INSERT INTO SomeOtherTable (ID1, ID2, ID3) VALUES (1111, 'EWEEE',
TO_DATE('15/10/2001 9:10:7', 'dd/mm/yyyy hh24:mi:ss')) : INSERT INTO
SomeOtherTable2(CCID, INTL_CCID2) VALUES (1, '1') Select * from Table1
where dsaf.fds=54324fsd.df dadasd select as, asas, from Table4, Table5
where Table6.as = Table7.asas afdsafdsa fsdfdsfsa fdsafdsa


After parsing this sample text file above I should be able to produce the
following list of tables:


Table1
Table2
Table3
SomeOtherTable
SomeOtherTable2
Table1
Table4
Table5


I modified your code, however I still have some minor issues :)


"RB Smissaert" wrote in message
...
No trouble, one day I may need it myself.

RBS


"Michael" wrote in message
...
Thank you so much, It worked just great.

-- Michalis

"RB Smissaert" wrote in message
...
Yes, the one I posted yesterday, but will repeat again incase you can't
see it:

-------------------------------------------------------
OK, you need a little modification:


Sub GetTables()

Dim i As Long
Dim n As Long
Dim strText As String
Dim collTables As Collection
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, ",", Chr(32) & "," & Chr(32), , ,
vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

Set collTables = New Collection

n = 1

For i = 0 To UBound(arr) - 1
If UCase(arr(i)) = "FROM" Or _
UCase(arr(i)) = "JOIN" Then
'this could be used to only get unique tables
'by doing: collTables.Add arr(i + 1), CStr(arr(i, 1))
'and doing On Error Resume Next before the outer loop
'----------------------------------------------------
collTables.Add arr(i + 1)
Do While arr(i + 1 + n) = ","
collTables.Add arr(i + n + 2)
n = n + 2
If i + n + 1 UBound(arr) Then
Exit For
End If
Loop
End If
Next i

For i = 1 To collTables.Count
Debug.Print collTables(i)
Next i

End Sub


Show the Debug data by doing View, Immediate Window


RBS

"Michael" wrote in message
...
RB Smissaert any hints for this?

Thanks
Michael

"Michael" wrote in message
...
Thank you so much. This works for the most part although if I have
the following test.txt it will only produce Table1 and Table2,

I was hoping it would return Table1 Table2 and Table3

dsadas fdsafsdf fdsfsad select * from Table1 where
dsaf.fds=54324fsd.df
dadasd select as, asas, from Table2, Table3 where Table2.as =
Table3.asas
afdsafdsa fsdfdsfsa fdsafdsa

Thanks
Michael

"RB Smissaert" wrote in message
...
It probably will need a bit further cleaning up like this:

Sub GetTables()

Dim i As Long
Dim strText As String
Dim arr

strText = OpenTextFileToString("C:\test.txt")

strText = Replace(strText, Chr(10), Chr(32), , , vbBinaryCompare)
strText = Replace(strText, Chr(13), Chr(32), , , vbBinaryCompare)

Do While InStr(1, strText, Chr(32) & Chr(32), vbBinaryCompare) 0
strText = Replace(strText, Chr(32) & Chr(32), Chr(32), , ,
vbBinaryCompare)
Loop

arr = Split(strText, Chr(32), , vbBinaryCompare)

For i = 0 To UBound(arr) - 1
If InStr(1, UCase(arr(i)), "FROM", vbBinaryCompare) 0 Or _
InStr(1, UCase(arr(i)), "JOIN", vbBinaryCompare) 0 Then
Cells(65536, 1).End(xlUp).Offset(1, 0) = arr(i + 1)
End If
Next i

End Sub


That might be enough and it will work.


RBS


"Michael" wrote in message
...
I want to use Excel to parse a .txt file (imported in Excel).

The text file has strings sunch as "select column1, column 2 from
Table1, Table2 where column1 = < some values"

The text is not delimited nor fixed width. It's very dynamic.


I want to use Excel to identify all the Tables that are used in SQL
statements through out the .txt file and generate a list in Excel.
Did I say the .txt file is huge? Manually this would take at least
one day worth of work :)

Thanks in advance
Michael