Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Using Access to scrape a report? (supplementing Monarch)

    Is it possible to scrape a text formatted report into a table using Access VBA? I'm trying to get around using the program Monarch because I am limited in my ability to join multiple tables. I realize Access is not built for this functionality specifically but I also know that I've been surprised before what can be done in VBA. I realize it would be easier pulling from the source data however, at my new place of employment, I've been barred from that and even creating custom reports. Coming from a company with an AS400 based ERP with open QGPL access to files, this limitation has been a huge frustration for me. Does anybody have any sample code for scraping or any ideas on how it might be done? Thanks - JB

  2. #2
    Join Date
    May 2010
    Posts
    601
    I call this parsing. Basic/VB/VBA has lots of stuff to allow you to to parse a text file.

    I have been transferring data between software packages for years. Since most system do not have an export feature, I capture reports to a text file. I use VBA to read the file and import the data. I start out doing this pre-windows with Microsoft's Professional Basic 7. I still use some of the same code in VB and also in Access VBA.


    Here is an example with Access 2000 or later that will read a file and convert LF to CRLF

    Code:
    Dim FH As Integer
    Dim FH2 As Integer
    
    Dim strPath As String
    Dim strPathOut As String
    Dim strLineIn As String
    Dim strLineOut As String
    
    strPath = "c:\report.txt"
    strPathOut = "c:\report2.txt"
    
    
    FH = FreeFile
    
    Open strPath For Input As #FH
    
    FH2 = FreeFile
    
    Open strPathOut For Output As #FH2
    
    Do
       Line Input #FH, strLineIn
       
       strLineOut = Replace(strLineIn, Chr(10), vbCrLf)
       
       
       Print #FH2, strLineOut
      
       
    Loop Until EOF(FH)
    
    Close #FH
    Close #FH2

    Hope this helps get you started.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I have parsed text files and imported as tables before using DoCmd.TransferText acImportDelim but how would I parse a text formatted report with headers, footers, grouping and totals. Basically I just want to scrape the detail lines and possibly append the group header (branch number) to each of the details underneath it. I tried this w/o VBA by linking to the text file, setting up fixed width for the detail and then limiting to get to the detail using a query of that table but I cannot think how to append the branch number to each detail. Thanks - Joshua

  4. #4
    Join Date
    May 2010
    Posts
    601
    I normally do not use DoCmd.TransferText acImportDelim...

    I prefer to write VBA code to process the file.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I had to do the same thing several years ago. The key is reading from the text file and being able to identify each line as a Report Header, Page Header, Report Footer, Page Footer, White Space, Detail Heading, or a Detail Line. When you are able to determine each of those items you will be able to get the information out of the report that you want. One problem I remember having was lines that were more than 80 characters would wrap and you would have one detail line on multiple lines in the text file. That was time consuming figuring our how to concatenate multiple lines.

    But if you don't have that issue then here is part of the code I wrote. I tried to post all of it but it was too long. Here is the important code:

    Code:
    Public Sub CreateWorkFile(strRawFilename As String, _
                              strWorkFilename As String)
    
        Dim intRawFileID As Integer
        Dim intWorkFileID As Integer
    
        Dim booReportFooter As Boolean
        Dim booSkipData As Boolean
        Dim strLineData As String
        Dim strTrimData As String
        
        'Kill the destination and the work file before beginning
        On Error Resume Next
        Kill strWorkFilename
        On Error GoTo 0
        
        'Get a file ID, and open the raw data file
        intRawFileID = FreeFile(1)
        Open strRawFilename For Input As #intRawFileID
    
        'Get a file ID, and open the clean data file
        intWorkFileID = FreeFile(1)
        Open strWorkFilename For Append As #intWorkFileID
    
        SkipReportHeader intRawFileID, strLineData
        SkipPageHeader intRawFileID, strLineData
        
        booReportFooter = False
        
        While Not EOF(intRawFileID) And Not booReportFooter
            
            booSkipData = False
            
            strLineData = ReadLineCRLF(intRawFileID)
            strTrimData = Trim(strLineData)
                
            'Check to see if the data is a footer
            If IsPageFooter(strLineData) Then
                booSkipData = True
                SkipPageHeader intRawFileID, strLineData
            End If
            
            'If the current line is a Report Footer then get out of the loop
            booReportFooter = IsReportFooter(strLineData)
            If booReportFooter Then booSkipData = True
                    
            If strTrimData & "" = "" Then booSkipData = True
            
            'If the record starts with HISTORY (added by ADP) then skip it
            If Left(strTrimData, 7) = "HISTORY" Then booSkipData = True
            
            If Not booSkipData Then
                Print #intWorkFileID, strLineData
            End If
                
        Wend
        
        Close intRawFileID
        Close intWorkFileID
    
    End Sub
    
    Private Sub SkipReportHeader(intFileID As Integer, _
                                 strData As String)
    
    'Description :      This procedure will move the file pointer ahead to the first Page Header in the file
    '
    'Parameters :       intFileID       The file ID for the file where the Report Header is being skipped.
    '                   strData         The line of text from the file.
    '
    'Return :           strData         Will be changed to the first Page Header in the file.
    '
    '03/04/04 DCK   Original function.
    
        Dim strTrimData As String
        Dim strCondition
        
        strTrimData = Trim(strData)
        'First check to see if you on the Report Header, if not start skipping
        strCondition = BuildCondition(gstrReportHeaderCue, strTrimData)
        If Not Eval(strCondition) Then
        
            'Start skipping lines until you reach the first Page Header or the end of the file
            Do
                strCondition = BuildCondition(gstrReportHeaderCue, strTrimData)
                If Eval(strCondition) Then Exit Do
                strData = ReadLineCRLF(intFileID)
                strTrimData = TrimData(strData)
                
            Loop While Not EOF(intFileID)
            
        End If
        
    End Sub
    
    Private Sub SkipPageHeader(intFileID As Integer, _
                               strData As String)
                               
    
    'Description :      This procedure will move the file pointer ahead to the first Page Header in the file
    '
    'Parameters :       intFileID       The file ID for the file where the Report Header is being skipped.
    '                   strData         The line of text from the file.
    '
    'Return :           strData         Will be changed to the first Page Header in the file.
    '
    '03/04/04 DCK   Original function.
    
        Dim strTrimData As String
        Dim strCondition As String
    
        strTrimData = Trim(strData)
        'First check to see if you on the Report Header, if not start skipping
        strCondition = BuildCondition(gstrReportHeaderCue, strTrimData)
        If Not Eval(strCondition) Then
        
            'Start skipping lines until you reach the first Page Header or the end of the file
            Do While Not EOF(intFileID)
            
                strData = ReadLineCRLF(intFileID)
                strTrimData = TrimData(strData)
                strCondition = BuildCondition(gstrPageHeaderCue, strTrimData)
                If Eval(strCondition) Then Exit Do
            
            Loop
        End If
                
        'Throw away next line since it should the rest of the two line Page Header
        strData = ReadLineCRLF(intFileID)
        
    End Sub
    
    Private Function IsPageFooter(strData As String) As Boolean
    
    'Description :      This function will determine if, in the file, we have reached the end of a screen.
    '
    'Parameters :       strData         The line of text from the file.
    '
    'Return :           Boolean. Whether or not the line is the line that shows at the end of a screen.
    '
    '03/04/04 DCK   Original function.
    
        Dim strTrimData As String
        Dim strCondition As String
        
        strTrimData = TrimData(strData)
        
        strCondition = BuildCondition(gstrPageFooterCue, strTrimData)
        
        IsPageFooter = False
        
        If Eval(strCondition) Then
            IsPageFooter = True
        End If
        
    End Function
    
    Public Function BuildCondition(strCondition As String, _
                                   strData) As String
    
        Dim strString As String
        Dim strFunction As String
        Dim lngLength As String
        
        strFunction = Piece(strCondition, "`", 1)
        strString = Piece(strCondition, "`", 2)
        lngLength = Len(strString)
        
        BuildCondition = strFunction & "(""" & strData & """, " & lngLength & ")=""" & strString & """"
    
    End Function
    The BuildCondition function worked off a table that stored key text that helped to identify the Report Header/Footer and Page Header Footer. It also stored whether to read from the Right or the Left. Keep in mind the code won't work it is not complete. But it will give you an idea of what to do.

Posting Permissions

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