Results 1 to 10 of 10
  1. #1
    Insyderznf is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    16

    Remove/Ignore beginning of text file on import

    Hello all,

    I'm trying to setup a way to import a text file using a vba import command with the example below:


    Code:
     Report Name
    
    
    *Report Criteria:*     *Report Date:  * October 19, 2011
    ------------------------------------------------------------------------
       IRMS:     9999 - LONG CORPORATE NAME
       Stuff: Things
       Stuff: Things
       Stuff: Things
       Stuff: Things
       Stuff: Things
       Stuff: Things     Stuff: Things
       Stuff: Things     Stuff: Things
       Stuff: Things    Stuff: Things
       Stuff: Things     Stuff: Things
    ------------------------------------------------------------------------
     
    
     
      
           Stuff and Things      Stuff and Things      Stuff and Things      Stuff and Things 
    Stuff and Things      Stuff and Things      Stuff and Things      Stuff and Things
    Stuff and Things     Stuff and Things      Stuff and Things      Stuff and Things
    Stuff and Things     Stuff and Things      Stuff and Things      Stuff and Things
    Stuff and Things      Stuff and Things 
         
    ------------------------------------------------------------------------
     
          0008AA     HHR     ABC     01/01/2001     Y     20     10     0     0     0     30     0     0     0     30     30
          0009AA     HHR     ABC     01/01/2001     Y     29     21     0     0     0     50     0     0     0     50     50
          0011AA     HHR     ABC     01/01/2001     Y     7     13     0     0     0     20     0     0     0     20     20
          0011AA     HHR     ABC     01/01/2001     Y     2     48     0     0     0     50     0     0     0     50     50
          0034Z     HHR     ABC     01/01/2001     Y     2     28     0     0     0     30     0     0     0     30     30
          0036Z     HHR     ABC     01/01/2001     N     6     4     0     0     0     10     0     0     0     10     10
          0045AA     HHR     ABC     01/01/2001     Y     15     5     0     0     0     20     0     0     0     20     20
    Is there a way I can set the import to ignore the first part of this information and instead start at the actual data? The top data should always be the same. My thoughts for this are as follows:

    Would it be possible find the length of the last of the ------- section and start from the end of that?

    Would it be possible to start at a certain amount of carriage returns?

    What else could I do or how would I execute either of the above information?



    Thank you very much for any help you can give.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I think would have to read each line and either import or reject depending on some criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Insyderznf is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    16
    I'm going to just play around and see if i can come up with anything. I did something similar in php/mysql with no problems but VBA doesn't seem to have the functions i need.

  4. #4
    Stingaway is offline Efficiency Junkie
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Location
    The deep south. Keep going until you hit water basically.
    Posts
    224
    You can do this in VBA - reading in each line at a time, just give it the number of lines to skip (assuming that stays the same) or identify a footer line to wait for (example '------') before starting.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Yes, VBA can read line at a time and yes, there is no intrinsic function to accomplish. Have write custom procedure.

    Google: vba import csv into access table

    Here is one:
    http://forums.aspfree.com/microsoft-...vba-30159.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Insyderznf is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    16
    Okay I'm going crazy trying to figure this out . I would be okay but I can't figure out how to skip to a certain line or start at a footer like suggested above.

    I'm using readline is there something else I should be using?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I have never had to do this but will throw something out there. If data always starts on same line (say number 50) then increment a counter variable as each line is read. If count is less than the starting line, reject the import, else import.

    Dim i as Integer
    i = 1
    While Not File.EOF 'or whatever test for end of file is used with a text file
    'code to read line
    If i > 49 Then 'code to import
    i = i + 1
    'code to move to next line
    Wend
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Insyderznf is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    16
    June,

    I was just thinking about that this morning. I'll give it a try! Thank You.

    My other thoughts were to count the spaces in the string and if it did not have X amount of spaces ignore it. I wasn't able to get the count spaces code to work the way I wanted it to yet though.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Show your attempted code if you want us to analyze. The point is, must have some criteria that can be relied on for consistency.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Insyderznf is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    16

    Solved- Thanks!

    Here is what I ended up with after I found the all lines I wanted started with space tab. It really helped to that there were tabs between all of the columns as well. If it wasn't for this I would still be trying to figure out what to do.

    I realize I could condense portions of this code but it's easier for me to read this way.

    Thank you very much for all your suggestions.

    Code:
    Do While Not MyFile.AtEndOfStream
          InBuffer = MyFile.ReadLine    '-- Bring in the next Input Line
    
                Dim firstTab As Integer
                Dim secondTab As Integer
                Dim newBuffer As String
                Dim tblValue(20) As String ' Array to save values
                Dim i As Integer
                
                'Two spaces for lot number
                If Mid(InBuffer, 1, 3) = "  " & vbTab Then 'Check for lines that start with 2 spaces and a tab
                    newBuffer = Trim(InBuffer) 'trim the spaces off both sides, string has to be clean
                    i = 1 ' counter
                        Do While Len(newBuffer) > 0 'The code will remove portions of the string so when at 0 end loop
                            newBuffer = LTrim(newBuffer)
                            firstTab = InStr(1, newBuffer, vbTab) 'Start of string to process
                            secondTab = InStr(2, newBuffer, vbTab) 'end of string to Process
                            
                            If secondTab = 0 Then 'different code for the end of the string when there is no tab
                                tblValue(i) = Mid(newBuffer, firstTab)
                                newBuffer = ""
                            Else
                                '+1 skips first tab, -3 because there are 3 spaces between items for this particular file
                                tblValue(i) = Mid(newBuffer, firstTab + 1, secondTab - 3)
                                newBuffer = Mid(newBuffer, secondTab)
                                i = i + 1
                            End If
                        Loop
                        
                        With MyRs
                           .AddNew
                           !Lot_Number = tblValue(1)
                           !Vaccine_Name = tblValue(2)
                           !Manufacturer = tblValue(3)
                           !Expires = tblValue(4)
                           .Update
                        End With
                Else
                
                End If
       Loop

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to remove blank rows when I import from excel
    By timpepu in forum Import/Export Data
    Replies: 3
    Last Post: 09-14-2015, 04:04 PM
  2. Automaically Import Text File in Access Form
    By ract123 in forum Import/Export Data
    Replies: 1
    Last Post: 06-13-2011, 09:06 AM
  3. Import Text File Size Limit
    By wfbp in forum Import/Export Data
    Replies: 1
    Last Post: 11-04-2010, 09:05 AM
  4. Replies: 2
    Last Post: 08-17-2010, 02:58 PM
  5. Import text file question
    By sumdumgai in forum Import/Export Data
    Replies: 14
    Last Post: 03-23-2010, 07:59 AM

Posting Permissions

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