How to read a text file like this with vb???

[rahmanjan]

hi all,

BRWNS SUPSHK CHO 22213.000 23250.000
BROWNS SUPASHK 5 24.000 24.000
BROWNES SUPASHAK 2986.000 3464.000
BROWNES SUPASHAK 3726.000 3914.000
BROWNES ZOOM VAN 17436.000 18554.000
BROWNES ZOOM CHO 17858.000 18955.000
BROWNES SUPASHAK 4537.000 4608.000
BRWNS CHL 200g k 39445.000 40579.000
BRWNS CHL STRNG 169921.000 181638.000
BROWNS CHL STRNG 2278.000 2278.000

I need to load this data in an array or atleast be able to read it in 3 variables (one for each column) but the problem is i don't know how to do it. It is not comma delimited and it is also not fixed lenght. The lenght for the first column could be different as it is now. Is there any trick to read this file. I am more interested with the 2nd column values???

thanks in advance

 

[johnwm]

It looks as if the columns are separated by tab characters or by multiple spaces. Try doing a Split on each line:

Dim strA() As String
strA() = Split(Text1.text, " ", , vbTextCompare)

if it's spaces or:

strA() = Split(Text1.text, vbTab, , vbTextCompare)

if it's tabs. Either way you'll find the second element in strA(1)


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[MichaelRed]

While johnwm's soloution is going in the right direction, I think it is somewhat incomplete. There are (aparently) multiple spaces in the source, not tabs (not tabs because the "text", when presented in a fixed font, lines up with the 2nd and third fields RIGHT justified). The multiple spaces between the "fields" result in multiple (empty) elements in the array creaqted by the Split function. Thes need to be "filtered" out of the results. Further, the spaces in the first field need to be "retained" (actually replaced) for the results to represent the original information. The following function properly seperate and formats the elements and places them in an "N * 3" array, although at the expense of translating the numeric values to strings.

Public Function basTxt2Array(FilIn As String) As Boolean


    'Michael Red 12/26/03 Tek-tips, thread222-736793
    'For "yamjan"

    'Sample Usage:
    'basTxt2Array("C:\My Documents\MsAccess\MyList.Txt")

    Dim Idx As Long
    Dim Jdx As Long
    Dim MyTxt As String
    Dim MyLines As Variant
    Dim MyWds As Variant
    Dim MyRealWds() As String
    Dim MyAry() As String

    'Use the helper function to get the file into a text string
    MyTxt = basGrabFile(FilIn)

    'Get the Text into an array of Lines
    MyLines = Split(MyTxt, vbCrLf)
    ReDim MyAry(3, UBound(MyLines))

    While Idx < UBound(MyLines)

        'Split the lines into Words
        MyWds = Split(MyLines(Idx), &quot; &quot;)

        'Remove the &quot;Empty&quot;  Words
        Jdx = 0
        Kdx = 0
        ReDim MyRealWds(0)
        While Jdx <= UBound(MyWds)

            If (MyWds(Jdx) <> &quot;&quot;) Then
                MyRealWds(Kdx) = MyWds(Jdx)
                Kdx = Kdx + 1
                ReDim Preserve MyRealWds(Kdx)
            End If

            Jdx = Jdx + 1
        Wend

        'Discard the last element of the &quot;Real Words&quot;, added but not used
        ReDim Preserve MyRealWds(UBound(MyRealWds) - 1)

        'Put the &quot;words&quot; into an array
        myStr = &quot;&quot;
        Jdx = 0
        While Jdx <= UBound(MyRealWds) - 2

            myStr = myStr & &quot; &quot; & MyRealWds(Jdx)

            Jdx = Jdx + 1
        Wend

        MyAry(1, Idx) = myStr
        MyAry(2, Idx) = MyRealWds(UBound(MyRealWds) - 1)
        MyAry(3, Idx) = MyRealWds(UBound(MyRealWds))

        Idx = Idx + 1
    Wend

    Jdx = 0
    While Jdx <= UBound(MyAry, 2)
        Debug.Print Jdx, MyAry(1, Jdx), MyAry(2, Jdx), MyAry(3, Jdx)
        Jdx = Jdx + 1
    Next Jdx


    Stop

End Function

The function also utilizes the convenient (if somewhat arcane) helper function (basGrabfile) to get the entire source file into a single string variable. This is somewhat extreme for the trivial (size) sample, but can be quite important for larger sources, as I/O generally consumes the majority of the time required to process files, particularly when the process uses line Input.

Although the helper function has been severally posted in these fora, the un-reliability of the search capability of this site compells me to re-post it, to be assured that the user will have access to it:

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim myfil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    myfil = FreeFile

    Open FilIn For Binary As #myfil

    MyTxt = String(LOF(myfil), " ")
    Get #myfil, 1, MyTxt

    Close #myfil

    basGrabFile = MyTxt

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[strongm]

Well, it looks to me as if this is actually fixed width data.

In which case the following are two alternatives for dealing with it (assuming data is in c:\demo.txt):
[tt]
Option Explicit

Private Type MyType
Data1 As String * 16
Data2 As String * 12
Data3 As String * 12
End Type

Private Sub Command1_Click()
Dim strSource As String
Dim arrResults() As MyType
Dim lSize As Long
Dim lp As Long

strSource = Replace(CreateObject("scripting.filesystemobject&quot.OpenTextFile("c:\demo.txt&quot.ReadAll, vbCrLf, "&quot

lSize = (Len(strSource) / 40) - 1
ReDim arrResults(lSize)

For lp = 0 To lSize
arrResults(lp).Data1 = Mid(strSource, 1 + lp * 40, 16)
arrResults(lp).Data2 = Mid(strSource, 1 + lp * 40 + 16, 12)
arrResults(lp).Data3 = Mid(strSource, 1 + lp * 40 + 28, 12)
Next

End Sub
[/color[/tt]
or
[tt]
Option Explicit

Private Type MyType
Data1 As String * 16
Data2 As String * 12
Data3 As String * 12
End Type

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Sub Command2_Click()
Dim strSource() As Byte
Dim arrResults() As MyType

strSource = Replace(CreateObject("scripting.filesystemobject&quot.OpenTextFile("c:\demo.txt&quot.ReadAll, vbCrLf, "&quot

ReDim arrResults((UBound(strSource) + 1) / (40 * 2) - 1)
CopyMemory ByVal VarPtr(arrResults(0)), ByVal VarPtr(strSource(0)), UBound(strSource)

End Sub

 

[strongm]

Or, if you want to be more traditional (none of that nasty filesystemobject or copymemory API stuff):
[tt]
Dim arrResults() As MyType
Dim lSize As Long
Dim lp As Long
Dim dummy As String * 2

Dim hFile As Long

hFile = FreeFile

Open "c:\demo.txt" For Binary As hFile Len = 40

lSize = (LOF(hFile) / 40) - 1
ReDim arrResults(lSize)
For lp = 0 To lSize
Get #hFile, , arrResults(lp)
Get #hFile, , dummy ' deal with crlf
Next


Close hFile

 

[rahmanjan]

Dear all,

Thanks for all the kind replies.

Though I haven't tried the code but I beliee Michael is in the right track. The file looks like a fixed lenght but actually it is not. We are using a software that creates it so the first column length could be "BRWNS SUPSHK CHO " or could be "BRoWNS SUPer-SHeeK CHOcolate 250gm" etc. Therefore, i can't deal with it as fixed lenght.

I still don't' know how Michael's function will deal with the line "BRoWNS SUPer-SHeeK CHOcolate 250 22213.000 23250.000" to divide it in 3 N-array (let is assume that 250 belongs to description field) and this somehting that worries me. I used split function but it was confusing let is say in above situation where first field has and ends with number too.

let me try and let you guys.

regards

 

[MichaelRed]

yamjan,

Actually, strongm's observation does appear to be correct. Each record (line) in the source does apear to be of the same length:

         1        2         3         4
1234567890124567890123456789012345678901
BRWNS SUPSHK CHO   22213.000   23250.000
BROWNS SUPASHK 5      24.000      24.000
BROWNES SUPASHAK    2986.000    3464.000
BROWNES SUPASHAK    3726.000    3914.000
BROWNES ZOOM VAN   17436.000   18554.000
BROWNES ZOOM CHO   17858.000   18955.000
BROWNES SUPASHAK    4537.000    4608.000
BRWNS CHL 200g k   39445.000   40579.000
BRWNS CHL STRNG   169921.000  181638.000
BROWNS CHL STRNG    2278.000    2278.000

With Perhaps some difficulty in being SURE of the field lengths. How the sample was generated MAY be an issue, but if have hte software which creates the "files" the documnetation (or the every present availability of Help) should provide the answer for your use. The field width used by strongm are consistient with the displayed sample, which can be seen in the anotated (with column numbers) sample shown above.

I am not necessarily reccomending any of the soloutions presented, as there are 'preferences' in coding styles as well as the value of at least being exposed to the different approaches, however the statement that the fields are NOT fixed width cannot be shown from the sample presented.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[rahmanjan]

Thanks Michael,

As i mentioned above we have a software that creates the above sample. The user has the option to set and select the size of columns. Therefore, they are varying each time and user by user. Therefore, I can't rely on fixed column based way of reading it.

I also used your above solution, but it still looks like the 3rd column is a mixture of the number and string. something like: CHO 22213.000.

I have impleneted the solution by converting the above sample to .csv file using Excel but it is another un-necessay step for the users.

Is there any way to change the above sample to comma delimted file without user interaction???

regards,

 

[LPlates]

Try this code, I placed the following into App.Path named 'Test.txt'...

BRWNS SUPSHK CHO 22213.000 23250.000
BROWNS SUPASHK 5 24.000 24.000
BROWNES SUPASHAK 2986.000 3464.000
BROWNES SUPASHAK 3726.000 3914.000
BROWNES ZOOM VAN 17436.000 18554.000
BROWNES ZOOM CHO 17858.000 18955.000
BROWNES SUPASHAK 4537.000 4608.000
BRWNS CHL 200g k 39445.000 40579.000
BRWNS CHL STRNG 169921.000 181638.000
BROWNS CHL STRNG 2278.000 2278.000

Option Explicit

Dim Arr() As String

Private Sub Command1_Click()
Dim intFreeFile As Integer
Dim sText As String
Dim lText() As String
Dim x As Integer
Dim i As Integer
Dim z As Integer

intFreeFile = FreeFile
Open App.Path & "\Test.txt" For Input As #intFreeFile
While Not EOF(intFreeFile)
Input #intFreeFile, sText
x = x + 1
Wend
Close #intFreeFile

ReDim Arr(x, 2)
x = 0
intFreeFile = FreeFile
Open App.Path & "\Test.txt" For Input As #intFreeFile
While Not EOF(intFreeFile)
Input #intFreeFile, sText
Debug.Print sText
lText() = Split(sText, " &quot

For i = 0 To 2
Do Until Trim(lText(z)) <> vbNullString
z = z + 1
Loop
Arr(x, i) = Trim(lText(z))

Debug.Print Arr(x, i)
z = z + 1
Next i
x = x + 1
z = 0
Wend
Close #intFreeFile

End Sub

View the array...

Private Sub Command2_Click()
Dim i As Integer
For i = 0 To UBound(Arr()) - 1
Debug.Print Arr(i, 0) & &quot;,&quot; & Arr(i, 1) & &quot;,&quot; & Arr(i, 2)
Next i
End Sub

 

[LPlates]

Output the array as comma delimited text file (Press Command1_Click first!)...

Private Sub Command3_Click()
Dim intFreeFile As Integer
Dim i As Integer

intFreeFile = FreeFile
Open App.Path & &quot;\Test2.txt&quot; For Output As #intFreeFile
For i = 0 To UBound(Arr()) - 1
Print #intFreeFile, Arr(i, 0) & &quot;,&quot; & Arr(i, 1) & &quot;,&quot; & Arr(i, 2)
Next i
Close #intFreeFile
End Sub

 

[MichaelRed]

hmmmmmmmmmmmmm ... mmmmmmmmmmmmm,

Hard to tell from the lack of detail, but something is not being divulged. Below is the &quot;Debug&quot; srceen from running the program I posted above:

basTxt2Array("C:\My Documents\MsAccess\MyList.Txt")
 0             BRWNS SUPSHK CHO           22213.000     23250.000
 1             BROWNS SUPASHK 5           24.000        24.000
 2             BROWNES SUPASHAK           2986.000      3464.000
 3             BROWNES SUPASHAK           3726.000      3914.000
 4             BROWNES ZOOM VAN           17436.000     18554.000
 5             BROWNES ZOOM CHO           17858.000     18955.000
 6             BROWNES SUPASHAK           4537.000      4608.000
 7             BRWNS CHL 200g k           39445.000     40579.000
 8             BRWNS CHL STRNG            169921.000    181638.000
 9             BROWNS CHL STRNG           2278.000      2278.000
 10

So the appearance of the additional characters in the third field reported by yamjan (ie, CHO 22213.000would APPEAR to be not from the sample, and not conform to the original statement of hte file format.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Merlijn]

Maybe the soft that creates the file also maintains a parameter file where it stores the width of each field. If you can find and read that parameter file, you can use the sample app replacing the fixed lengths with the ones found in the parameter file. Just a thought.
I did the same trick with an app that my friend's father in law had on his computer and which he wanted to be expanded with some other functionality. The software firm asked too much for it, so he turned to me. btw, it worked .

Merlin is the name and logic is my game.

 

[davejazz]

A down & dirty &quot;kludge&quot; solution may be to reverse each line and then search for the first blank space.

ln = BRWNS SUPSHK CHO 22213.000 23250.000
for i=length(ln) to 1 step -1
temp = mid(ln,i,1)
next i

(temp now equals &quot;000.05232 000.31222 OHC KHSPUS SNWRB&quot

i = Instr(temp,&quot; &quot
var3 = mid(temp,1,i-1) '000.05232
temp = mid(temp,i+1) (temp now equals &quot;000.31222 OHC KHSPUS SNWRB&quot

i = Instr(temp,&quot; &quot
var2 = mid(temp,1,i-1) '000.31222
temp = mid(temp,i+1) (temp now equals &quot;OHC KHSPUS SNWRB&quot

var1 = temp 'OHC KHSPUS SNWRB


Just reverse the three variables again to get their value.

This &quot;kludge&quot; solution assumes you're not using any sort of hidden delimeter in each line. You should also use the var = Trim(var) function to remove any leading or trailing spaces which may have slipped in.

Davejazz

 

[SemperFiDownUnda]

Instead of reversing the string just use
InStrRev() function.

Another option you could do is something like this

Private Function DelimitString(ByRef sData As String, ByVal iMaxColWidth, ByVal sDelim As String) As String
  Dim iCnt As Integer
  For iCnt = iMaxColWidth To 2 Step -1
    sData = Replace(sData, Space(iCnt), sDelim)
  Next iCnt
  DelimitString = sData
End Function

The only problem with this procedure is that it requires atleast 2 spaces to be between columns.

You can then use this in something like

vArray = Split(DelimitString(sData, 20, "|"),"|")