How to Import a txt fixed with file with multiple data specifications it it.

[jnordfors]

I have a fixed with txt file that has multiple data specifications in it. I am using MS access 2013 to parse out the data.

Is there an easy way to apply the different specifications to the file without having to build separate queries in Access to parse it out?

[twinnyfo]

jnordfors,

Welcome to Bytes!

I "think" i know what you mean by "multiple data specifications" but it would be much more helpful if you were more descriptive.

If you mean that you have a fixed width text file in which some lines are laid out one way and other lines are laid out another, then I would abandon the idea of using queries to parse out this file.

I would use VBA and open the file as a text stream, input each line of text, evaluate each line and then import it accordingly. We manage hundreds of text files like this. The challenge is making sure you know the layout of each line of text and hope that the text format doesn't ever change.

However, for standardized text files, this process has proven extremely successful.

Hope this hepps!

[jnordfors]

That sounds like what I am dealing with. I do have the layouts of all the different record types.

However, each record type can have a different number of records to it depending on the file.

I can have 600 records in layout "A" on one day and 1000+ on a second day, this is the same with every layout.

Since I am a newbie to VBA is there some information you can point me to about this?

[twinnyfo]

I will give you the absolute basics. We can work through details if you need....

You will need to add "Microsoft Scripting Runtime" to your VBA Editor references.

Here are the basics:

Private Sub OpenMyTextFile()
    Dim fso     As FileSystemObject
    Dim ts      As TextStream
    Dim strText As String
 
    Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile(FileName:="My File Name")
    strText = ts.Readline
 
[Do whatcha gotta do here with the text string]
 
    Set ts = Nothing
    Set fso = nothing
End Sub

This will get you started, but I can only imagine that you will have much work to do. My code, which imports various text files runs about 1500 lines. Yours may be a more simple import. But, just be aware, that this will take some effort, depending upon how complicated and complex your data import is.

Hope this hepps!

[ADezii]

Sample Files, along with a Description of the Layouts, would be extremely helpful.

[jnordfors]

Hi

I built an import specification to import the text file as 1 column in a table in the database with a dialog box to be able to choose an import format and file location.

This is because we have different users that can parse out this data depending on the project they are working on.

I guess I need some help reading through the table once it is in access.

The format is attached, It is only for 2 record types. It looks like the first few elements up to the "Record Identifier" are standard across all the layouts some have an additional Identifier as in the "Field Type"

[jnordfors]

There are about 60 different specifications.

[twinnyfo]

In general, using the code from post #4 (with modifications, of course), it looks like you simply need to evaluate characters 30-31 of each line of text you import. If it is "01" it is for one type of record, if it is "02" then it is another type. Knowing this, you can pull any specific data based upon character locations and use the data as you wish.

I am not exactly sure what you mean by "60 different specifications." So that may require greater detail.

However, if all the specifications have characters 30-31 as the record identifier, you may be good to go with what I've just described.

Unless I am missing something obvious (which wouldn't be the first time).

[ADezii]

Unless I am mistaken, this is a fairly complex scenario. There are 60 distinct Specifications that have to be considered. The number of Records in each Specification is irrelevant. A Text File, conforming to any of 60 Specifications is Imported into a single Field in Access and is subsequently parsed based on that Specification details. If what I have stated is true, you may be able to accomplish this with two Related Tables and a Public Function. The Code, however, may be a little complicated, but there will be no need for any Queries or Import Specifications. Just let me know if my previous assumptions are correct, and I will see what I can come up with.

P.S.- The step of Importing each line into a single Field would also be eliminated. Each line would be added directly into the Master Table as Long as the Spec is known.

[ADezii]

One more question, do the Text Files have Field Names in the first row?

[jnordfors]

Hi ADezii,

You are correct in your assumption.

There are no field names in the first row.

I have been able to create a form that allows the user to browse for the file location and choose witch specification to use while importing

Thanks

[ADezii]

1. I am envisioning a System that will:
   1. Completely eliminate the need for any Queries.
   2. Eliminate the need for any Specifications, although the details of each Specification would need to be known just like in your Upload.
   3. Totally eliminate the step where each Line of the Fixed Width File is Imported into a single Field in a Table then parsed.
2. How I intend to go about this (not a simple Task as I see it):
   1. Use Low Level IO Functions to read each Line of the Fixed Width File, Parse each Line, then append the individual Values directly to a Table, all in a single step.
   2. The Field Names and Start and Stop positions for each Field would be contained in Reference Tables then read into the Code. This is the difficult part.
3. I created simplified Demo to illustrate how this can be done. The Logic makes some major assumptions which may/may not be true. Only you can make that determination.
4. Rather that post the Code, which is actually simple at this point, I will Upload the Demo.
5. Extract both the Demo (.mdb) and the Fixed Width File (.txt) into the 'same' Folder, or else it will not work. The Demo is partially based on the Participant Dates Specifications.
6. Interesting Project, let me know how you make out.

[twinnyfo]

Adezii,

Your approach is very similar to what I use. It is straightforward, but not simple. Thanks for fleshing this out for the OP!

Great work!

[ADezii]

Thanks twinnyfo, the hard work is yet to come!(LOL)

[ADezii]

1. I created a more detailed Demo for you that will:
   1. Allow you to select from one of two Specifications in a Combo Box.
   2. Opens a File Dialog Box filtered for Text Files (*.txt) only.
   3. Via the Office File Dialog, allows you to select a Text File. Once a File has been chosen, it will Open that File and use the selected Specification Parameters to populate a Table with it's contents, line-by-line.
   4. I have no way of knowing what Text Files would go with what Specifications, so that is something that you would have to deal with. Should you select a File that does not match a Specification then you will either generate a Run Time Error or gibberish will be displayed in the Table.
   5. For this Demo, it is obvious what File goes with what Specification.
2. The Code is somewhat complex, unorthodox, very detailed, and specific.
3. The advantages are that it does not require any Queries or named Specifications, and performs the Opening of the File, Parsing it, and Appending the Data into a Table in a single operation.
4. Should you have any questions, I imagine that there would be, feel free to ask. I can't speak for twinnyfo, but I am sure that he would be willing to assist also.

[jnordfors]

Hi ADezii

Well That certainly makes all the queries I built to do this not needed. And yes that is what I am looking to do.

So If I am thinking about this correctly, This would be one table with all the different layouts within it based on the field names from the specifications.

Then I would parse it out based off the individual Record Specifications... OR would this create a new table for the individual record Specs?. I guess I can do it either way.

So how can I adjust this to read a table with the different layouts in it so I don't have to program in each Record specification?

[ADezii]

I am a little confused, the Specification Names, Fields in the Specifications, and the Start and Stop positions of those Fields are in the Table. How else would you get this info in there? Maybe tninnyfo has a different perspective on this.

[jnordfors]

Hi ADezzi,

Sorry about that.

I have a table with all the specifications in it. I would rather refer to that table in the code rather then hard coding the specifications into the code itself.

Is there a way that I can do that?

I also found that some of the file layout specifications for what the Record Identifier are not just the characters 30-31 as I had originally thought.

So in my Specification table I have an indicator for the Record Identifier along with the Record Name, Starting positions and Length for each record.

I attached the table just so you can see what I am dealing with.

I hope that makes sense.

[ADezii]

I have a table with all the specifications in it. I would rather refer to that table in the code rather then hard coding the specifications into the code itself.

Is there a way that I can do that?

Yes there is. You can retrieve the precise information that you need by creating a Filtered Recordset based on TBL_RecordSpecifications.

1. I am assuming that the combination of [Companyname] and [RecordName] uniquely identifies a unique set of Records.
2. That being said, let's also assume that the User selects Company1/38 Record from a Combo Box.
3. The following Code, simulating a Combo Box selection by the use of CONSTANTS, will generate the [CompanyName], [RecordName], [FieldName]s, [StartingPosition]s, [EndingPosition]s, and [Length]s for the combination of Company1/38 Record:
   

   Dim strSQL As String
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
    
   '**** Simulate Combo Box Selection ****
   Const conCOMPANY_NAME = "Company1"
   Const conRECORD_NAME = "38 Record"
   '**************************************
    
   strSQL = "SELECT * FROM TBL_RecordSpecifications WHERE [CompanyName] = '" & _
             conCOMPANY_NAME & "' AND [RecordName] = '" & conRECORD_NAME & "'"
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    
   Debug.Print "Company"; Tab(12); "Record Name"; Tab(24); "Field Name"; Tab(61); _
               "Start"; Tab(70); "End"; Tab(77); "Length"
   Debug.Print String(83, "-")
    
   With rst
     Do While Not .EOF
       Debug.Print ![CompanyName]; Tab(12); ![RecordName]; Tab(24); ![FieldName]; _
                   Tab(62); ![StartingPosition]; Tab(70); ![EndingPosition]; Tab(78); _
                   ![Length]
         .MoveNext
     Loop
   End With
    
   Debug.Print String(83, "-")
    
   rst.Close
   Set rst = Nothing
   Set MyDB = Nothing

4. OUTPUT:
   

   Company    Record Name Field Name                           Start    End    Length
   -----------------------------------------------------------------------------------
   Company1   38 Record   PLAN NUMBER                            1       5       5 
   Company1   38 Record   FILLER                                 6       12      7 
   Company1   38 Record   SOCIAL SECURITY NUMBER                 13      23      11 
   Company1   38 Record   Account Identifier                     24      24      1 
   Company1   38 Record   FILLER                                 25      29      5 
   Company1   38 Record   RECORD IDENTIFIER                      30      31      2 
   Company1   38 Record   LOAN ID                                32      43      12 
   Company1   38 Record   PRINCIPAL BALANCE                      44      52      9 
   Company1   38 Record   INTEREST TYPE                          53      53      1 
   Company1   38 Record   SPACE                                  54      54      1 
   Company1   38 Record   REGULAR PAYMENT AMOUNT                 55      63      9 
   Company1   38 Record   LOAN REPAYMENT METHOD                  64      64      1 
   Company1   38 Record   SPACE                                  65      65      1 
   Company1   38 Record   DATE LAST PAYMENT RECEIVED             66      73      8 
   Company1   38 Record   SPACE                                  74      74      1 
   Company1   38 Record   SPACE                                  75      78      4 
   Company1   38 Record   AFTER DEEMED DISTRIBUTION INDICATOR    79      79      1 
   Company1   38 Record   FILLER                                 80      80      1 
   -----------------------------------------------------------------------------------

5. The next step would be to incorporate this Code into the Demo that I gave you.

[jnordfors]

Hi ADezii,
Thanks for that!

The unique identifier is [CompanyName].

What I am trying to do is to use the information in TBL_RecordSpecifications to parse out the data in participant dates.txt file in your example.

1. The [IsRecordIdentifier] in TBL_RecordSpecifications indicates what is needed to parse the participant dates.txt. (it’s a true/false field)

2. The [FilterIdentifier] has the information that is used to filter the data.

3. example; For the "02D Record" it has 2 items indicated as [IsRecordIdentifier] that need to be used to parse out he data in participant dates.txt

4. I need to run each record specification automatically based off the selected [CompanyName] in TBL_RecordSpecifications to parse out the data in participant dates.txt.

I just don't know how to make the compare of the 2 tables and run through all the [RecordName] per selected [CompanyName]

[ADezii]

This is getting to become very complicated to say the least, but I'll stay with it as long as I can, but I definitely need additional information before proceeding any further. There are 91 Records in TBL_RecordSpecifications where [CompanyName] = 'Company1" and [IsRecordIdentifier] = 1. I have only include six of these Records for reference minus the [StartingPosition], [EndingPosition], and [Length] Fields. Based on the Values of the [FilterIdentifier] Fields (3A, R, 49, 49ACH, 49ACHAD, and 49ACHBN), how would I know what to parse from the Fixed Width Text File? How do the Starting and Ending positions factor into the equation for these specific Records?

CompanyName  RecordName      FieldName          FilterIdentifier  IsRecordIdentifier    
Company1     3A Record       RECORD IDENTIFIER       3A                  1
Company1     47 Record       SWP INDICATOR           R                   1
Company1     49 Record       RECORD IDENTIFIER       49                  1
Company1     49ACH Record    RECORD IDENTIFIER       49ACH               1
Company1     49ACHAD Record  RECORD IDENTIFIER       49ACHAD             1        
Company1     49ACHBN Record  RECORD IDENTIFIER       49ACHBN             1

P.S. - Please ignore the Record Formatting, cannot get Identifier to place where it should be.

P.P.S. Formatting has been fixed [twinnyfo].

[jnordfors]

Hi,


I think I may be making this more difficult than it should be.

For example [RecordName] “3A Record”, has a number 1 in the [IsRecordIdentifier] and 3A in [FilterIdentifier] with the starting position, ending position and field length for the [FilterIdentifier].

I would go to the refenced position for the [FilterIdentifier] and filter for 3A.

Any data that has the 3A record is then placed into a table with the column headings located in [FieldName] based on their positions in the file.

I would then go to the next [RecordName] find the [IsRecordIdentifier]’s and [FilterIdentifier]’s for that record and repeat the process until all the [RecordName]’s had been completed for the selected [CompanyName].

Does that make sense?

I also attached a sample file of data.

[ADezii]

I'll give one more shot, and see if I am correct:

1. The below references are for CompanyName = 'Company1' and Recordname = '07DR Record'. There are four Record IDs for this combination.
   1. I would look in the Text File for 07 in positions 30-31, and if found write it to the [RECORD IDENTIFIER] Field.
   2. I would look in the Text File for D in positions 32-32, and if found write it to the [FIELD TYPE] Field.
   3. I would look in the Text File for 01 in positions 33-34, and if found write it to the [FIELD NUMBER] Field.
   4. I would look in the Text File for Y in positions 80-81, and if found write it to the [ROTH INDICATOR] Field.
   5. Values are written to the Table only if the [FilterIdentifier] exists in the [StartingPosition] to [EndingPosition] locations within the Textr File?
2. Please teel me if I am at least close? (LOL).

[jnordfors]

You are correct, however all 4 conditions need to be met for anything to be written to a table

As a Side note, if all 4 conditions are met then rest of the data for '07DR Record' will also be written to their respective fields in that table as well.

Example: Position 1-5 written to [Plan Number] ect....

I hope this makes sense.

I did program this into seperate queries for each 'Record'. I have to do this for 10-15 other 'Çompanies' with different layouts and it doesn't really scale well with individual queries having the specs hard coded into them.

[ADezii]

This will to difficult to program, if it can be done at all. You may need a Professional Programmer for this one, but I will give it an honest effort. This Task may simply be above my skill set. The two things that I will need right now are time (because of COVID-19 I have an abundance of), and a valid copy of Participant Dates.txt. If either you are pressed for time or I cannot obtain a working copy of Participant Dates.txt, with sensitive Date masked, then there will be no need to go any further. I`'ll check back in tomorrow.

[jnordfors]

Hi Adezii,

I have attached the sample data in a prior message :). It is named 'sampleCompany1.txt'

[ADezii]

1. I am going to have to pull away from this Project for am little while, but before I do, I want to post where we are at this point. What I am posting is a limited, work-in-progress which in hope you can carefully look at to see what is happening, as well as to give me some feedback.
2. You can actually Copy-N-Paste the Code to the Click() Event of a Command Button that you create on the previous Version that I had given you. Obviously, you will need to access TBL_RecordSpecifications and tblImports. The Code appears to work well in it's limited state.
3. Here are some points that are important to this Demo:
   • Instead of accessing a Text File, I use a String assigned to a Variable (strLine). I am nowhere near the point where I can input and analyze a Multi-Line Text File.
   • The [CompanyName] is also assigned to a variable (strCompany), whereas you would select this from a Combo Box.
   • Likewise, the [RecordNumber] is assigned to a variable (strRecordName) since I am not ready to deal with all the Record Numbers associated with Company1.
   • The 01 Record Specification has a single Record where the [IsRecordIdentifier] = 1. The associated [FilterIdentifier] is 01 which should exist in positions 30 and 31 of strLine.
   • If the Identifier exists all Fields in the Specification are appended to tblImports, and if not, the Append Operation is cancelled.
   • The hard parts are yet to come where I must access every Line of the Text File, Loop thru all Specifications instead of just 01 Record, and dealing with Records where there are multiple [IsRecordIdentifier]s (07DR Record). All associated [FilterIdentifier]s must be present in the Line or the Record cannot be appended.
   • In any event, have a look at what is done so far and let me know what you think.
   • Code Definition:
     

     Private Sub Command6_Click()
     Dim blnCanAppend As Boolean             'Indicates whether or not an APPEND can be performed
     Dim MyDB As DAO.Database
     Dim rstDistinct As DAO.Recordset        'Unique/Distinct [CompanyName]s
     Dim rstRecs As DAO.Recordset            'All Records belonging to each Unique [RecordName]
     Dim rstImports As DAO.Recordset         'Used to Append validated Records to MASTER
     Dim strSQL1 As String
      
     '************************************** To BE REMOVED **************************************
     Dim strCompany As String
       strCompany = "Company1"
     Dim strRecordName As String
       strRecordName = "01 Record"       'simulate choosing from Combo Box
     Dim strLine As String
       strLine = "PLAN1XXXXXXX999-99-7777ZXXXXX01LASTNAMELASTNAMELASTFIRSTNAMEFIRSTNXXXXXXXXXMFT"
     '*******************************************************************************************
     blnCanAppend = True         'Assume you cannot perform an APPEND Operation
      
     Set MyDB = CurrentDb
     Set rstImports = MyDB.OpenRecordset("tblImports", dbOpenDynaset, dbAppendOnly)   'APPEND only
      
     DoCmd.Hourglass True
      
     '*************************** FOR DEBUGGING PURPOSES ONLY ***************************
     Debug.Print "Company"; Tab(12); "Record#"; Tab(25); "Field Name"; Tab(53); _
                 "Filter ID"; Tab(65); "Is Record ID?"; Tab(84); "Start"; Tab(96); "End"
     Debug.Print String(101, "-")
     '***********************************************************************************
      
     'Create a Recordset that consists of all Records for each Unique [RecordName]
     strSQL1 = "SELECT * FROM TBL_RecordSpecifications WHERE [FieldName] NOT IN('FILLER','SPACE','SPACES','RESERVED') " & _
                         "AND [CompanyName] = '" & strCompany & "'"
     Set rstRecs = MyDB.OpenRecordset(strSQL1, dbOpenSnapshot)
      
     rstImports.AddNew
       Do While Not rstRecs.EOF
         If rstRecs![RecordName] = "01 Record" Then    'Simulate a Record Choice from the Combo Box
           If rstRecs![IsRecordIdentifier] = 1 Then
             'Does the [FilterIdentifier] exist in the Line at the designated Start/Stop Positions?
             'If not, don't Append Record, if yes to ALL [RecordIdentifier]s, then Append
             If Mid$(strLine, rstRecs![StartingPosition], (rstRecs![EndingPosition] - rstRecs![StartingPosition]) + 1) = _
                     rstRecs![FilterIdentifier] Then
      
               'Can Append the Record
               '*************************** FOR DEBUGGING PURPOSES ONLY ***************************
                'MsgBox rstRecs![IsRecordIdentifier] & " | " & rstRecs![FieldName] & " | " & _
                       'rstRecs![FilterIdentifier] & " | " & rstRecs![StartingPosition] & " | " & _
                       'rstRecs![EndingPosition]
               '***********************************************************************************
             Else
               'As soon as the [FilterIdentifier] does not match, do NOT Append
               blnCanAppend = False
             End If
          End If
           'Eliminate Dbl-Space problem in Fields
           rstImports.Fields(Replace(rstRecs![FieldName].Value, "  ", " ")) = Mid$(strLine, rstRecs![StartingPosition], (rstRecs![EndingPosition] - _
                                                                                            rstRecs![StartingPosition]) + 1)
      
           '********************************** FOR DEBUGGING PURPOSES ONLY **********************************
           'Debug.Print rstRecs![CompanyName]; Tab(12); rstRecs![RecordName]; Tab(25); rstRecs![FieldName]; _
                       'Tab(55); rstRecs![FilterIdentifier]; Tab(70); rstRecs![IsRecordIdentifier]; Tab(85); _
                       'rstRecs![StartingPosition]; Tab(96); rstRecs![EndingPosition]
           '*************************************************************************************************
          End If
            rstRecs.MoveNext
       Loop
         If blnCanAppend Then
           rstImports.Update: DoCmd.Hourglass False: DoCmd.OpenTable "tblImports", acViewNormal: Exit Sub    'REMOVE after rstImports.Update:
         Else
           rstImports.CancelUpdate: DoCmd.Hourglass False: Exit Sub
         End If
     DoCmd.Hourglass False
      

P.S. - I do believe that this is going to be a nightmare to maintain. I have already ran into problems such as dbl-spaces in the [FieldName] Field of TBL_RecordSpecifications which is why you see Replace(rstRecs![FieldName].Value, " ", " ") in the Code, cases where [StartingPosition], [EndingPosition], and [Length] are not in sync. [length] should equal (([EndingPosition] - [StartingPosition]) +1). These are just for starters.

[jnordfors]

THanks for the help. I will see what I can do with this. :)

[ADezii]

As stated in Post# 27, I am going to have to pull away from this Thread for personal reasons, but I did not want to leave you empty handed. I created a Demo that will hopefully point you in the right direction. I will also be checking back every couple of days should you have any questions.

1. Download the Attachment which contains two Files. Extract the Files to the 'same' Folder.
2. Participant Dates contains three line of Fixed Width Data. Two of these lines, (beginning with PLAN1 and PLAN3), conform to the [IsRecordIdentifier] AND [FilterIdentifier] criteria. These two lines will be appended to tblImports while the other, (beginning with PLAN2), will NOT be appended.
3. In the Combo Box, select the first Item named Company1 - 01 Record.
4. After the selection in the Combo Box, click the Command Button at the bottom of the Form. Select Participant Dates.txt then Open.
5. tblImports will be opened displaying the two Records.
6. Hopefully, I have helped you in some small form. The Coding is a little complex and somewhat convoluted, but it should be a good base to start from.
7. It is amply, but not overly, commented from my perspective.
8. In any event, download the Attachment and I'll check back in a couple of days.

[Rabbit]

Just a few observations and questions.

• Company name doesn't seem to be required. Unless you're saying that a record identifier can be used by different companies and that the same identifier have different specs?
• Finding the correct record type should be possible without having to choose the record type before hand by evaluating the identifiers until a matching one is found.
• Each different record type should be imported into a different table to save yourself headaches in the future.
• ADezii, you're doing a ton of work on this! Save something for the OP lol

[ADezii]

Hello rabbit, hope this Post finds you well.

Company name doesn't seem to be required. Unless you're saying that a record identifier can be used by different companies and that the same identifier have different specs?

I was just going by what the OP stated in Post# 20, namely:

The unique identifier is [CompanyName].

Finding the correct record type should be possible without having to choose the record type before hand by evaluating the identifiers until a matching one is found.

Please expand, I am open to all suggestions.

Each different record type should be imported into a different table to save yourself headaches in the future.

I agree, there are 71 Unique [RecordName]s/Specifications with 270 Unique [FieldName]s. The demo simply populates an Imports Table that contains the Field Names defined in the Specification synchronized with the *.txt File, plus a couple of others. The overall game plane is to dynamically build the Tables as each Specification is declared.[/quote]

ADezii, you're doing a ton of work on this! Save something for the OP lol

Ironically, were it not for Covid-19, I would never be able to devote this much time for a Thread. Stay safe.

[Rabbit]

Sorry ADezii, I should have clarified that the first bullet was for the OP to verify that company is actually needed to identify the correct spec.

As far as

Finding the correct record type should be possible without having to choose the record type before hand by evaluating the identifiers until a matching one is found.

The idea is that you can loop through all spec record identifiers and their restrictions for each input row to automate finding the correct record spec. Similar to what you already do to verify that the row conforms to the chosen spec, but instead, you do it for every spec and every input row. This uses more cpu cycles but is easier for the user as it will process the entire file without the user having to preselect the spec to use.

Something along the lines of

for each input row
    for each spec
        fully conforming = true
 
        for each spec requirement
            if input row does not conform then
                fully conforming = false
            end if
        next spec requirement
 
        if fully conforming then
            spec count += 1
            spec to use = spec
        end if
    next spec
 
    if spec count = 1 then
        process row with spec to use
    end if
next input row

[twinnyfo]

I'm thinking along similar lines to Rabbit, but it is unclear from OP's text file exactly what I am supposed to be looking for.

I guess my question is this: For each record in the Text File, is there a way to know the spec that applies to that record? And, does that particular info apply to "every record"? To me, it seems a bit silly to send someone a text file, filled with records, with no easily identifiable way to distinguish between what types of records are contained therein. I fully recognize that, apparently, this is the problem. But is there a way to get the data with some sort of record-type indicator that would speed this process dramatically, rather than checking each line to see if it meets the specifications for each specification. Agreed, this is CPU overload.

I import text files all the time. There are lines of these text files that contain data I need and lines that I don't need. I simply examine several characters in each line to determine if it is a line to keep or discard. The import process for each line can be quite complex, but determining which line to keep--in my case--is the easy part.

I'm just wondering if there is a way to get these data files in a more usable format?

[ADezii]

@twinnyfo & Rabbit:
As always, your Input is always appreciated and well respected. At this point, I'm not sure what is up or down with this Thread (LOL).