Jim:
More here than most will care to read, but Jim - and thank you! - you
set of a light bulb on this business of locating the last field in a
filename (more below. ) For processing a consistently delimited fileset
(the first fileset of two), I had been attempting a one-stop,
post-parsing approach within an [ UPDATE ... SELECT #c ... ] statement
(still working on it. ) Switching gears. Filenames in FileSet#1 are
structured with the '(' delimiter:
Field1(Field2(Field3(Field4 ... (Field13
... which makes it straightforward to capture the contents of the
randomly-placed last field, say, Field10, containing the extension,
prior to parsing using:
SET VAR vFieldCount INT = NULL
SET VAR vDocExt TEXT =NULL
SET VAR vFieldCount = ((SLOCI(SourceFilename,'(',0))+1) [1]
SET VAR vDocExt = (SSUBCD(SourceFileName,.vFieldCount,'('))
... wherein vDocExt can be copied without issue to Column DocExt.
[1] The first field is not preceded by the delimiter.
Your approach looks like it may go a long way towards solving the
thornier problem presented by filenames within the hot mess of the
second fileset, which (a) contain substructures which could and should
have been ordered in a logical field structure; (b) were not, and (c)
contain one or more instances of every delimiter candidate known to
mankind ... except, thankfully, for the '(' delimiter. I'm thinking of
a grueling iterative process wherein subsections bracketed by various
characters are isolated by application of a sequence of
character/delimiter substitutions, until the potential for order
appears. Will let you know privately how that goes.
I'm still hoping to solve the issue of SELECT #c recognition, tho, 'coz
it has torn off my ego, and is beating me with it.
Jim, I hope you, your family, your colleagues and all your charges are,
and remain, healthy and uplifted. Appreciate your help.
Best Regards, Bruce
Bruce A. Chitiea
SafeSectors, Inc.
112 Harvard Ave #272
Claremont CA 91711-4716
[email protected]
+011 (909) 238-9012 c
------ Original Message ------
From: "'James Bentley' via RBASE-L" <[email protected]>
To: [email protected]
Sent: 5/17/2020 4:09:50 PM
Subject: Re: [RBASE-L] - Using #c within an UPDATE ... SELECT
Bruce,
Instead of trying to locate the file extension from the parsed columns
consider populting the DocExt column by using the original full file
name in populaing the column. By definition the file extension appears
after the last "." in the full file name. Therefor the object is to
find the position of the last "." via:
To find the LAST occurrence in the value of a string, use the "-1"
parameter in set var vLast = (SLOCP(.v1,'.',-1)) . Here is the full
function to get the file extension
set var vext = (SGET(.vFN,(SLEN(.vFN)) - (SLOCP(.vFN,'.',-1)),
(SLOCP(.vFN,'.',-1)) + 1 ))
to test I used set var vFN = 'file.name.ext'
You can build it into the parse process when loading the table or if
the table includes the full file name make DocExt a computed column
replacing .vFN with the
column name.
Jim Bentley, American Celiac Society
On Friday, May 15, 2020, 07:11:00 PM CDT, Bruce Chitiea
<[email protected]> wrote:
All:
Courier New font
BACKGROUND
For a file naming and re-naming project, several thousands of
custom-delimited document filenames are to be parsed into fields within
table ParsedFileNames. The field count for each record varies by class
and filetype, so that the filename extension scatters over a range of
fields.
The goal is to copy the filename extension from whichever field it
falls within, to common column DocExt, ideally as the table is being
populated.
To this end, during parsing, SLOCI generates a field count to be stored
in a FieldCount column for each record. Since the filename extension is
the last field in a parsed filename, the extension is found within
Column# = FieldCount.
ISSUE
By itself ...
SELECT ('#'+(CTXT(FieldCount))) FROM ParsedFilenames
... produces:
#13
#11
.
#10
... as intended. So, I'd thought this would do the trick:
UPDATE ParsedFileNames +
SET DocExt = +
SELECT ('#'+(CTXT(FieldCount))) +
FROM ParsedFileNames +
WHERE DocExt IS NULL
... which produces the error:
"Illegal table name - ('#'+(CTXT(FieldCount))) (2037)"
Several attempts to turn the offending string into a variable, for use
as an ampersand variable, have also failed. Still working at it, but
clearly not getting it.
How might the column number be specified within the SELECT statement,
to avoid resort to a WHERE loop or CURSOR?
Many thanks, Bruce
Bruce A. Chitiea
SafeSectors, Inc.
112 Harvard Ave #272
Claremont CA 91711-4716
[email protected]
+011 (909) 238-9012 c
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/emdaf59928-37f7-4860-a39e-91a4b7d4e199%40pathfinder
<https://groups.google.com/d/msgid/rbase-l/emdaf59928-37f7-4860-a39e-91a4b7d4e199%40pathfinder?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/1985439270.620569.1589756990489%40mail.yahoo.com
<https://groups.google.com/d/msgid/rbase-l/1985439270.620569.1589756990489%40mail.yahoo.com?utm_medium=email&utm_source=footer>.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/emeafe50f5-3e4d-497e-9219-209df3d6a846%40pathfinder.
