Query a linked text file

[J Shrimps, Jr.]

Have a linked delimited text file with
several thousand rows. Text
file is created from IBM main frame.
There is one row ("header row")
where the first three characters are ".//"
uniquely identifying all the records
that follow, until the next header row.

Going to query the header row only,
'cause in the 13th position of that row,
and for the next eight positions, contain
the name of the program (file is a dump
of all JCL programs for a person's account
- rows 1 - 50 could be program one,
rows 51 -72 will be the next program,
rows 73 - 90 the next, etc,
but the first line of each program
starts with ".//".)

So, I want to query the linked text file
using mid([Field1],13,8) on rows that
start with ".//" , and return every
consecutive row following, until the next
"header row" - again, starting with
".//" - which means I have all the records
associated with the header row I queried.

Do I have to use some kind of
db.recordset
rs.movenext
type thing for this?

 

[John Nurick]

I'd probably do this by "munging" the textfile into a standard format,
with each row complete in itself rather than depending on a preceding
group header. Then link to the munged file and select the records you
want with a query in the usual way.

Here's a VBScript that should do the job, maybe with minor changes.

If you need to automate the whole process, the VBScript code doesn't
need much change to make it work as an Access VBA procedure.

'---------------Start of VBscript code --------------
'Prepend.vbs
'VBScript
'Call from commandline as
' cscript Prepend.vbs GroupTag InFile OutFile
'where GroupTag is the string that begins a group.
'E.g. if the data is
'
'Group # 1
'First Line
'Second Line
'Group # 2
'First Line
'Second Line
'Third Line
'
'use a command line like
' cscript Prepend.vbs "Group # " Infile.txt Outfile.txt
'to get the output
'1,First Line
'1,Second Line
'2,First Line
'2,Second Line
'2,Third Line


Option Explicit

Dim fso 'As FileSystemObject
Dim fIn 'As TextStream
Dim fOut 'As TextStream
Dim strLine 'As String
Dim strTest 'As String
Dim strTag 'As String
Dim DELIM 'As String

DELIM = "," 'String to separate the "group" data
'from the rest of the line. Change this
'as needed: e.g. to Chr(9) for Tab

Set fso = CreateObject("Scripting.FileSystemObject")
strTest = WScript.Arguments(0)
Set fIn = fso.OpenTextFile(WScript.Arguments(1))
Set fOut = fso.CreateTextFile(WScript.Arguments(2))

Do Until fIn.AtEndOfStream
strLine = fIn.ReadLine
If Left(strLine, Len(strTest)) = strTest Then
'this line starts a group so store its value
strTag = Mid(strLine, Len(strTest)+1)
Else
fOut.Write strTag & DELIM & StrLine & Chr(13) & Chr(10)
End If
Loop

fIn.Close
fOut.Close
'---------------end of code ------------

Have a linked delimited text file with
several thousand rows. Text
file is created from IBM main frame.
There is one row ("header row")
where the first three characters are ".//"
uniquely identifying all the records
that follow, until the next header row.

Going to query the header row only,
'cause in the 13th position of that row,
and for the next eight positions, contain
the name of the program (file is a dump
of all JCL programs for a person's account
- rows 1 - 50 could be program one,
rows 51 -72 will be the next program,
rows 73 - 90 the next, etc,
but the first line of each program
starts with ".//".)

So, I want to query the linked text file
using mid([Field1],13,8) on rows that
start with ".//" , and return every
consecutive row following, until the next
"header row" - again, starting with
".//" - which means I have all the records
associated with the header row I queried.

Do I have to use some kind of
db.recordset
rs.movenext
type thing for this?