File IO Into Array 1

[GerardMcL]

Hi,
I have a file with a recipe and each line has three parts and need to be stored and placed into a database and form.
Below is the content of .txt file

A00120041006162417003002Keith BESTMIX 105
B001 319.00 02020017 16498N0012000002004090900 352
C001Htage Beef Finshr Nt 000000 0001999.999 000.000a 001 054
E001 3 10 0000034.00000 0000000679.999660565
E002 10 10 0000025.00000 0000000499.999750666
E003 52 10 0000021.20000 0000000423.999788561
E004 14 10 0000005.10000 0000000101.999949061
E005 67 10 0000004.50000 0000000089.999955073
E006 99 10 0000004.00000 0000000079.999960481
E007 223 20 0000003.45000 0000000068.999966266
E008 24 10 0000001.00000 0000000019.999990067
E009 97 10 0000001.00000 0000000019.999990144
E010 20 10 0000000.45000 0000000008.999996752
E011 213 10 0000000.25000 0000000004.999998653
E012 65 10 0000000.05000 0000000001.000000450

I know it looks like a mess, but thats what I gotta work with. I have never done any file handling before and would appreciate any help that you could give.

Cheers!

 

[vladk]

GerardMcL,

You could employ FileSystemObject to read your source files line by line, file by file.
You might want to use Split function and/or Instr, Right, Left, Mid functions, or even Regular Expressions to parse each line to prepare it to be put into your database.

There are plenty of threads on how to do this.

vladk

 

[CubeE101]

nah...

Dim TextAry() As String
Open "file.txt" for input as #1
  TextAry = Split(Input(LOF(1), #1), vbcrlf)
Close

you can then remove excess spaces from each line and split that data too...

Dim TextData() As String
For x = 0 to ubound(TextAry)
  'remove extra spaces...
  While Instr(1, TextAry(x), "  ")
    TextAry(x) = Replace(TextAry(x), "  ", " "
  Wend
  'then split the line by the spaces...
  TextData = Split(TextAry, " ")
  Debug.? TextData(1)
Next

Hope this helps ;-)

Visit My Site
PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[GerardMcL]

Hi,

I have the code you suggested in but there is a problem with
TextAry = Split(Input(LOF(1), #1), vbCrLf)

No library found for Input.
I know someone will start hurling abuse at me shortly for these silly questions, but what reference does this require?

 

[Motor11]

This function is longer than it has to be, but it is very fast and works with UNIX or DOS ascii files that are both small and large:

To use it, define an array of strings called individ_lines()

Global individ_lines() As String

This string will be populated with the contents of the filename that you pass the function. One line in the file = one element in the individ_lines() array. Now you can parse the array easily. The function returns the number of lines in the array. I can send you an example of parsing the array if you'd like.

Public Function build_file_array(filename As String) As Long
    Dim wholefile As String
    Dim endline As String
    Dim k As Long
    Dim cloc As Long
    Dim i As Long
    Dim lastline As String
    Dim ff As Integer
    Dim holder() As String
    ReDim Devicelist(0)
    ReDim individ_lines(0)
    ff = FreeFile
    Open filename For Binary As #ff
    wholefile = String(LOF(ff), 0)
    'Read the entire file in one operation and close it
    Get #ff, 1, wholefile
    Close #ff
    
    If InStr(wholefile, vbCrLf) > 0 Then
        endline = vbCrLf   'ASCII DOS format
    Else
        endline = vbLf   'ASCII UNIX format
    End If
    
    i = 1
    k = -1 ' Next element to store
    lastline = "junk"
    
    Do While (i <= Len(wholefile))
        cloc = InStr(i, wholefile, endline)
        ' last line need not have terminator
        If cloc = 0 Then cloc = Len(wholefile) + 1
        k = k + 1 ' new element
        If k > UBound(individ_lines) Then
           ReDim Preserve individ_lines(2 * k)
        End If
    
            individ_lines(k) = Mid(wholefile, i, cloc - i)
        
        
        i = cloc + Len(endline) ' bump over terminator
    Loop
    If k > -1 Then
           ReDim Preserve individ_lines(k) ' Resize to actual
    End If

    build_file_array = k
    Debug.Print "Exit build_file_array"
End Function

 

[CubeE101]

No library found for Input.
I know someone will start hurling abuse at me shortly for these silly questions, but what reference does this require?

The standard references:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation

Visit My Site
PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[GerardMcL]

Hi,

Motor 11 cheers for the code, I was hoping you would be able to send me an example of parsing the array.

Thanks very much (everybody)