Re: Parsing / ' " characters

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Sat, 26 Nov 2005 09:14:34 -0500, "J Shrimps, Jr."
<JumboShrimps@xxxxxxxxxxx> wrote:

>
>The mainframe dump exports all the code in the exact order of
>execution, so if my statement seeks the first unqiue record
>that identifies the program, the rest of the records (until
>the next unqiue identify (./) for the next program) are in the
>correct order.

Remember that you cannot rely on records in a table in a relational
database to be returned in the same order in which they were appended.
This could be disastrous if each record is a statement in a program<g>.

For safe working with relational data you have to treat a table as an
unordered "bucket" of records; the only way to be certain that records
will always be returned in the order you want is to include a field or
fields, on which they can be sorted into that order.

I know nothing about the structure of your tables and nothing about JCL.
At a guess you need one table for Programs and another for lines of
code, with a 1:M relationship

Programs
ProgramID*
ProgramName
Other fields

LinesOfCode
ProgramID* (foreign key into Programs)
SeqNumber* (a sequential number or line number or some such
that together with the ProgramID allows you to keep thel
lines sorted into their original order
Code (the actual line of code)

Or maybe you need a single table

Code
ProgramName* (text)
Code (memo field storing all the lines of code for that program)

.. But you presumably also have other fields and tables to track
versions, revisions, persons responsible for various programs, and so
on.


>I used a replace function to replace ' with !, - character
>not found in any of the JCL programs - so all records
>can be appended. Once the temp table is built, the
>same function does the inverse.
>Set rst = db.OpenRecordset("SELECT tmpcode.[code] _
>& "FROM tmpcode WHERE (((tmpCode.code) ='" & strProgram & "'));)"

This creates a recordset containing any records where the field [code]
matches whatever's in strProgram, so I don't see why you're using
FindNext; that's for finding the first record that matches your
criteria.

>Rst.findnext seeks (and returns) the first record
>but Rst.Movenext
>starts with the very first record at the top of the
>linked list

What do you mean by "the linked list"?

> - every time I create the temp table,
>the exact same 24 records are added
>So it appears I can't just:
>Rst.Movenext,
>strCode = Rst!code
>strSQL =(INSERT INTO tmpExport ( & strCode & ))
>docmd.runSQL
>'cause I'm getting the first 24 records every time.
>Once I've identified the unique record that begins
>the set of records I'm looking for - how do I
>Rst.Movenext (or however) to the next record
>(and keep moving) directly below?
>Once I have solved that, I
>have the rest of the code to append the
>contents of each field -one at a time-
>into the export table. When the next
>record that begins with the string "./"
>- JCL inserts that string at the start of every program,
>the process stops. Some programs are just 20 lines
>some over a thousand, but the first two characters
>of every program begin with "./"

I have to say I'm thoroughly confused. Please try to explain more
simply, remembering that I can't see over your shoulder. Maybe it would
be best to start a new thread: that will encourage other people to join
in.



>
>
>"John Nurick" <j.mapSoN.nurick@xxxxxxxxxxxxxx> wrote in message
>news:qj6co1pst2ehqjdgkr210j25h09evvqqeu@xxxxxxxxxx
>> The problem is in the SQL statement you are building. If you want to
>> create a new record in table TTT containing the string
>> //XXXXXXX ZZZ 13DSAS,'B GATES BSP04D',XYZCLASS=T,CLASS=T))
>> in the field FFF, you need to assemble and execute this statement
>> (ignoring the line breaks):
>>
>> INSERT INTO TTT (FFF) VALUES ('//XXXXXXX ZZZ 13DSAS,''B GATES
>> BSP04D'',XYZCLASS=T,CLASS=T))');
>>
>> Note how (as described in my last post) I have had to double the
>> apostrophes in
>> 'B GATES BSP04D'
>> .
>>
>> By the way, how are you ensuring that the order of the lines of code is
>> maintained through the importing, exporting and updating processes?
>>
>> On Thu, 24 Nov 2005 11:06:05 -0500, "J Shrimps, Jr."
>> <JumboShrimps@xxxxxxxxxxx> wrote:
>>
>> >
>> >Code is lnked as fixed width.
>> >The problem appears to be the identifying the
>> >record with the characters in question
>> >I can append ALL the records from the
>> >linked text list through docmd.sql,
>> >and I can append the
>> >subsequent records once the unique
>> >identifying record is found, BUT
>> >I just can't append the identifying record -
>> >generating a runtime error '3075'
>> >Syntax error in expression (missing operator)
>> >in query expression '(((tmpcode.code =
>> >'//XXXXXXX ZZZ 13DSAS,'B GATES BSP04D',XYZCLASS=T,CLASS=T))'
>> >this is the one unique record I need to start the append process.
>> >All subsequent records can be appended 'cause I guess
>> >the rest of the records don't have commas and whatever.
>> >Linked text list is a dump of all mainframe code
>> >into one big doggy pile. Building my own
>> >Visual Source Safe (kind of) through Access.
>> >Programmers can select program name from combo box,
>> >and a text file is exported of just
>> >the code associated with that program - in exact order,
>> >of course, otherwise the code won't compile.
>> >
>> >here is code
>> >dim strprogram as string 'find the name of the program
>> > 'based on combo box
>> >dim strCode as string
>> >strProgram = me.cmbProgram 'combo box of program names
>> >
>> >Dim db As Database, tdf As TableDef
>> >Set db = CurrentDb()
>> > Set rst = db.OpenRecordset("SELECT tmpcode.[code] _
>> >& "FROM tmpcode WHERE (((tmpCode.code) ='" & strProgram & "'));)"
>> > rst.findfirst 'find that program header (unique row)
>> > Do While Not rst.EOF
>> >strCode = rst!code 'Set strCode to contents of linked text field
>"CODE"
>> > 'only one field in linked text file
>> > strSQL = "(INSERT INTO tmpExport ( & strCode & ))"
>> >docmd.runsql (strsql) 'append into export table that line of code
>> > rst.MoveNext 'keep going
>> > Loop
>> > rst.Close
>> >
>> >BRAINSTORM:
>> >Use a dLookup function to identify the first record,
>> >map strCode to the dLookup, and append strCode
>> >to export table.
>> >Gonna try that.
>> >
>> >
>> >"John Nurick" <j.mapSoN.nurick@xxxxxxxxxxxxxx> wrote in message
>> >news:a6mao19nug0lql50mf7q8tje0k31nb4c3g@xxxxxxxxxx
>> >>
>> >>
>> >> On Wed, 23 Nov 2005 17:52:09 -0500, "J Shrimps, Jr."
>> >> <JumboShrimps@xxxxxxxxxxx> wrote:
>> >>
>> >> >
>> >> >I believe the comma's in the field of the linked file that
>> >> >are preventing that paticular record from being appended.
>> >> >Is there any way to load the contents of a specific record,
>> >> >even with the , and // and '
>> >> >into memory such that I can copy just that record
>> >> >into a temporay table?
>> >>
>> >> When you link the file, the wizard gives you the opportunity to specify
>> >> the "delimiter" (the character used to separate fields) and text
>> >> qualifier (the character used to enclose the data in text fields). By
>> >> default, these are , and " respectively.
>> >>
>> >> To import the whole line from the text file into a single field, set
>the
>> >> qualifier to {none}, and for a delimiter, specify a character that does
>> >> not appear anywhere in the file. Often the pipe character "|" is a good
>> >> one to use - but this depends entirely on the contents of your file.
>> >>
>> >> To get the contents of a specific record in the text file, you have
>> >> basically two choices. One is to link the file as above and then use a
>> >> query that selects that records by its contents.
>> >>
>> >> The other is to write code that reads the text file and identifies the
>> >> record in question, then appends it to your temporary table. This
>> >> usually the only sensible approach if the record is identified by its
>> >> position in the file (e.g. the first record) rather than by its
>> >> contents. Here's some air code that shows the general idea:
>> >>
>> >> Dim strFileName As String
>> >> Dim strLine As String
>> >> Dim strSQL As String
>> >> Dim lngFN as Long
>> >>
>> >> 'Open file
>> >> strFileName = "D:\Folder\File.txt"
>> >> lngFN = FreeFile()
>> >> Open strFileName For Input As #lngFN
>> >>
>> >> 'Read first line and close file
>> >> Line Input #lngFN, strLine
>> >> Close #lnfFN
>> >>
>> >> 'If there are any ' in the line, we need to double them. Otherwise
>> >> 'they will be confused with the ' ' used as quote marks in the SQL
>> >> 'statement
>> >> strLine = Replace(strLine, "'", "''")
>> >>
>> >> 'Append to table
>> >> strSQL = "INSERT INTO NameOfTable ( NameOfField ) VALUES ('" _
>> >> & strLine & "');"
>> >> CurrentDB.Execute strSQL, dbFailOnError
>> >>
>> >>
>> >> >That one record uniquely identifies the records to follow,
>> >> >so I really need the entire contents of that record.
>> >> >
>> >> >"John Nurick" <j.mapSoN.nurick@xxxxxxxxxxxxxx> wrote in message
>> >> >news:gj68o1t8d7pdbc15491kupj2k9d81mfpna@xxxxxxxxxx
>> >> >> This can get complicated, but let's start with the simple questions.
>> >> >>
>> >> >> 1) What do you mean by "the insert statement doesn't execute"? I
>> >presume
>> >> >> that's what's actually happening is that you have a statement like
>> >> >> CurrentDB.Execute strSQL
>> >> >> which is being executed but that some or all of the records are not
>> >> >> being appended.
>> >> >>
>> >> >> 2) Is the problem in the linking or in the appending? Open the
>linked
>> >> >> table in data*** view and inspect some of the records that you
>know
>> >> >> from the text file should contain quote marks or the other
>characters
>> >> >> you mention. Do they appear correctly in the linked table? If not,
>the
>> >> >> problem is either in the text file or in the way you linked the
>table.
>> >> >>
>> >> >> 3) Open the text file in Notepad or another text editor. As I
>> >understand
>> >> >> it from what you've said, you're treating each line as a single
>field.
>> >> >> Does every line begin and end with a quote (") character? (i.e.
>each
>> >> >> field is "qualified" with guote marks) This is what Access expects
>of a
>> >> >> linked text file unless you tell it otherwise.
>> >> >> If the lines are just plain text not "qualified" with quotes, you
>> >need
>> >> >> to specify this when you link the text file, either in the text
>import
>> >> >> wizard or with an import/export specification. Otherwise, quotes
>within
>> >> >> the data are likely to make the import/link routine go wrong.
>> >> >>
>> >> >>
>> >> >>
>> >> >> On Tue, 22 Nov 2005 21:46:45 -0500, "J Shrimps, Jr."
>> >> >> <JumboShrimps@xxxxxxxxxxx> wrote:
>> >> >>
>> >> >> >
>> >> >> >Have a lnked text file I need to
>> >> >> >extract data from - only one character field
>> >> >> >in file, but some of the records have
>> >> >> >// ' " characters, so when I try to
>> >> >> >insert them into a temporary table with
>> >> >> > strSQL = "Insert Into tmpExport ( Code )" _
>> >> >> >& "Select " & StrTblName & ".[CODE] from" & StrTblName & ";"
>> >> >> >However, when some of these funky characters are encountered
>> >> >> >in the field CODE, then the insert statement doesn't execute.
>> >> >> >I'm assuming Access is interpreting the // " ' characters as some
>kind
>> >of
>> >> >> >delimiter.
>> >> >> >How can I mask these characters so Access can append them
>> >> >> >(through code) to another table?
>> >> >> >
>> >> >> >
>> >> >>
>> >> >> --
>> >> >> John Nurick [Microsoft Access MVP]
>> >> >>
>> >> >> Please respond in the newgroup and not by email.
>> >> >>
>> >> >
>> >> >
>> >>
>> >> --
>> >> John Nurick [Microsoft Access MVP]
>> >>
>> >> Please respond in the newgroup and not by email.
>> >>
>> >
>> >
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
>
>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

.

Quantcast