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

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
SIMMS7400
Posts: 487
Joined: 07 Jan 2016 07:47

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

#1 Post by SIMMS7400 » 17 Dec 2020 02:18

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
Expert
Posts: 2414
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

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

#2 Post by dbenham » 17 Dec 2020 08:55

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
Posts: 487
Joined: 07 Jan 2016 07:47

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

#3 Post by SIMMS7400 » 17 Dec 2020 09:47

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
Expert
Posts: 2414
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

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

#4 Post by dbenham » 17 Dec 2020 10:42

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

SIMMS7400
Posts: 487
Joined: 07 Jan 2016 07:47

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

#5 Post by SIMMS7400 » 18 Dec 2020 05:14

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
Posts: 487
Joined: 07 Jan 2016 07:47

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

#6 Post by SIMMS7400 » 21 Dec 2020 15:02

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
Expert
Posts: 2414
Joined: 12 Feb 2011 21:02
Location: United States (east coast)

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

#7 Post by dbenham » 22 Dec 2020 07:18

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
Posts: 487
Joined: 07 Jan 2016 07:47

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

#8 Post by SIMMS7400 » 22 Dec 2020 14:30

HI Dave -

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

Post Reply