Results 1 to 5 of 5
  1. #1
    Join Date
    May 2005
    Provided Answers: 1

    Question Unanswered: Import routine for a text file on specific rows of data

    Hi, I have searched the forums, but can not find anything specific to my requirement, so I thought I would put it to the comunity

    I am using access 2003

    Hi I am currently working on a new project, where the end user runs a report and generates a text file, the text file is always in the same format/layout, please find attached file

    What I need to do is import specific rows/columns of data, within the attached file

    e.g. if i just wanted to import R15 C56 - C61, the resulting import into the database would be 76.829

    In reality I will need to import mutiple Rows/Columns of data within each text file.

    How do I go about this, Thanks
    Attached Files Attached Files
    Last edited by MarkWhyte; 03-01-10 at 06:33. Reason: no attachment

  2. #2
    Join Date
    Jan 2002
    Bay Area
    The attached file is my solution to your text file import question. There may be a better and easier way, and I am always eager to learn from suggestions and corrections from anyone.
    The procedure imports 8 fields from your text file in "c:\Temp\MA012203.txt". The table key is the batch date & time on line #1 of the text file. You would need to set up a way to manage importing different file names if the name changes. Here is the code from Module1:

    Private Function Import_Text()
    Dim WholeLine As String, TextIn() As String
    Dim lineCount As Integer, RowNumb As Integer
    Dim recCount As Long
    Dim i As Integer, j As Integer
    Dim tblName As String
    Dim sqlText As String
    Dim keyCheck
    'imported values will be assigned to the following variables
    Dim batchEnded_ As String
    Dim shiftStart_ As String
    Dim shiftEnd_ As String
    Dim idleTime_ As String
    Dim activeTime_ As String
    Dim pausedTime_ As String
    Dim emptyTime_ As String
    Dim sysErrorTime_ As String
    fName = "c:\Temp\MA012203.txt"
    rowsToImport = Array(1, 11, 13, 15, 16, 17, 18, 19)
    tblName = "Feeder Processing"
    lineCount = rowsToImport(UBound(rowsToImport))   'import data occurs in rows 1-lineCount
    ReDim TextIn(UBound(rowsToImport))
    Close #1
    Open fName For Input Access Read As #1
    RowNumb = 0
    'count lines
    While Not EOF(1)
        Line Input #1, WholeLine
        RowNumb = RowNumb + 1
    If RowNumb < lineCount Then
        MsgBox "The file " & fName & " is incomplete. Exiting."
        Exit Function
    End If
    Close #1
    Open fName For Input Access Read As #1
    j = 0
    For i = 0 To lineCount - 1
        Line Input #1, WholeLine
        If i + 1 = rowsToImport(j) Then
            TextIn(j) = WholeLine
            j = j + 1
        End If
    Next i
    Close #1
    batchEnded_ = Mid(TextIn(0), 1, 17)
    On Error Resume Next
    recCount = DCount("*", tblName)
    On Error GoTo 0
    If recCount > 0 Then
        keyCheck = DLookup("[BatchEnded]", tblName, _
            "[BatchEnded]='" & batchEnded_ & "'")
        If Not IsNull(keyCheck) Then
            MsgBox "Records for this batch entered on " & batchEnded_ & vbCrLf & _
            "have already been entered into the table """ _
            & tblName & """." & vbCrLf & "Exiting."
            Exit Function
        End If
    End If
    shiftStart_ = Mid(TextIn(1), 54, 8)
    shiftEnd_ = Mid(TextIn(2), 54, 8)
    idleTime_ = Mid(TextIn(3), 45, 8)
    activeTime_ = Mid(TextIn(4), 45, 8)
    pausedTime_ = Mid(TextIn(5), 45, 8)
    emptyTime_ = Mid(TextIn(6), 45, 8)
    sysErrorTime_ = Mid(TextIn(7), 45, 8)
    sqlText = "INSERT INTO [" & tblName & "] "
    sqlText = sqlText & "([BatchEnded], [ShiftStart], [ShiftEnd], "
    sqlText = sqlText & "[IdleTime], [ActiveTime], [PausedTime], "
    sqlText = sqlText & "[EmptyTime], [SysErrorTime]) "
    sqlText = sqlText & "VALUES(""" & batchEnded_ & """,""" & shiftStart_ & ""","""
    sqlText = sqlText & shiftEnd_ & """,""" & idleTime_ & ""","""
    sqlText = sqlText & activeTime_ & """,""" & pausedTime_ & ""","""
    sqlText = sqlText & emptyTime_ & """,""" & sysErrorTime_ & """);"
    DoCmd.SetWarnings False
    DoCmd****nSQL sqlText
    DoCmd.SetWarnings True
    End Function
    Attached Files Attached Files
    Last edited by JerryDal; 03-04-10 at 22:31. Reason: DLookup produced 'Null' error. Changed keyCheck type to Variant.

  3. #3
    Join Date
    May 2005
    Provided Answers: 1
    Hi Jerry,

    Thank you for your help so far, and sorry to be a pain, but I cannot get the code to run, if I try to run the code from the design view , it runs beautifully, but If I try to assign a macro to run the code, using runcode feature, I get this message

    “The expression has a function name that Micro Soft Office can’t find”

    Could you give me guidance on how I can get this to run

    Many Thanks


  4. #4
    Join Date
    Aug 2009
    Up Nort' Wi
    Have you changed to "Private" to "Public" at the declaration of the function and put it in it's own module?

    ::Edit:: oh yeah, don't forget to turn "docmd****nsql" to "d o c m d. r u n s q l" (without the spaces)

    Sam, hth
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    May 2005
    Provided Answers: 1

    Quote Originally Posted by SCrandall View Post
    Have you changed to "Private" to "Public" at the declaration of the function and put it in it's own module?
    Did the trick


Posting Permissions

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