Want to use Excel to parse a text file

[Michael]

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]

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

 

[RB Smissaert]

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

 

[RB Smissaert]

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

 

[Jim Cone]

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

 

[RB Smissaert]

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]

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

 

[RB Smissaert]

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

RBS

 

[RB Smissaert]

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

 

[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]

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]

RB Smissaert any hints for this?

Thanks
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]

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]

Thank you so much, It worked just great.

-- Michalis

 

[RB Smissaert]

No trouble, one day I may need it myself.

RBS

Thank you so much, It worked just great.

-- Michalis

 

[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

 

[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]

however I still have some minor issues

What issues?

RBS