Need help with reading line by line a large .nc file converted .txt using Microsoft Access DAO Recordset.
I'm providing a sample database as to my attempts to read a linked txt file that I basically renamed from a .nc data file.
If you take a look - there is a module: modEExchange with a sub: PullNonValidTagLineItemsOn
ly that I'm trying to get working correctly.
To test you open the immediate window and enter: Call PullNonValidTagLineItemsOn
0016", 7) per eeexample2.png.
Basically I've identified some tags within this file that don't seem to find the ending </us-gaap and now I'm taking that and trying to go to that exact line no within that text file and put in a separate table until I come across either that ending </us-gaap or - the next 10 line items within that text file at least
I understand doing this can be tricky cause text files have limitations when you are looking at this line by line and the string limitations as to width. I made sure though the data file is long text for the Field1 and have linked the text file with the advanced link specifications in image: eeexample1.png.
Per eexample2.png - the first tag to come up is CommitmentsAndContingencie
s on line number: 19175 within the text file..based on the sSQL looping through the DAO.REcordset ... once I grab the first tag I loop through the linked table: 0001130310-19-000016 and am now trying to go to that exact line no - to start pulling that tag - into table: usgaapfileswtags_SUB as a sample of that data.
But as I go through using DAO.Recordset: rsTxt.... it reads line by line by stops at line 84, per image: eexample2.png shows.
I don't know why it doesn't go to line 19175 instead.
Manually - In eexample2.png you can see I can go in the linked table: - select 19175 - and go to that specific record line item - and get that tag no problem
In eexample3.png you see notepad ++ goes to line no 19175 - and there is the same information.
But here using DAO - if you notice in eexample2 - it stops on line no 84 and refuses to loop anymore.
So my question is - how do I make the dao recordset jump to that specific line no. within the linked text file and grab that record?
Here is the code:
Public Sub PullNonValidTagLineItemsOnly(sTextFile As String, lIDFileName As Long)
Dim sSQL As String
Dim rs As DAO.Recordset
Dim sSQLTxt As String
Dim sLineItem As String
Dim rsTxt As DAO.Recordset
Dim m As Long
Dim sTagName As String
Dim lLineNo As Long
Dim lDetailNo As Long
Dim lOrgFileswTags As Long
Dim sTagFull As String
Dim lLimit As Long
Dim lTimes As Long
sSQL = "SELECT usgaaptags.tagname, usgaapfileswtags.usgaaplineno, usgaapfileswtags.usgaapvalid, usgaapfileswtags.usgaapfilesid, usgaapfileswtags.usgaapdetailid, usgaapfileswtags.usgaaprow"
sSQL = sSQL & " FROM usgaaptags INNER JOIN usgaapfileswtags ON usgaaptags.tagid = usgaapfileswtags.tagid"
sSQL = sSQL & " WHERE [usgaapfileswtags].[usgaapvalid] = False And [usgaapfileswtags].[usgaapfilesid] = " & lIDFileName
Set rs = CurrentDb.OpenRecordset(sSQL)
Do Until rs.EOF
sTagName = rs.Fields(0)
lLineNo = Nz(rs.Fields(1), 0)
lOrgFileswTags = rs.Fields(3)
lDetailNo = rs.Fields(4)
sTagFull = Nz(rs.Fields(5), "")
'now loop through and grab those from here...
'find the lineno and do this to go in for 10 tries at the most...
'or warn couldn't find it..
m = 0
sSQLTxt = "SELECT * FROM [" & sTextFile & "]"
Set rsTxt = CurrentDb.OpenRecordset(sSQL)
Do Until rsTxt.EOF
m = m + 1
'If m = 22783 Then Stop
'sLineItem = Nz(rsTxt.Fields(0), "")
If m = lLineNo Then
sLineItem = Nz(rsTxt.Fields(0), "")
sSQL = "INSERT INTO [usgaapfileswtags_SUB] (usgaapdetailid, usgaaplineno, usgaaprowsub)"
sSQL = sSQL & " VALUES (" & lDetailNo & ", " & m & ", '" & sLineItem & "')"
If sLineItem Like "*</us-gaap:*" Or lTimes > lLimit Then
Set rsTxt = Nothing
lLimit = 10
lTimes = 0
Set rs = Nothing