problems reading a text file

Hi sam_norian,

As long as the file uses consistent rules, this can be done.  What we need you to do is to codify those rules
in as much detail as you can.  E.g.:

"The quantity field always starts:

QTY+21:

then has the value I want, sometimes [or always, as the case may be] followed by a decimal.  The field terminates
with an apostrophe."

Regards,

Patrick

Alot,

please elaborate what information you want to extract so that i can suggest an appropriate solution.

if i assume you are just interested in the line containing <<<<< or >>>>>, just process line by line and then make use of the "split" function of VB

ooops....Sam Norian not Alot :)

@sam_norian

>>import the marked 'fields' (lines with <<< after) in to a database

in the given sample content, you didnt mention the portion you want to select
Please specify the portion you want to extract

Hi,

Sorry these are the parts I need to import:

IMD+++DU:::NAVY BED' >>> i just want 'navy bed'

QTY+21:1.00' >>>i just want '1.00'

NAD+DP+::+123 Made Up Street :Made Up Town::Made Up City:+Mr Made Up:01614833974:::++++AA12 1AA' >>> From this line i need '123 Made Up Street' as (db field) Address 1, 'Made Up Town' as (db field) Address 2, Made Up City as (db field) Address 3, Mr Made Up as (db field) CustomerName, 01614833974 as (db field) PhoneNumber and finally 'AA12 1AA' as (db field) Postcode.

DTM+11:20061031:102' >>> I need the 20061031 (if possible reformatted to English date (31/10/2006).

I hope I have explained this well enough :s

As far as i understood following are the lines u want to process

IMD+++DU:::NAVY BED' >>>
QTY+21:1.00' >>>
NAD+DP+::+123 Made Up Street :Made Up Town::Made Up City:+Mr Made Up:01614833974:::++++AA12 1AA' >>> 
DTM+11:20061031:102' >>>

some more questions
1. following prefixes will always be constant right ?
IMD+++
QTY+
DTM+

2. File content will carry only one item(line) for each entries ?
3. There wont be any character after ">>>" in the same line ?

>>1. following prefixes will always be constant right ? Yes they are.
>>2. File content will carry only one item(line) for each entries ? I'm not sure what you mean by this?
>>3. There wont be any character after ">>>" in the same line ? I added the >>> to comment the text, the ' (apostrophe) signals the end of the line.

in second question i mean to know that
there can be two entries like the following in one file?

IMD+++DU:::NAVY BED1'>>>
IMD+++DU:::NAVY BED2'>>>

Yes, there is a very good chance there will be, each chunk of text (like the shown above) is 1 order, there are likely to situations where that product is order more than once in one file but that line wont appear in an order more than once.

where do u need all data to be populated
Shall i put message boxes, so that you can do the rest from there

The data will end up in a database table. Message boxes will be fine, I should be able to work it out from there.

Thanks alot,

Sam

Private Sub Command1_Click()
Dim oReg, oMat, oMCol, Content, sFile
    Set oReg = CreateObject("VBScript.RegExp")
    oReg.IgnoreCase = True
    oReg.Global = True
    sFile = "C:\Your_File.txt" '<----- "Modify your file path
    Content = GetFileContent(sFile)
   
    'Getting IMD+++DU:::NAVY BED' >>>
     oReg.Pattern = "IMD\+{3}\w+\:{3}(.*?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "IMD : " & oReg.Replace(oMat.Value, "$1")
     Next
     'Getting QTY+21:1.00' >>>i just want '1.00'
     oReg.Pattern = "QTY\+\d+\:(.+?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "QTY : " & oReg.Replace(oMat.Value, "$1")
     Next
     'DTM+11:20061031:102' >>>
     oReg.Pattern = "DTM\+\d+\:(\d{4})(\d{2})(\d{2})'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "DTM : " & oReg.Replace(oMat.Value, "$3") & "/" & oReg.Replace(oMat.Value, "$2") & "/" & oReg.Replace(oMat.Value, "$1")
     Next
End Sub
Private Function GetFileContent(ByVal sFile As String) As String
Dim oFso, oText
    Set oFso = CreateObject("Scripting.FileSystemObject")
    If Not oFso.FileExists(sFile) Then
        MsgBox "File does not exist", vbExclamation
        Exit Function
    End If
    Set oText = oFso.OpenTextFile(sFile)
    GetFileContent = oText.ReadAll
    oText.Close
    Set oFso = Nothing
End Function

i need some more clarification in the line
please let me know what all things to be extracted from this line

NAD+DP+::+123 Made Up Street :Made Up Town::Made Up City:+Mr Made Up:01614833974:::++++AA12 1AA' >>>

one change

replace this line   --------------> oReg.Pattern = "DTM\+\d+\:(\d{4})(\d{2})(\d{2})'[ \t]*>>>"
with
       oReg.Pattern = "DTM\+\w+\:(\d{4})(\d{2})(\d{2})\:\d+'[ \t]*>>>"

123 Made Up Street , Made Up Town , Made Up City , Mr Made Up , 01614833974 , AA12 1AA

Thanks alot

Still having doubt in that line
i am listing some possible entries, are they valid ?

NAD+DP+::+123 Made Up Street1 :Made Up Town1::Made Up City1:+Mr Made Up1:01614833974:::++++AA12 1AA' >>>

NAD+DP+::+123 Made Up Street2 :Made Up Town2::Made Up City2:+Mr Made Up2:01614833975:::++++AA12 1AB' >>>

NAD+DP+::+123 Made Up Street3 :Made Up Town3::Made Up City3:+Mr Made Up3:01614833976:::++++AA12 1AC' >>>


That is only the values
  123 Made Up Street
  Made Up Town
  Made Up City
  Mr Made Up
  01614833974
  AA12 1AA
are getting changed rest of the things will be same.
That is seperators  :   :+   :::++++ are will be in place
right ?

more specifically 6 data

NAD+DP+::+content1:content2::content3:+content4:content5:::++++content6' >>>

right ?

Yes this is right. Thank you

ok append this much code

     'Getting NAD+DP+::+123 Made Up Street :Made Up Town::Ma...
     oReg.Pattern = "NAD\+\w+\+\:{2}\+(.+?)\:(.+?)\:{2}(.+?)\:\+(.+?)\:(.+?)\:{3}\+{4}(.+?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     Dim i As Integer
     Dim sData, sSep
     For Each oMat In oMCol
        sSep = ""
        sData = ""
        For i = 1 To 6
            sData = sData & sSep & oReg.Replace(oMat.Value, "$" & i)
            sSep = ","
        Next
        MsgBox "NAD : " & sData
     Next

I have put this code under a command button but it doesn't seem to do anything, if I change the sFile = "C:\Your_File.txt" to file that doesn't exist I get the error message but when I have the file located correctly it doesn't do anything (no message boxes or error messages).

Thanks

can u post the complete code u have ?

Private Sub Command1_Click()
Dim oReg, oMat, oMCol, Content, sFile
    Set oReg = CreateObject("VBScript.RegExp")
    oReg.IgnoreCase = True
    oReg.Global = True
    sFile = "c:\23-10-06.txt" '<----- "Modify your file path
    Content = GetFileContent(sFile)
   
    'Getting IMD+++DU:::NAVY BED' >>>
     oReg.Pattern = "IMD\+{3}\w+\:{3}(.*?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "IMD : " & oReg.Replace(oMat.Value, "$1")
     Next
     'Getting QTY+21:1.00' >>>i just want '1.00'
     oReg.Pattern = "QTY\+\d+\:(.+?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "QTY : " & oReg.Replace(oMat.Value, "$1")
     Next
     'DTM+11:20061031:102' >>>
     oReg.Pattern = "DTM\+\w+\:(\d{4})(\d{2})(\d{2})\:\d+'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     For Each oMat In oMCol
        MsgBox "DTM : " & oReg.Replace(oMat.Value, "$3") & "/" & oReg.Replace(oMat.Value, "$2") & "/" & oReg.Replace(oMat.Value, "$1")
     Next
     
          'Getting NAD+DP+::+123 Made Up Street :Made Up Town::Ma...
     oReg.Pattern = "NAD\+\w+\+\:{2}\+(.+?)\:(.+?)\:{2}(.+?)\:\+(.+?)\:(.+?)\:{3}\+{4}(.+?)'[ \t]*>>>"
     Set oMCol = oReg.Execute(Content)
     Dim i As Integer
     Dim sData, sSep
     For Each oMat In oMCol
        sSep = ""
        sData = ""
        For i = 1 To 6
            sData = sData & sSep & oReg.Replace(oMat.Value, "$" & i)
            sSep = ","
        Next
        MsgBox "NAD : " & sData
     Next
End Sub
Private Function GetFileContent(ByVal sFile As String) As String
Dim oFso, oText
    Set oFso = CreateObject("Scripting.FileSystemObject")
    If Not oFso.FileExists(sFile) Then
        MsgBox "File does not exist", vbExclamation
        Exit Function
    End If
    Set oText = oFso.OpenTextFile(sFile)
    GetFileContent = oText.ReadAll
    oText.Close
    Set oFso = Nothing
End Function

can u post the sample file content you tried

or u can upload file to
http://www.ee-stuff.com/ 
against your question

no probs: https://filedb.experts-exchange.com/incoming/ee-stuff/1267-23-10-06.txt

Oops
there is no string  ">>>" in the file
i thought we need to take only lines ending with >>>

sorry , missed out one line

pls change
           oReg.Pattern = "QTY\+\d+\:(.+?)'[ \t]*>>>"

to
           oReg.Pattern = "QTY\+\d+\:(.+?)'[ \t]*\r"

Ok brilliant that seems to work.

Thank you very much for all your help

:-)
Thank you for the grade