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