Spreadsheet and XML Global Substitution Accountability Tutorial

Today we address some accountability improvements to the XML Global Substitution web application we last left off with at Spreadsheet and XML Global Substitution Genericization Tutorial. You may recall, then, that we had a two step procedure of …

From filespec create CSV mapping (global_substitution.csv) for second part via section tags containing role=”paragraph” and old_label=”[OldLabel]” and label=”[NewLabel]” all before section tag end
From input index file and CSV above create Korn Shell (ksh) file that runs … ksh -x global_substitution.ksh … to update for new index file (but backup yourself first)

… where, if that sounds a bit new to you, that is because we bit the bullet, and renamed the web application with the more apt name “global_substitution” wherever, earlier on, we had the name “tr_mapping”. And this is a fairly big topic regarding “accountability”. To us, “accountability” is mainly about being able to revisit some software, work out what it does, and be confident to use it when and if and how it is applicable to an issue you have and/or a job you want done.

“Accountability” with respect to any application goes hand in hand with listening to the client as to how their life can be made easier because of your application’s workings. With our XML “global substitution” work we’ve had quite a few user driven changes of recent times, and we decided to assemble them into a blog posting today. First off, what files are involved as inputs, where bold parts are new since that last incarnation …

“Filespec files”: File specification for input XML data files … whose data can be used to populate CSV and KSH files
“Index file”: File name for input (and output) index XML file
“CSV file”: This file, previously, was mainly thought of as an intermediate or output file, but today we focus more on it being an input file … to quote the code today …

Allow for jobs where supplied with index file and intermediate CSV and a “halfway prefix” to search for in CSV file, but no KSH file yet

And what are the intermediate and/or output files?

“CSV file”: as mentioned above
“KSH file”: Our Korn Shell scripting “Index file” amender is created via “Filespec files” processing or, as of today, via reading the “CSV File” as an input file … and now we encourage its use more often to improve the reporting functionality talked about below
“HTML file”: COM_.htm report file using the talents of the HTML div element (remember HTML Textarea and Div Talents Primer Tutorial?)
“TXT file”: New “sanity check” extracting files of contents of optionally designated tag of interest
“Index file”: File name for input (and output) index XML file

Sound a bit familiar? Well, yes, remember with XML Subtraction and Addition Genericization Tutorial how we said …

Both above were challenging, and with the second we were glad we could call on …

HTML Textarea and Div Talents Primer Tutorial proved, at least to us, that HTML div elements had to be involved in some way shape or form … along with the web application user experience (UX) thoughts of …

HTML5 Details Summary Primer Tutorial taught us, recently, about a great new HTML5 reveal CSS styling idea

… and we also call on the HTML textarea element for improvements today.

Have been told that those “HTML file” and “TXT file” seal the deal as far as “feeling good” regarding the data, which is important, of course.

Another user driven improvement with the “Filespec files” processing (to create that “CSV file”) we add more data intelligence via …

optional “Filespec files” first label in the “CSV file” first column via new “New label regarding global substitutions start delimitation” [ uci=”] and “New label regarding global substitutions end delimitation” [“]
optional “Filespec files” title in the “CSV file” last column via new “After Find Title (CSV last field) tag prefix” [>] and ” After Find Title (CSV last field) tag suffix [</title>]

A couple of new options that you might expect as an HTML input type=checkbox element are, instead, today, handled by clicking a link for those decisions …

if “KSH file” exists ahead of a …

Allow for jobs where supplied with index file and intermediate CSV and a “halfway prefix” to search for in CSV file, but no KSH file yet

… type of execution, we allow the user to delete that “KSH file” ahead of the run
we used to demand the existence of both oldlabel and label attributes … but we allow for just label existant data as the default but if the user does not like this idea, they can go back to the original idea that both oldlabel and label have to exist in the XML data

All very “business logic” feeling, but there you are, businesses know the way they want the data to work for them.

Here are the downloadable PHP global_substitutionphp (changed thisway based on that previous tr_mapping.php incarnation). Maybe you can adapt it to something you are working at with XML data, and you can see us using it with today’s PDFslideshow presentation?

Previous relevant Spreadsheet and XML Global Substitution Genericization Tutorial is shown below.

Spreadsheet and XML Global Substitution Genericization Tutorial

It’s one thing to write a useful one off web application with quite a few hard codings, but what about an attempt to genericize it, and by so doing, oftentimes you are improving its documentation aspects, so that, if the code is revisited years later …

its generic qualities will be plain to all … and at the same time …
it will be far easier to imagine as far as inputs are concerned …
the user can (still) break the job up
the user has less to worry about as far as a backup of data goes
it does not feel like a one off any more
it is less likely to be ill used for an inapplicable application

… so that, all in all, we feel much more confident such code can last the test of time and usefulness into the future than yesterday’s (albeit useful) one off feeling version of the code you can see at Spreadsheet and XML Global Substitution CSV Tutorial as shown below.

What’s the main driver of genericization, in our book (but not our pamphlettes) for small jobs?

turn all hardcodings you can into parameterizable variables, and today that is via PHP $_GET[] variables off the web browser address bar
allow the user to change these, as the hardcodings just become defaults, and are presented in a submittable HTML form whose action is to recall the same piece of PHP software (code)

… simple, huh?!

Again, you can see the various aspects of this, in play, with today’s tutorial picture, and though it is not much use to run the PHP code live, its style is far more generic now, so we want to share tr_mappingphp (changed thisway) with you for your perusal … just in case (it is of use for you).

Previous relevant Spreadsheet and XML Global Substitution CSV Tutorial is shown below.

Spreadsheet and XML Global Substitution CSV Tutorial

Programmatically, we came in half way with the programming when we presented Spreadsheet and XML Global Substitution Primer Tutorial as shown below. The programming, then, had two inputs, namely …

input spreadsheet’s CSV file manually created
index XML file

… but that CSV file can be programmatically created rather than manually created. And while we’re at programmatically creating the CSV we could also programmatically create the Korn Shell (ksh) easier there too, with the same program, rather than using TextWrangler’s Grep (RegEx) talents … not that we’re ungrateful or anything … but it is good to mix things up to improve procedures sometimes.

And what programming language can we use, and what environment for that programming code? We think …

coding wise, we’ll use PHP (starring PHP’s glob() method) … and the environment for that will be that …
we’ll use a (local Apache/PHP/MySql web server) MAMP subfolder (ie. how desktop application “can meet” web application) off its Document Root (/Applications/MAMP/htdocs/) … /Applications/MAMP/htdocs/tr_mapping/ … to store the XML data files (no CSV needed as input this way, as it will be programmatically created in part 1 of 2 parts to the whole job) … which becomes accessible in two ways …
1. //localhost:8888/tr_mapping/tr_mapping.php#in_a_web_browser
2. at Mac OS X Terminal desktop application command line via …
  cd /Applications/MAMP/htdocs/tr_mapping
  ksh -x tr_mapping.ksh

Again, email is the conduit for both sides of …

input in
output out

… to complete proceedings. You can see the various aspects of this, in play, with today’s tutorial picture, and though it is not much use to run the PHP code live, its style is leaning towards the generic side enough for us to want to share tr_mappingphp with you for your perusal … just in case (it is of use for you).

Previous relevant Spreadsheet and XML Global Substitution Primer Tutorial is shown below.

Spreadsheet and XML Global Substitution Primer Tutorial

Yesterday when we were discussing Worldbank API World Country Reporting Regex Tutorial we mentioned …

… and we use it (ie. Regex) with serverside PHP today, under the auspices of the preg_match function, though we most often use RegEx thinking with the Javascript replace function, as the way to make substitutions for more than one occurrence, (the one occurrence design being) a default “curiosity” (but can be useful too) about Javascript’s version of substitution. You may know this RegEx usage of the Javascript replace function as “global substitution”.

… and that term “global substitution”. Many editing jobs, especially text file based ones, require or benefit from “global substitution” carefully applied, that is. It is common to see an editor who shies away from “global substitution” methods, and in many cases that is wise, but “global substitution” gets good results when you …

substitute things you know exist in the precise form you intend to search for, and only there, where you want to replace … to
replacements should not feed back into the substitution list … doh … or you will end up with a confused unintended result

In real life, it is often the case that the conditions above are easy to obey, because you are mapping an old numbering and/or naming system to a completely new and dissimilar numbering and/or naming system. That’s the case in a little job we drilled down into, to show you what we did, that involved RegEx thoughts, to solve a problem.

So, with our job we had …

Aim: Change some XML in one file to have the text in one column of a Spreadsheet be mapped to the contents of another column of that same Spreadsheet
Inputs: Excel Spreadsheet with those two columns as mentioned above and the one input XML file
From the User: Asked for the user to send the Excel Spreadsheet … Saved As Comma Separated Values (CSV) in MS-DOS format and the one input XML file as two attached files in an Email
Processing:
1. Opened Email with Gmail web application in Safari web browser desktop application, on a MacBook Pro laptop
2. Downloaded the two Attachments and copied over to where we like to work … the home of MAMP local Apache/PHP/MySql web server … on a Mac OS X system is /Applications/MAMP/htdocs (which we’ll access later with the Mac OS X Terminal desktop application later via “cd /Applications/MAMP/htdocs”)
3. Opened our favourite Text Editor desktop application, called TextWrangler, whose “Find and Replace” “Grep” suboption will be a feature of today’s solution
4. File -> Open the Spreadsheet CSV file
5. Search -> Find… … Matching Mode: Grep … Find: ^ Replace: # … Replace All … remember our “RegEx” “cheat sheet” discussion (lots of which is relevant to TextWrangler Matching Mode: Grep as well) at that aforesaid mentioned tutorial …
  - ^ can mean “start of”
  - $ can mean “end of”
  - . can sometimes mean “one existant character wildcard” … or sometimes it is % or ? for this in other “systems”
  - * can often mean “zero or more of preceding character wildcard”
  - [] and () bracketing rules are pretty crucial for the more esoteric usages … also study | usage
  ? … well, we want to start out mapping all lines to non-acting Korn Shell command lines
6. Typed as the new top line #!/bin/ksh … just for completeness sake … is optional step
7. Search -> Find… … No Matching Mode … Find: #,[ Replace: cat COMM.MIL~INDEX.xml | sed ‘/\[ … Replace All
8. Search -> Find… … Matching Mode: Grep … Find: ]$ Replace: \\]/g’ > x.xxx ; cat x.xxx > COMM.MIL~INDEX.xml ; rm -f x.xxx … Replace All
9. File -> Save As… fix_csv.ksh (to /Applications/MAMP/htdocs directory)
Opened Terminal desktop application that has a default Bash environment (a lot like Linux, but is (giving you access to) a Mac OS X BSD operating system, really)
Typed in: cd /Applications/MAMP/htdocs # to get to data
Typed in: cp COMM.MIL~INDEX.xml COMM.MIL~INDEX_original.xml # to backup data ahead of processing, as well as to compare file sizes with later, as a sanity check
Typed in: ksh -x fix_csv.ksh # access Korn Shell interpreter and run the TextWrangler created Korn Shell Script (and the -x switch tells the interpreter to be verbose with output reporting)
Typed in: ls -l COMM.MIL~INDEX*.xml # first sanity check verified files different, and not disastrously so … good first sign
Typed in: fgrep -c ‘[S1.12.4.20]’ COMM.MIL~INDEX*.xml ; fgrep -c ‘[CCR.28E.20]’ COMM.MIL~INDEX*.xml # second sanity check to prove old/new parts of first/last relevant Spreadsheet CSV file records were correctly mapped … and they were … so
Opened Email with Gmail web application in Safari web browser desktop application (and used “Forward” option, attaching that new XML file), on a MacBook Pro laptop … so that …
Output: One XML file with the global substitutions expressed in the Excel Spreadsheet performed, returned to User via Email “Forward” option, attaching that new XML file

We hope you can see the good use you can make with Email and a good Text Editor and Linux type shell scripting, influenced by RegEx pattern matching regarding …

grep
sed

Which leaves us with today’s PDF slideshow of snapshots of making this job work, here.

If this was interesting you may be interested in this too.

Your Numbers Game
Get clue	Your answer	Your Score
Clue?		Score 0/0

S	M	T	W	T	F	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Spreadsheet and XML Global Substitution Accountability Tutorial

☞

One Response to Spreadsheet and XML Global Substitution Accountability Tutorial

Leave a Reply Cancel reply

Numbers Guessing Game

Your Numbers Game

Recent Posts

Categories

Meta

Your Background Image

OnTopList

Recent Comments

Spreadsheet and XML Global Substitution Accountability Tutorial ☞