New here and need help

[xEVILFREDx]

Hello everyone,

Hope all is well for all. I do have a questions and hoping anyone can assist. So I'm importing a text file that looks like the below.

ID	Field1
1	Warning:
2	CorrelationID:
3	LogFileID: EXP_2018-05-01_05.15
4	<CA>
5	<Security identifyingSecurityID="19130364" identifyingSecurityIDType="NIS" CFICode="EXXXXX" issuerDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" issueDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" countryOfIssue="US" countryOfIss
6	<Security identifyingSecurityID="92206C870" identifyingSecurityIDType="333" CFICode="EXXXXX" issuerDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" issueDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" countryOfIssue="US" countryOfIs
7	<Security identifyingSecurityID="B4KH912" identifyingSecurityIDType="GB" CFICode="EXXXXX" issuerDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" issueDescription="VANGUARD INTERMEDIATE TERM CORPORATE BOND ETF" countryOfIssue="US" countryOfIssue
13	<CAFields>
14	<CAField status="CO" name="ExDate" value="20180501" type="D" />
15	<CAField status="CO" name="RecordDate" value="20180502" type="D" />
16	<CAField status="CO" name="DueBillOnDate" value="" type="D" />
17	<CAField status="CO" name="DueBillOffDate" value="" type="D" />
18	<CAField status="CO" name="MeetingDate" value="" type="D" />
19	<CAField status="CO" name="DueBillRedemptionDate" value="" type="D" />
20	<CAField status="CO" name="DeclarationDate" value="" type="D" />
21	<CAField status="CO" name="DueBillFlag" value="" type="S" />
22	<CAField status="CO" name="CancelCA" value="N" type="S" />
34	</CAFields>
83	<PayoutField status="CO" name="PayableDate" value="20180504" type="D" />
84	<PayoutField status="CO" name="GrossAmount" value="0.2373000000000" type="N" />
85	<PayoutField status="CO" name="IncomeTypeSource" value="IRSX" type="S" />
86	<PayoutField status="CO" name="ExchangeRate" value="" type="N" />
87	<PayoutField status="CO" name="GrossAmountStatus" value="" type="S" />
88	<PayoutField status="CO" name="ExemptionType" value="" type="S" />
89	<PayoutField status="CO" name="RateType" value="" type="S" />
90	<PayoutField status="CO" name="EarliestPayableDate" value="" type="D" />
91	<PayoutField status="CO" name="FeeCurrency" value="" type="S" />
92	<PayoutField status="CO" name="PayoutDeletedFlag" value="N" type="S" />
93	<PayoutField status="CO" name="CountryofIncomeSource" value="" type="S" />
94	<PayoutField status="CO" name="WHTaxRate" value="0.0000000000000" type="N" />
95	<PayoutField status="CO" name="Fee" value="" type="N" />
96	<PayoutField status="CO" name="IncomeType" value="0006" type="S" />
97	<PayoutField status="CO" name="NetAmount" value="" type="N" />
98	</PayoutFields>
99	</Payout>
100	</Payouts>
101	</Option>
102	</OptionsPayouts>
103	<CustodianData />
104	<CALinks />
105	<Text textChangedIndicator="N">
106	<Terms />
107	<ExtendedText />
108	<Restrictions />
109	</Text>
110	</CA>
111
112	<ADP_MESSAGE>
113	<Warning type="translation" severity="Warning" retry="no">Real Time Booking is set to Off for event type 'CD' </Warning>
114	</ADP_MESSAGE>
115
116	Translation warning
117
118	Warning:
119	CorrelationID:
120	LogFileID: EXP_2018-05-01_05.15
121	<CA>
122	<Security identifyingSecurityID="20178799" identifyingSecurityIDType="NIS" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
123	<Security identifyingSecurityID="92203J407" identifyingSecurityIDType="333" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
124	<Security identifyingSecurityID="BBDJJS1" identifyingSecurityIDType="GB" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
125	<Security identifyingSecurityID="BNDX" identifyingSecurityIDType="X03" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
126	<Security identifyingSecurityID="US92203J4076" identifyingSecurityIDType="997" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
127	<Security identifyingSecurityID="V007698" identifyingSecurityIDType="X01" CFICode="EXXXXX" issuerDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" issueDescription="VANGUARD TOTAL INTERNATIONAL BOND ETF" countryOfIssue="US" countryOfIssuer="US" />
128	<GeneralCAAttributes corporateActionKey="4755816" xspReferenceID="XSP4755816" corporateActionType="CD" dealNumber="1" addedDate="20180118" modifiedDate="20180501" modifiedBy="IDC" voluntaryMandatory="C" lastExportDate="201804231415" owner="" />
129	<CAStatuses workflowStatus="AP" corporateActionStatus="U" customerWorkflowStatus="BLANK" CAWorkStatus="U" confirmationStatus="CN" reassignedStatus="N" />

Question how can I write this rule where I take certain items and create a new line in a report. I might have to create another table to output this item.

Example: Line 5 - Security identifyingSecurityID="19130364 ** I just want to take Security 19130364



Also the next new line would be

118	Warning:
119	CorrelationID:

Any idea is awesome and thanks in advanced.

[Beetle]

Welcome to the forum. Can you provide more details? Specifically;

• Are you trying to extract the information from the text file during the import process?
• Are you attempting to do this after the entire text file has been imported into your Access application?
• If the latter, what is the structure of the table where the imported data resides?

Etc.

[xEVILFREDx]

Please see my response in the quote.

Welcome to the forum. Can you provide more details? Specifically;

• Are you trying to extract the information from the text file during the import process? Correct, a table was created as the original source is a txt file
• Are you attempting to do this after the entire text file has been imported into your Access application? Yes
• If the latter, what is the structure of the table where the imported data resides? Please see the attached image if that helps.

Etc.

Thanks Beetle!

[Beetle]

The code below assumes the following;

1) A temp table named tblOutput that would be used as the basis for the report record source (correct table name as needed for your application).
2) That your imported data consistently follows the pattern of the sample data you posted.

This would loop through the import table, look for the word "Warning" in Field1, then look for the first row after that that contains the word "Security", extract the relevant data from the beginning of that string and append to the temp table, then look for the next occurrence of the word "Warning", etc. etc.

    Dim strSQL As String, strResult As String
    Dim rs As Recordset, rs2 As Recordset
    
    strSQL = "DELETE *  " _
           & "FROM tblOutput;"
           
    CurrentDb.Execute strSQL, dbFailOnError
    
    strSQL = "SELECT * " _
           & "FROM tblImport " _
           & "ORDER BY ID;"
    
    With CurrentDb
        Set rs = .OpenRecordset(strSQL, dbOpenDynaset)
        With rs
            If Not .EOF Then
                .MoveFirst
                Do While Not .EOF
                If !Field1 = "Warning:" Then
                    strSQL = "SELECT * " _
                           & "FROM tblImport " _
                           & "WHERE ID>" & !ID _
                           & " ORDER BY ID;"
                    Set rs2 = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                    With rs2
                        If Not .EOF Then
                            .MoveFirst
                            Do While Not .EOF
                            If Nz(InStr(1, !Field1, "Security"), 0) > 0 Then
                                strResult = Mid(!Field1, 2, 8) & " " & Mid(!Field1, InStr(1, !Field1, "=") + 2, _
                                            InStr(InStr(1, !Field1, "="), !Field1, " ") - InStr(1, !Field1, "=") - 3)
                                strSQL = "INSERT INTO tblOutput (ID, Field1) " _
                                       & "VALUES (" & !ID & ", """ & strResult & """);"
                                CurrentDb.Execute strSQL, dbFailOnError
                                Exit Do
                            End If
                            .MoveNext
                            Loop
                        End If
                    End With
                End If
                .MoveNext
                Loop
            End If
        End With
    End With

Based on the sample import data you posted, the resulting data in the output table would be;




Keep in mind that this may not be highly efficient (i.e. fast) if your imported data set is large.