Inserting text files into access

Post a couple of rows of the text file here.

Here is what I am dealing with.  I will post an example.  Idealy I want this done in Visual Basic but I am open to suggestions.

text1.txt - file does not exist or cannot be accessed ??

test1.txt

GRayL, you should be able to open the file.  Click on test1.txt please.

If you're looking to do this in VBA it is definitely possible to parse this out manually.  Just open the text file and loop through it, line by line, parsing the text and running INSERT statements along the way.

I was thinking the same thing.  The only thing that I need out of the text files is the table data like patient #, adm#, etc.  I will attach a more accurate rendition of the type of file I am looking at.  Hold on as I process the file please.

Strange, it opened without a problem.  BTW, There is no field separator.  This is a fixed width file.  You have to open the text file and remove the top portion that is not data.  Save the file, exit, open Access, File, Get External Data..., Import, set the Type to CSV, Text, etc., when the Wizard opens, check Fixed width, and go from there.

Here is a revision of the attached file.  Each "page" of information consists of 45 records and there are over 120 pages of information.  What I would like to do is just grab the table data information and run from there.  You may notice that the first page of 45 records are the same as the second page, please ignore this.  There are unique records for every record recorded from here.  Please see attached file.

Well, i have to automate this task, is there a way to remove everything except the table data or field data?

Any way you can go back to the source and ask them to remove the pagination and top and bottom info lines so that the data portion is contiguous - unbroken?

If all the Patient ID's begin with '1244' then open the file in VBA, store each line in a variable l, examine trim(l) and if left(trim(l))<>"1244", ignore and loop to the next.  If so, copy the line to a new file.  The new file should have nothing but fixed-width fields.  

Actually GRayL, I only took a few of the first records and ran this text file.  It starts from 124xxxxxx and it goes all the way to 72852635.  They run this batch everyday, I'd imagine that there would be even higher numbers after this text file

OK, then looking at it another way, each line with data after trimming is 126 characters long.  So if len(trim(l))<>126, then ignore it.  Seem like a plan?

Or, if your header is of a fixed format then you'll always know when your data actually starts.

So, in theory:

'Start Import

'Loop through lines until you get to "Patient #"

If Left(Data, 9) = "Patient #" then
   'Go to current Line + 8
 
   If IsNumeric(Left(Data, 8) then
      'Loop through rows collecting data until  Left
   Else
      'Encountered header on page 2 or file is finished
   end if

How do you skip to the next line if you are doing a readline?

Just read the next line and don't do anything with it

Give an example please.

I borrowed this example from here (http://www.experts-exchange.com/Programming/Languages/.NET/Q_23013668.html?sfQueryTermInfo=1+file+read+text)

Ok, so this example is just a rough outline, but basically you'd just put a loop in for the number of rows you wanted to skip.  (If you only wanted to get to the next line, then you wouldn't need the loop)  Every time you use the ReadLine command, it automatically goes to the next line.

    'Open a file for reading
    Dim FILENAME as String = Server.MapPath("yourFile.txt")
 
    'Get a StreamReader class that can be used to read the file
    Dim objStreamReader as StreamReader
    objStreamReader = File.OpenText(FILENAME)
 
    dim someString as String
    While objStreamReader.Peek() <> -1
      someString = objStreamReader.ReadLine()
      'Process data here...
 
      'Skip X Lines
       For iIndex as Integer = 0 to X
          someString = objStreamReader.ReadLine()
       Next
    End While
 
    objStreamReader.Close()

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Hi Torrwin, when inserting the data into a spreadsheet or database table, I want to be able to use this format and insert the data one record at a time.  Perhaps you can look at the file that is attached, but I want to be able to insert by way of a delimited file.  How could I do this?  Thanks!