find file path, open text file, populate table with text 1

[rareswan]

I have a table and one of the fields is a file path to some text files. I want to get the file path, find the file, open the file, and populate another table with the comma separated values in the text file. I have over one hundred text files so I'm trying to get away from manually importing each file.

Thanking you in advance for any help. (I'll thank you afterwards too.)

Randy Funny how such little knowledge can get me into sooooo much trouble.

 

[MichaelRed]

Should help.

Public Function basCSV2Array(FName As String, _
                             Optional RecSep As String = vbCrLf, _
                             Optional FldSep As String = ",") As Variant

    Dim Fil As Integer
    Dim NRecs As Long
    Dim NFlds As Long
    Dim Idx As Long
    Dim J As Long                               'indices
    Dim RawFile As String                       'Holds the entire contents of the file
    Dim RawSplit() As String                    'the file split up on a line per line basis
    Dim OneLine() As String                     'used to hold the tokens for one line
    Dim RptAry() As String                      '2d array. Holds X lines & N elements per line

    Fil = FreeFile                              'get the next free file number

    'This works for large files. I (Troy Williams)tried it
    'with a 50 meg file on a computer with 128 Mb of ram and it worked fine.
    'open the file and dump the contents into the rawfile variable

    Open FName For Binary As #Fil               'Open file
    RawFile = String$(LOF(Fil), 32)             'Create "empty" String of Length
    Get #Fil, 1, RawFile                        'Fill "Empty Str with File
    Close #Fil                                  'Close File

    'Get the Nunber of Records and Fields
    RawSplit = Split(RawFile, RecSep)           'Split the file up by lines
    NRecs = UBound(RawSplit) - 1                'Num Lines/Records in the file

    OneLine = Split(RawSplit(0), FldSep)        'Split the first line
    NFlds = UBound(OneLine)                     'Get the Number of Fields

    ReDim RptAry(0 To NRecs, 0 To NFlds)

    For Idx = 0 To NRecs
       OneLine = Split(RawSplit(Idx), FldSep)
       For Jdx = 0 To NFlds
          RptAry(Idx, Jdx) = OneLine(Jdx)
       Next Jdx
    Next Idx

    basCSV2Array = RptAry

End Function

Of course YOU still need to do the loop through the table of filenames and call this little bugger. Further, this does NOT place the values into a table, so that's a bit of loop logic to be included. Although it is somwhat simple, you would need to know/have a map of both the table name(s) and field names corresponding to the Array indicies ...

Another soloution would just use the sources and do a transfer text, but this also needs the mapping of file names to table names and preculdes any mapping of the field names. Further, any and all data "Grooming" would need to be accomplished on the .txt files, while the above could easily be enhanced to provide both field and record level validation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over