Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: How to import several text files in one folder into access tables using VBA

    Hi Gurus,
    I need your guidance. This is pretty urgent.

    We have several machine generated files in one folder. Some of the files are tab delimited text file with .REC file extentions. Those .REC file names are *_01.REC, *_02.REC, *_03.REC, *_04.REC .... (* is a wildcat character and the real file name are varied. It can be 326_01.REC, etc)
    The file layout is similar to the following. The first line has only 4 columns, but the rest of line including the 2nd line (column header) have more columns than the first line.

    08/06/2006 11:09 Bank# 1: 326HR Parameter# 1: IL-4/5-S1
    Scan Temp Hum Ref1 Ref2 Time C_3 C_4 C_5
    1 -999 -999 -10.039 -10.038 0.002 -11.931 -11.247 -13.088
    2 -999 -999 -10.041 -10.038 2.003 -11.931 -11.247 -13.087
    3 -999 -999 -10.041 -10.039 4.003 -11.898 -11.233 -13.062
    4 -999 -999 -10.042 -10.041 6.003 -11.935 -11.251 -13.095

    I need to write a VBA code in Access 2002 to loop through the folder and import all of the .REC files into different access tables.

    I don't know if the split function will work since the first line has few columns than other lines.
    Please also help me to point out whether the following code will work for my purpose. Thank you very much!!

    Newguy


    Private Sub cmdappx_Click()
    Dim fOK As Boolean
    Dim srcdir As String
    Dim appdir As String
    Dim srcprog As String
    Dim objFSO As FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
    Dim DB1 As DAO.Database, RS1 As DAO.Recordset, RS2 As DAO.Recordset
    Dim RS3 As DAO.Recordset, RS4 As DAO.Recordset
    Dim str As String, str1() As String, str2() As String, str3() As String, str4() As String

    Set DB1 = CurrentDb
    If (ocno = 1 Or ocno = 3) Then


    srcdir = "C:\ocs\record"

    Else
    srcdir = "C:\program files\record"


    End If

    'programno is a global variable, the value will be depended on what user input in the form

    srcprog = srcdir & "\" & programno

    DoCmd.SetWarnings True

    '
    Dim i As Integer
    On Error GoTo Err_handler

    Set objFSO = New FileSystemObject

    Set objFolder = objFSO.GetFolder(srcprog)
    Set RS1 = DB1.OpenRecordset("rec1")
    Set RS2 = DB1.OpenRecordset("rec2")
    Set RS3 = DB1.OpenRecordset("rec3")
    Set RS4 = DB1.OpenRecordset("rec4")

    For Each objFile In objFolder.Files


    If InStr(objFile.Name, "_01.REC") <> 0 Then

    Open objFile For Input As #1
    Do While Not EOF(1)
    Line Input #1, str
    str1 = Split(str, vbTab)
    RS1.AddNew
    For i = 0 To UBound(str1)
    RS1(i) = str1(i)
    Next
    RS1.Update
    Loop
    Close #1
    RS1.Close

    End If
    If InStr(objFile.Name, "_02.REC") <> 0 Then

    Open objFile For Input As #1
    Do While Not EOF(1)
    Line Input #1, str
    str2 = Split(str, vbTab)
    RS2.AddNew
    For i = 0 To UBound(str2)
    RS2(i) = str2(i)
    Next
    RS2.Update
    Loop
    Close #1
    RS2.Close

    End If
    If InStr(objFile.Name, "_03.REC") <> 0 Then

    Open objFile For Input As #1
    Do While Not EOF(1)
    Line Input #1, str
    str3 = Split(str, vbTab)
    RS3.AddNew
    For i = 0 To UBound(str3)
    RS3(i) = str3(i)
    Next
    RS3.Update
    Loop
    Close #1
    RS3.Close

    End If
    If InStr(objFile.Name, "_04.REC") <> 0 Then

    Open objFile For Input As #1
    Do While Not EOF(1)
    Line Input #1, str
    str4 = Split(str, vbTab)
    RS4.AddNew
    For i = 0 To UBound(str4)
    RS4(i) = str4(i)
    Next
    RS4.Update
    Loop
    Close #1
    RS4.Close

    End If


    Next

    Exit Sub
    Err_handler:
    MsgBox Err.Number & "-" & Err.Description


    DoCmd.SetWarnings False


    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you run your recordset operations with Warnings ON and set Warnings OFF only after an error is thrown... this is an unusual approach. the typical game-plan is to run with Warnings OFF; to exit normally through a tidyup scheme that sets Warnings ON and explicitly releases objects such as your objFSO; and to resume at the normal exit/tidyup after error handling.

    blah
    blah
    myExit:
    set objFSO = nothing
    exit sub
    myErrorHandler:
    blah
    blah
    resume myExit

    your code should run as is but will leave Warnings in an indeterminate state (OFF if error, else ON)

    you are treating all felds as text when all of your data is numeric...
    ...which is a necessary consequence of your treating the preamble line AND fieldname line as though they were data lines. again your code should run, but will leave you with a sorry excuse for a table where e.g. the first field holds three completely different species of data depending which row you look at. one of the philosophical rules of databases is that the order of the recods has no meaning so, at least philosophically, your table is "unreadable". i would be tempted here to spin the first line off into another table, discard the second line, and read the numeric data as numeric.

    you use four tables (with - and this is only a guess - identical structure once the first two lines are dropped). sure it should work, but i would be tempted to use a single table adding a field to identify the _01, _02 etc source.

    you have a strange cocktail of FSO and native VBA file-handling. it should work, but you can do the same job with either one of them on its own. should be faster wthout FSO.

    you use instr(). right$(filename, 7) should be faster.

    probably you know the answer to your question already since all you need to do is run the code to see if it works,
    but you asked "will it work?". probably yes, but i don't like the code or the resulting tables.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2007
    Posts
    88

    Thank you very much!

    izy,

    Thank you very much for spending time in replying my question. My knowledge in access and vba is very limited. I knew the way I wrote the code is not proficient and professional at all and that's why I asked for help and guidance from experienced gurus.

    Actually, I don't need the first two lines in the REC files to import to the access tables. Because I don't know how to get rid of them when importing. The four tables are identical in structure. Would you please let me know how to import without the first two lines? That way, I can define the table field as number. In that case, how to change the code to import as numbers. It will be very much appreciated if you can modified my code to show me the right way to do it.

    Thank you very much!

    Newguy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "the right way to do it" is asking too much, but here is aircode that seems prettier:


    assuming doubles suit your task and you are happy to use only one table:
    design a table tblOnly with the same number of fields as your numeric data - datatype double
    add field "source" - text - at the end
    add field "header" - text - at the (new) end

    Code:
    on error goto myErrorHandler
    
    const cstScrDir as string = "c:\ocs\record\" 'it seems to be constant and you save one concatenation by including the "\"
    
    dim RSonly as DAO.recordset
    dim srcprog as string	'target folder
    dim sHeader as string	'the date etc
    dim sSource as string	'the sourcefile
    dim sFile as string	'the filename
    dim sBuff as string	'line buffer
    dim sArray()		'the split-line array
    dim fnum as integer
    	
    srcprog = cstScrDir & programno
    fnum = freefile
    set RSonly = currentdb.openrecordset(tblOnly)
    
    sFile = dir(srcprog & "\*_0?.REC") 'assuming programno does not include the trailing "\"
    do while lenb(sFile) > 0
    	Open objFile For Input As #fnum
    	Line Input #fnum, sHeader 	'read and keep for later
    	Line Input #fnum, sBuff 	'read and ignore - nothing easier than dropping a text line!
    	docmd.setwarnings false 'no SQL action query here so shouldnt be any warnings: included just for fun
    	Do While Not EOF(#fnum)
    		Line Input #fnum, sBuff
    		sArray = Split(sBuff, vbTab)
    		RSonly.AddNew
    		For i = 0 To UBound(sArray)
    			RSonly(i) = cdbl(sArray(i))
    		Next
    		RSonly(i+1) = right$(sFile, 7) 	'source file
    		RSonly(i+2) = sHeader		'header stuff from first line
    		RSonly.Update
    	Loop 'till EOF
            Close #fnum
    	sFile = dir
    Loop 'till no more pattern-matching files in srcprog
    
    myExit:
    on error resume next 'cant afford to fall over in the tidyup
    Close #fnum
    RSonly.close
    set RSonly = nothing
    docmd.setwarnings true
    exit sub
    
    myErrorHandler:
    msgbox err.number & ": " & err.description
    resume myExit
    totally untested aircode - no idea if it will fly.

    izy

    aircode- HUH!
    Open objFile For Input As #fnum
    should have been
    Open sFile For Input As #fnum
    ...still aircode tho
    Last edited by izyrider; 10-30-08 at 16:07.
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2007
    Posts
    88

    Thank you so much!

    I am not smart enough to understand your code completely right away. Finally, I understand it now. It's really a very pretty code. It's so much better than the code I wrote. I will try to see how it works!
    Thank you sooooooo.... much!

    newguy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •