Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23

How to import text files (non-delimited) from filepath into a field so it's searchable

  1. #16
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    41,130
    You want to add OCR text to existing records. TransferText would create new record. So this requires opening and manipulating a text file object in VBA to update field of relevant record.

    To update all the existing records, will probably want to open a recordset of the table, loop through the recordset to read the file name, open the file, read text, update field (this is a crude example of pseudocode - writing out the steps needed to be coded).

    Then you might want a procedure to get the OCR text when a new record is created. So you could have 3 procedures. One procedure that has the code to do the actual manipulation of the text file and then two procedures that call the first one.

    The following code would be in a general module:
    Code:
    Function GetOCR(strFile As String) As Variant
    Dim objFSO As Object
    Dim objTextStream
    If Dir(strFile) <> "" Then
         Set objFSO = CreateObject("Scripting.FileSystemObject")'Open the text file
         Set objTextStream = objFSO.OpenTextFile(strFile, 1)
         'return the text string
         GetOCR = objTextStream.ReadAll
         'Close the file and clean up
         objTextStream.Close
         Set objTextStream = Nothing
         Set objFSO = Nothing
    Else
         GetOCR = Null 'or return a string like None
    End If
    End Function
    Then that function procedure can be called in other procedures. Here is code to populate the existing records. Run it once and maybe never again.
    Code:
    Sub UpdateRecordsOCR()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT ID, Textpath, OCRText FROM Writings WHERE NOT Textpath Is Null")
    While Not rs.EOF
         rs.Edit
         rs!OCRText = GetOCR(rs!Textpath)
         rs.Update
         rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    End Sub
    All tested and works for me.

    Now you can call the function whenever a new record is created and the filepath is available to pass to the function. Perhaps in a button Click event or Textpath textbox AfterUpdate. Simple example referencing textbox controls that have been named with a tbx prefix:



    If Not IsNull(Me.tbxTextpath) Then Me.tbxOCRText = GetOCR(Me.tbxTextpath)
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  2. #17
    Doug Simpson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    13
    Wow. I fogged around trying to figure out how to do this, but can't. I am not sure whether I put the VBA code in the form that I'm using or what, or where, of how to make it run, or what the variables or file names you have that might differ from mine.

    Any chance you could share a zip file so I can see how you did this--or would that even help?

    Thanks--I sure appreciate your thoughts and time on this.

    Doug

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    41,130
    I'd rather you post your attempts and describe what happens - error message, wrong results, nothing happens?

    Did you create a general module and put the function there? The UpdateRecordsOCR procedure can also go in the general module. The module cannot have same name as a procedure. This procedure can be run by placing the cursor anywhere in the procedure and clicking the Run button on the VBA editor. Suggest you test it on a small set of records, perhaps modify the SQL to restrict the recordset.
    Set rs = CurrentDb.OpenRecordset("SELECT ID, Textpath, OCRText FROM Writings WHERE NOT Textpath Is Null AND ID < 35514"). Remove the additional criteria when the test works.

    The function is generic, there are no objects of the database referenced. The UpdateRecordsOCR procedure references objects in your db and files you provided. You should be able to copy/paste the code into VBA and run it as is.

    Review http://www.functionx.com/vbaccess/Lesson01.htm can skip down to the section 'Microsoft Visual Basic Fundamentals' and the subsections 'Modules' and 'A module from scratch'
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  4. #19
    Doug Simpson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    13
    Well I've studied several VBA-code sites, including the one your referenced, then created and attempted to run the code. I get a compile error.

    Click image for larger version. 

Name:	Compile Error.jpg 
Views:	10 
Size:	62.5 KB 
ID:	29877
    Click image for larger version. 

Name:	Compile Error2.PNG 
Views:	10 
Size:	45.6 KB 
ID:	29878
    I see an "if" statement in the GetOCR function (line reads: "If Dir(strFile) <> "" Then"), and wonder whether I need to insert a path name in between the quotes there, to tell the code where to look, and what files need to be opened, read, then closed. But the compile-error message says that it is looking for a procedure, not a module.

    I have not created a button on a form to update a new record, for those times in the future where I want to update new records. Instead, so far, I've just tried to run the code, in an effort to update the records' OCRText field in the table "writings."

    This is like learning Greek. But I know learning Greek is possible. And I can order a beer in four languages, so maybe if I keep after this I can get it . . . eventually--at least enough to handle this task.

    Thanks for your help.

    Doug

  5. #20
    ssanfu is online now VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,630
    You do not "Call" modules. DO NOT name the module the same name as any sub/function .
    Maybe change the name of the module to "modGetOCR".

    What are you trying to do with the line
    Code:
    rs!OCRText = GetOCR(rs!Textpath)
    Is "GetOCR" a function??
    If you want to set rs!OCRText equal to rs!Textpath in the same record, that is what you write
    Code:
    rs!OCRText = rs!Textpath
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    41,130
    As I said in post 18: "module cannot have same name as a procedure"

    Create one module named UpdateOCR then paste both the function and the sub into that module. Delete the two modules you have now.

    Knowledge of basic programming concepts would be a great benefit. If you have zero programming background the learning curve for VBA will be a bit steep. All programming is application of logic and expressing that logic in executable code. Once you get past the hurdle of understanding concepts, learning new programming platforms is less challenging. I recently learned Android programming just from internet resources.

    ssanfu, GetOCR() is a UDF - see post 16.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #22
    ssanfu is online now VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,630
    I knew that!! ... but forgot.(head-slap)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #23
    Doug Simpson is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    13
    I pasted the code into one module of my test database--IT WORKS! Amazing!

    Next I'll work on a form button to see whether I can update records singly in the future.

    Then I'll see if I can run the code on the 70000 records in the main database.

    I can't tell you how much I appreciate your help on this. Yes, it's a steep learning curve--but I learned a lot on this so far.

    Thanks again.

    Doug


    Click image for larger version. 

Name:	Success.jpg 
Views:	6 
Size:	96.2 KB 
ID:	29885

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Specs and Text Files
    By Gloverb06 in forum Access
    Replies: 3
    Last Post: 03-15-2016, 12:43 PM
  2. Replies: 7
    Last Post: 11-20-2012, 04:08 PM
  3. Import Text Files without access
    By 95DSM in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 03:36 PM
  4. Field returns error when I import Text Files to Access
    By geng in forum Import/Export Data
    Replies: 3
    Last Post: 06-01-2010, 01:20 PM
  5. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 10:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums