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

**jnordfors** · Aug 21 '20, 07:02 PM

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** · Aug 21 '20, 09:00 PM

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** · Aug 22 '20, 02:48 AM

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.

Attached Files

TBL_RecordSpecifications.txt (43.0 KB, 135 views)

**ADezii** · Aug 22 '20, 12:15 PM

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_RecordSpeci fications.

I am assuming that the combination of [Companyname] and [RecordName] uniquely identifies a unique set of Records.
That being said, let's also assume that the User selects Company1/38 Record from a Combo Box.

The following Code, simulating a Combo Box selection by the use of CONSTANTS, will generate the [CompanyName], [RecordName], [FieldName]s, [StartingPositio n]s, [EndingPosition]s, and [Length]s for the combination of Company1/38 Record:

Code:

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

OUTPUT:

Code:

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 
-----------------------------------------------------------------------------------

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

**jnordfors** · Aug 24 '20, 12:52 PM

Hi ADezii,
Thanks for that!

The unique identifier is [CompanyName].

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

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

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

3. example; For the "02D Record" it has 2 items indicated as [IsRecordIdentif ier] 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_RecordSpeci fications 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** · Aug 24 '20, 02:23 PM

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_RecordSpeci fications where [CompanyName] = 'Company1" and [IsRecordIdentif ier] = 1. I have only include six of these Records for reference minus the [StartingPositio n], [EndingPosition], and [Length] Fields. Based on the Values of the [FilterIdentifie r] 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?

Code:

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** · Aug 24 '20, 05:29 PM

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 [IsRecordIdentif ier] and 3A in [FilterIdentifie r] with the starting position, ending position and field length for the [FilterIdentifie r].

I would go to the refenced position for the [FilterIdentifie r] 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 [IsRecordIdentif ier]’s and [FilterIdentifie r]’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.

Attached Files

sampleCompany1.txt (16.3 KB, 122 views)

**ADezii** · Aug 24 '20, 06:38 PM

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

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 [FilterIdentifie r] exists in the [StartingPositio n] to [EndingPosition] locations within the Textr File?
Please teel me if I am at least close? (LOL).

**jnordfors** · Aug 24 '20, 09:20 PM

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** · Aug 24 '20, 10:34 PM

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** · Aug 25 '20, 12:22 PM

Hi Adezii,

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

**ADezii** · Aug 25 '20, 04:26 PM

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.
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_RecordSpeci fications and tblImports. The Code appears to work well in it's limited state.

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 [IsRecordIdentif ier] = 1. The associated [FilterIdentifie r] 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 [IsRecordIdentif ier]s (07DR Record). All associated [FilterIdentifie r]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:

Code:

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_RecordSpeci fications which is why you see Replace(rstRecs ![FieldName].Value, " ", " ") in the Code, cases where [StartingPositio n], [EndingPosition], and [Length] are not in sync. [length] should equal (([EndingPosition] - [StartingPositio n]) +1). These are just for starters.

**jnordfors** · Aug 26 '20, 10:26 AM

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

**ADezii** · Aug 26 '20, 02:42 PM

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.

Download the Attachment which contains two Files. Extract the Files to the 'same' Folder.
Participant Dates contains three line of Fixed Width Data. Two of these lines, (beginning with PLAN1 and PLAN3), conform to the [IsRecordIdentif ier] AND [FilterIdentifie r] criteria. These two lines will be appended to tblImports while the other, (beginning with PLAN2), will NOT be appended.
In the Combo Box, select the first Item named Company1 - 01 Record.
After the selection in the Combo Box, click the Command Button at the bottom of the Form. Select Participant Dates.txt then Open.
tblImports will be opened displaying the two Records.
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.
It is amply, but not overly, commented from my perspective.
In any event, download the Attachment and I'll check back in a couple of days.

Attached Files

Import Text Files_4.zip (56.8 KB, 64 views)

**Rabbit** · Aug 26 '20, 09:45 PM

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment