Parsing text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have code that allows my users to grab the name of text files. What I need
to do now is parse out the text file name into several pieces. Here is an
example of a file:

TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text values that
are separated by "_". The 2 values at the end of the string are date and time
(separated by "-"). I don't care about the ".txt" at the end.

Any ideas as how I can do this?

Much thanks!!
Clint
 
Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)
 
Ofer,

You beat me by a few minutes. I like your code - I keep forgetting about the
Split() function.

My code is more of a brute force method, but I'll post it as another method
(not as tight, but ...)


'-------
Dim strFileName As String
Dim strPart1 As String
Dim strPart2 As String
Dim strPart3 As String
Dim strPart4 As String


strFileName = "TestID_TestMachineName_CIF_20070302-013022.txt"

'remove the last 4 chars, ie ".txt"
strFileName = Left(strFileName, Len(strFileName) - 4)

'get the first text value
strPart1 = Left(strFileName, InStr(1, strFileName, "_") - 1)
'Debug.Print strPart1

'remove the first value from the string
strFileName = Right(strFileName, Len(strFileName) - InStr(1,
strFileName, "_"))

'2nd value
strPart2 = Left(strFileName, InStr(1, strFileName, "_") - 1)
'Debug.Print strPart2

'remove second value from the string
strFileName = Right(strFileName, Len(strFileName) - InStr(1,
strFileName, "_"))

'3rd value
strPart3 = Left(strFileName, InStr(1, strFileName, "_") - 1)
'Debug.Print strPart3

strFileName = Right(strFileName, Len(strFileName) - InStr(1,
strFileName, "_"))

'date-time
strPart4 = strFileName
'Debug.Print strPart4

MsgBox strPart1 & vbCrLf & strPart2 & vbCrLf & strPart3 & vbCrLf &
strPart4
'--------------


HTH
 
TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text values
that are separated by "_". The 2 values at the end of the string are
date and time (separated by "-"). I don't care about the ".txt" at the
end.
Click to expand...



Public Sub ParseFile()

Const MyFileName = " TestID_TestMachineName_CIF_20070302-013022.txt"

'> The file will always have the same structure. I need the 3 text values
'> that are separated by "_". The 2 values at the end of the string are
'> date and time (separated by "-"). I don't care about the ".txt" at the
'> end.


Dim text1 As String, text2 As String, text3 As String
Dim MyDate As Date, myTime As Date

Dim re As Object, regPattern As String
Dim resultSet As Object

regPattern = "(.*)_(.*)_(.*)_" & _
"(\d{4})(\d{2})(\d{2})-" & _
"(\d{2})(\d{2})(\d{2})\.txt"

Set re = CreateObject("VBScript.RegExp")
re.Pattern = regPattern

Set resultSet = re.Execute(MyFileName)

With resultSet(0).Submatches
text1 = .Item(0)
text2 = .Item(1)
text3 = .Item(2)

MyDate = DateSerial(.Item(3), .Item(4), .Item(5))
myTime = TimeSerial(.Item(6), .Item(7), .Item(8))

End With


End Sub
 
Ofer,

I am in similar dilemma, I trying to split names that have “FOR†on it such
as “KRISTINE STEWARD FOR REBECCA L STEWARDâ€. I have try the following code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the “Invalid procedure or argument†but it works using
blank spaces?
I try changing the above code to use the split function but got nowhere. Any
help is appreciated

Neil
 
I haven't tested, but you don't put square brackets around variable names.
As well, assuming midWord is supposed to contain Rebecca L Steward in your
example, it should be

midWord = Mid([varSamp_Rest], i + 4)

You also need to check that it did, in fact, find FOR in the text. If it
didn't, i will be 0, and Left will not work with a negative number.

i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]


If that's not the problem, on which line of code is it actually failing?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Ofer,

I am in similar dilemma, I trying to split names that have "FOR" on it
such
as "KRISTINE STEWARD FOR REBECCA L STEWARD". I have try the following
code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the "Invalid procedure or argument" but it works
using
blank spaces?
I try changing the above code to use the split function but got nowhere.
Any
help is appreciated

Neil


Ofer Cohen said:
Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)
Click to expand...
Click to expand...
 
Doug, thanks for the fast responce. I used your code but now I am getting
the wrong number of argument. Here is the entire code.
---Code
Dim varLen, i As Integer
Dim varSamp_Rest, midWord, sRest As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM SampleFetch_T2S " '<---- Change the Col Name
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]
rst.Update
rst.MoveNext
Loop
rst.Close
-End
Any suggestions

Douglas J. Steele said:
I haven't tested, but you don't put square brackets around variable names.
As well, assuming midWord is supposed to contain Rebecca L Steward in your
example, it should be

midWord = Mid([varSamp_Rest], i + 4)

You also need to check that it did, in fact, find FOR in the text. If it
didn't, i will be 0, and Left will not work with a negative number.

i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]


If that's not the problem, on which line of code is it actually failing?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Ofer,

I am in similar dilemma, I trying to split names that have "FOR" on it
such
as "KRISTINE STEWARD FOR REBECCA L STEWARD". I have try the following
code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the "Invalid procedure or argument" but it works
using
blank spaces?
I try changing the above code to use the split function but got nowhere.
Any
help is appreciated

Neil


Ofer Cohen said:
Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)

--
Good Luck
BS"D


:

I have code that allows my users to grab the name of text files. What I
need
to do now is parse out the text file name into several pieces. Here is
an
example of a file:

TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text values
that
are separated by "_". The 2 values at the end of the string are date
and time
(separated by "-"). I don't care about the ".txt" at the end.

Any ideas as how I can do this?

Much thanks!!
Clint
Click to expand...
Click to expand...
Click to expand...
 
Sorry, my fault.

midWord = Trim(varSamp_Rest, i + 4)

should be

midWord = Trim(Mid(varSamp_Rest, i + 4))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Doug, thanks for the fast responce. I used your code but now I am getting
the wrong number of argument. Here is the entire code.
---Code
Dim varLen, i As Integer
Dim varSamp_Rest, midWord, sRest As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM SampleFetch_T2S " '<---- Change the Col Name
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]
rst.Update
rst.MoveNext
Loop
rst.Close
-End
Any suggestions

Douglas J. Steele said:
I haven't tested, but you don't put square brackets around variable
names.
As well, assuming midWord is supposed to contain Rebecca L Steward in
your
example, it should be

midWord = Mid([varSamp_Rest], i + 4)

You also need to check that it did, in fact, find FOR in the text. If it
didn't, i will be 0, and Left will not work with a negative number.

i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]


If that's not the problem, on which line of code is it actually failing?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Ofer,

I am in similar dilemma, I trying to split names that have "FOR" on it
such
as "KRISTINE STEWARD FOR REBECCA L STEWARD". I have try the following
code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the "Invalid procedure or argument" but it works
using
blank spaces?
I try changing the above code to use the split function but got
nowhere.
Any
help is appreciated

Neil


:

Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)

--
Good Luck
BS"D


:

I have code that allows my users to grab the name of text files.
What I
need
to do now is parse out the text file name into several pieces. Here
is
an
example of a file:

TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text
values
that
are separated by "_". The 2 values at the end of the string are date
and time
(separated by "-"). I don't care about the ".txt" at the end.

Any ideas as how I can do this?

Much thanks!!
Clint
Click to expand...
Click to expand...
Click to expand...
 
Small typo the second Else should have been end IF

---Code
Dim varLen, i As Integer
Dim varSamp_Rest, midWord, sRest As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM SampleFetch_T2S " '<---- Change the Col Name
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
End if
rst!Name = midWord
rst!Name4 = [sRest]
rst.Update
rst.MoveNext
Loop
rst.Close
-End


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


N.Ordiers said:
Doug, thanks for the fast responce. I used your code but now I am getting
the wrong number of argument. Here is the entire code.
---Code
Dim varLen, i As Integer
Dim varSamp_Rest, midWord, sRest As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM SampleFetch_T2S " '<---- Change the Col Name
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]
rst.Update
rst.MoveNext
Loop
rst.Close
-End
Any suggestions

Douglas J. Steele said:
I haven't tested, but you don't put square brackets around variable names.
As well, assuming midWord is supposed to contain Rebecca L Steward in your
example, it should be

midWord = Mid([varSamp_Rest], i + 4)

You also need to check that it did, in fact, find FOR in the text. If it
didn't, i will be 0, and Left will not work with a negative number.

i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]


If that's not the problem, on which line of code is it actually failing?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Ofer,

I am in similar dilemma, I trying to split names that have "FOR" on it
such
as "KRISTINE STEWARD FOR REBECCA L STEWARD". I have try the following
code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the "Invalid procedure or argument" but it works
using
blank spaces?
I try changing the above code to use the split function but got nowhere.
Any
help is appreciated

Neil


:

Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)

--
Good Luck
BS"D


:

I have code that allows my users to grab the name of text files. What I
need
to do now is parse out the text file name into several pieces. Here is
an
example of a file:

TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text values
that
are separated by "_". The 2 values at the end of the string are date
and time
(separated by "-"). I don't care about the ".txt" at the end.

Any ideas as how I can do this?

Much thanks!!
Clint
Click to expand...
Click to expand...
Click to expand...
 
Thanks Doug, that did it.

Douglas J. Steele said:
Sorry, my fault.

midWord = Trim(varSamp_Rest, i + 4)

should be

midWord = Trim(Mid(varSamp_Rest, i + 4))


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


N.Ordiers said:
Doug, thanks for the fast responce. I used your code but now I am getting
the wrong number of argument. Here is the entire code.
---Code
Dim varLen, i As Integer
Dim varSamp_Rest, midWord, sRest As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM SampleFetch_T2S " '<---- Change the Col Name
Set rst = CurrentDb.OpenRecordset(strSQL)
rst.MoveFirst
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]
rst.Update
rst.MoveNext
Loop
rst.Close
-End
Any suggestions

Douglas J. Steele said:
I haven't tested, but you don't put square brackets around variable
names.
As well, assuming midWord is supposed to contain Rebecca L Steward in
your
example, it should be

midWord = Mid([varSamp_Rest], i + 4)

You also need to check that it did, in fact, find FOR in the text. If it
didn't, i will be 0, and Left will not work with a negative number.

i = InStr(varSamp_Rest, "FOR ")
If i > 0 Then
midWord = Trim(varSamp_Rest, i + 4)
sRest = Trim(Left(varSamp_Rest, i - 1))
Else
midWord = vbNullString
sRest = Trim(varSamp_Rest)
Else
rst!Name = midWord
rst!Name4 = [sRest]


If that's not the problem, on which line of code is it actually failing?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer,

I am in similar dilemma, I trying to split names that have "FOR" on it
such
as "KRISTINE STEWARD FOR REBECCA L STEWARD". I have try the following
code:
-Code below
Do While Not rst.EOF
rst.Edit
varSamp_Rest = rst!Samp_Rest
i = InStr([varSamp_Rest], "FOR ")
midWord = Right([varSamp_Rest], i + 2)
sRest = Left([varSamp_Rest], i - 1)
rst!Name = Trim(midWord) 'Var Char
rst!Name4 = Trim([sRest])
rst.Update
rst.MoveNext
Loop
rst.Close-End code
This has failed, with the "Invalid procedure or argument" but it works
using
blank spaces?
I try changing the above code to use the split function but got
nowhere.
Any
help is appreciated

Neil


:

Use the split function to split the string into parts

Dim MyString As String
MyString = "TestID_TestMachineName_CIF_20070302-013022.txt"
MyString = Replace(MyString , ".txt", "")

' First part
Split(MyString ,"_")(0)
' Second part
Split(MyString ,"_")(1)
' Third part
Split(MyString ,"_")(2)
' Date
Left(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")-1)
' Time
Mid(split(MyString ,"_")(3),Instr(split(MyString ,"_")(3),"-")+1)

--
Good Luck
BS"D


:

I have code that allows my users to grab the name of text files.
What I
need
to do now is parse out the text file name into several pieces. Here
is
an
example of a file:

TestID_TestMachineName_CIF_20070302-013022.txt

The file will always have the same structure. I need the 3 text
values
that
are separated by "_". The 2 values at the end of the string are date
and time
(separated by "-"). I don't care about the ".txt" at the end.

Any ideas as how I can do this?

Much thanks!!
Clint
Click to expand...
Click to expand...
Click to expand...
 
Back
Top