JREPL Usage - read text file with commas and replace empty value

[SIMMS7400]

Hello Folks -

I use JREPL for quite a few routines and it works wonderfully! I'm having a little trouble with a new business requirement and was wondering if you could help?

I have a text file that is PIPE delimited however some of the values have commas which as you know, pose a problem. The text file is as follows:

Employee Name (Last Suffix, First MI)|Job|Salary/Hourly|Employment Type|Employment Status|Text Item|Last Hire|Scheduled Work Hours|Salary Grade|Org Level 2 Code|Org Level 2|Employee Number|Annual|Bonus Target|Car Allowance|Sign-On|Obela Allocation|Projected Start Date
Position 1, Open|Planner|Salaried|Open Role|Active|Full-time|2020-01-01 00:00:00|80|13|512551|Ops|03270|56700|0.1||||

What I need to do is skip the first row and scan the 'Project Start Date' column and replace any nulls/empty values with "ERROR". 'Projected Start Date' will always be the 18th column position which I'm having trouble with.

Thanks!

[dbenham]

I don't see how commas within a field value pose any problem or complication. That is only a complication if the field delimiter also happens to be a comma.

Skipping the first line is simple via the /EXC option.

According to your sample data, the 18th field also is the last field in the record - That is much simpler to look for.
I'll assume white space is also considered to be empty. I'm also assuming the 18th field never contains a | literal.
The | character is a regex meta-character, so a literal value must be escaped in the search string.

Code: Select all

jrepl "\|\s*$" "|ERROR" /exc 1 /f "yourFile" /o -

It is not much more difficult to find the 18th field, assuming none of your fields contain a | literal. (?:pattern) is a non capturing group, and (?=pattern) is a positive look-ahead group that is not saved as part of the match. The empty 18th field ends at either end-of-line or else the next |.

Code: Select all

jrepl "^((?:[^|]*\|){17})\s*(?=$|\|)" "$1ERROR" /exc 1 /f "yourFile" /o -

I haven't tested either of the above, but I am fairly confident I got them right. Certainly the intended logic is correct.

Dave Benham

[SIMMS7400]

Hi Dave -

Thank you so much!! Both are working like a charm!

One question, is there a way to use JREPL to pull out the rows with NULL/empty strings in the 18th position and spool them to a "temp" file first before we replace with ERROR?

Thank you!

[dbenham]

Sure. Simply add the /A option (only write altered lines) and change /O to your desired output file.

[SIMMS7400]

Dave -

One thing I noticed is the syntax to spool to a "temp" file when NULL/empty values are found is working. But it's also creating an empty file if no matches are found.

Here is my syntax:

Code: Select all

CALL "%UTILPATH%Batch\JREPL" "\|\s*$" "| " /A /exc 1 /f "%FDMEE_BIN%%DATAFILENAME%" /o "%FDMEE_BIN%%ERR_FILE%"

Is there a way to prevent that?
Thanks!

[SIMMS7400]

HI Dave -

Is there a way to prevent that empty file creation if no altered lines are detected? Or do I just need to have another line of code to delete the empty file?

Thanks!

[dbenham]

You will have to test for and delete the empty file after JREPL finishes. There are any number of ways. Here are a couple:

Code: Select all

findstr "^" "%FDMEE_BIN%%ERR_FILE%" >nul || del "%FDMEE_BIN%%ERR_FILE%"

for %%F in ("%FDMEE_BIN%%ERR_FILE%") do if %%~zF == 0 del "%FDMEE_BIN%%ERR_FILE%"

Dave Benham

[SIMMS7400]

HI Dave -

Thank you, just wanted to confirm there wasn't another switch I was missing. THanks again!!

[SIMMS7400]

HI Dave -

I'm hoping this would be an easy tweak but there is a new business need. Per the above JREPL solution you suggested, it's working like a charm!! I do need to add a piece of logic that ignore certain rows that contain a specific string, is that possible?

If the first column contains a string called (USE FOR TERM) I need to ignore it from the process. Is that possible?

Thank you!

[dbenham]

Of course it is possible.

You are already using the /EXC option to ignore the 1st line. It can accept a comma delimited list of exclusions. (Use JREPL /?/EXC to see the full documentation). You just need to add an additional exclusion for lines that begin with your string. Since your string includes parens, and those are regex meta-characters, it is simplest if you exclude a string literal with the b option to force match at the beginning of the line.

So just change the /EXC option to read

Code: Select all

/exc "1,'(USE FOR TERM)|'b"

That will exclude the 1st line, as well as any line that has (USE FOR TERM) as the first column.


Dave Benham

[SIMMS7400]

Hi Dave -

I just tried the suggestion but unfortunately doesn't seem to work. Here are the contents of my first column, could the comma be causing an issue?

Code: Select all

CALL "%UTILPATH%Batch\JREPL" "^((?:[^|]*\|){17})\s*(?=$|\|)" "$1Projected Start Date Missing" /A /exc "1,'(USE FOR TERM)|'b" /f "%FDMEE_BIN%%DATAFILENAME%" /o "%FDMEE_BIN%%ERR_FILE%"

Column 1:

Code: Select all

Position 9, Open (USE FOR TERM)

Note, columns are | delimited. Thank you!

[SIMMS7400]

Hi Dave -

I used the following and it seems to work:

Code: Select all

/exc "1,'USE FOR TERM'

Do you forsee any issues with this update?

[dbenham]

Probably just a typo, but you are missing the closing double quote.

Beyond that, you are excluding if "USE FOR TERM" appears anywhere on the line, not just the first column.

The code I gave you doesn't work because I mistakenly thought the entire first column must exactly match "(USE FOR TERM)" to be excluded.

If you are confident "USE FOR TERM" never appears anywhere besides the 1st column, then sure, your code is fine.

If you need to restrict it to the 1st column, then you could use an appropriate regular expression to exclude any line where "(USE FOR TERM)" is found anywhere within the first column . The regular expression could look like

Code: Select all

/^[^|]*\(USE FOR TERM\)/

except the caret must be escaped as "\c" due to CALL caret doubling. For example "[^|]" becomes "[^^|]", which matches any character other than caret or pipe. "[\c|]" correctly matches any character other than a pipe. The /EXC option does not need the /XSEQ option to recognize the non-standard "\c" escape sequence. But it should also be used in the first argument, in which case you do need /XSEQ.

So the final command could be

Code: Select all

CALL "%UTILPATH%Batch\JREPL" "\c((?:[\c|]*\|){17})\s*(?=$|\|)" "$1Projected Start Date Missing" /A /xseq /exc "1,/\c[\c|]*\(USE FOR TERM\)/" /f "%FDMEE_BIN%%DATAFILENAME%" /o "%FDMEE_BIN%%ERR_FILE%"

Dave Benham