Variable set with multiple values - check each existence?

[SIMMS7400]

Hi Folks -

I have a custom process that is used to distribute financial reports to a client.

A master control file is managed which identifies each person and the respective reports that should be mailed to them. The script then parses each line to extract the "email to" (token 1) and sets all of the attachments (token 2+) in a single variable.

Here is the control file:

Code: Select all

manager1@gmail.com,Epicor_Report_001,Epicor_Report_002,Epicor_Report_003,Epicor_Report_004,Epicor_Report_005,Epicor_Report_006,Epicor_Report_007,Epicor_Report_008
user1@gmail.com,Epicor_Report_001,,,,,,,
user2@gmail.com,Epicor_Report_002,,,,,,,
user3@gmail.com,Epicor_Report_003,,,,,,,
user4@gmail.com,Epicor_Report_004,,,,,,,
user5@gmail.com,Epicor_Report_005,,,,,,,

Each of the reports are PDFs so I add the path to the report and extension on just before the command line email utility. The issue is that, for instance, if Epicor_Report_003.pdf does not exist, the entire email will not be sent to "manager1@gmail.com".

My question is, is there an easy to way to check the existence of each report (that is set in the variable) and if it doesn't exist, remove it from being set in the variable?

Here are the relevant sections of my script pertaining to this question:

Code: Select all

>>%LOGFILE% (
ECHO ********************************************************
ECHO Execute Financial Report Burst Process                                          
ECHO ********************************************************
ECHO.
)

FOR /F "USEBACKQ Tokens=1* Delims=," %%A IN ( %CNTRL_FILE% ) DO (

	CALL :EMAIL "%%A" "%%B|"
)

Code: Select all

:EMAIL
SET "RPT=%~2"
SET /A "LCNT+=1"

SET "SW_SUB=Client : Finance Report Distribution"
SET "SW_BODY=%UTILPATH%SwithMail\Body_Text\FR_DIST_TXT.txt"
SET "SW_TO_EMAIL=%~1"
SET "SW_ATTACH=%RPT:,=|%"

:LOOP
IF "%SW_ATTACH:~-2%"=="||" (
   SET "SW_ATTACH=%SW_ATTACH:~0,-1%"
   GOTO LOOP
)
SET "SW_ATTACH=%SW_ATTACH:|=.pdf|%"

PUSHD "%RPT_PATH%"

CALL "%SW_MAIL_BIN%SwithMail.exe" /s /x "%SW_MAIL_BIN%SwithMailSettings.xml"^
	/SSL /to "%SW_TO_EMAIL%" /sub "%SW_SUB%" /btxt "%SW_BODY%"^
	/a "%SW_ATTACH:~0,-1%" && (
	
		ECHO Execute Control File ^- Line %LCNT% : Successful >>%LOGFILE%
		) || (
		ECHO Execute Control File ^- Line %LCNT% : Failed >>%LOGFILE%
		SET "ERR=T"
)
POPD

GOTO :EOF

Thank you!

[Squashman]

Before your call command, use another for command to iterate through the PDF files and check if they exist. If they exist, add them to an environmental variable and use that variable with your call.

You already had a whole thread started about this script. Why didn't you just resurrect that one instead of starting a new one.

[aGerman]

This should work

Code: Select all

FOR /F "USEBACKQ Tokens=1* Delims=," %%A IN ( %CNTRL_FILE% ) DO (
   CALL :EMAIL "%%A" "%%B"
)

Code: Select all

:EMAIL
SET "RPT=%~2"
SET /A "LCNT+=1"

SET "SW_SUB=Client : Finance Report Distribution"
SET "SW_BODY=%UTILPATH%SwithMail\Body_Text\FR_DIST_TXT.txt"
SET "SW_TO_EMAIL=%~1"
SET "SW_ATTACH="

PUSHD "%RPT_PATH%"

setlocal EnableDelayedExpansion
for %%i in (!RPT!) do if exist "%%i.pdf" set "SW_ATTACH=!SW_ATTACH!%%i.pdf|"
endlocal &set "SW_ATTACH=%SW_ATTACH:~,-1%"

CALL "%SW_MAIL_BIN%SwithMail.exe" /s /x "%SW_MAIL_BIN%SwithMailSettings.xml"^
   /SSL /to "%SW_TO_EMAIL%" /sub "%SW_SUB%" /btxt "%SW_BODY%"^
   /a "%SW_ATTACH%" && (
   
      ECHO Execute Control File ^- Line %LCNT% : Successful >>%LOGFILE%
      ) || (
      ECHO Execute Control File ^- Line %LCNT% : Failed >>%LOGFILE%
      SET "ERR=T"
)
POPD

GOTO :EOF

Steffen

[SIMMS7400]

Hi Steffen -

That's really slick - thank you!

It's working almost as expected - for some reason it's doubling the list of attachments in the variable SW_ATTACHMENT. For instance, it's repeating all the attachments twice:

DOSTIPS_SS.png (14.33 KiB) Viewed 9614 times

Note, the list of attachments are correct - so thank you!

Please see screen shot.

[aGerman]

There is no reason for this failure. Check what variable RPT contains.

Steffen

[SIMMS7400]

RPT contains the all of the reports in my first screen shot (line 1) - which is correct.

However, after it checks the existence of all reports (I made it so Epicor_Report_008 would be removed from the list as it doesn't exist) it sets that list twice in the variable SW_ATTACH.

Note in my last screen shot, SW_ATTACH contains the original list (inclusive of Epicor_Report_008) and then without.

Edit : 7:29PM

I got it resolved - thank you Steffen - I will proceed with testing now and let you know if I run into any issues. It looks great though!!!!!

[aGerman]

Still makes no sense. Run test.bat in the attached environment.

Steffen

[Squashman]

I am literally baffled that you didn't already know how to do this because in your original thread you were literally breaking up all the pdf file names into their own specific variables. So you should have known that you can use a FOR command to iterate through all of the pdf file names to check for their existence.

[SIMMS7400]

Squash -

Of course I knew how to do that - literally - but unfortunately I was having trouble setting all the pdfs into 1 variable. During my testing it kept getting overwritten with that last value I set it to within my new for loop.

But I appreciate the comments, they have been very helpful! Have a beer, loosen up, the Giants won

@Steffen -

Thank you again for your help as usual - this process is working great! I've been able to fully test the solution.

Happy Sunday!

[SIMMS7400]

This should work

Code: Select all

FOR /F "USEBACKQ Tokens=1* Delims=," %%A IN ( %CNTRL_FILE% ) DO (
   CALL :EMAIL "%%A" "%%B"
)

Code: Select all

:EMAIL
SET "RPT=%~2"
SET /A "LCNT+=1"

SET "SW_SUB=Client : Finance Report Distribution"
SET "SW_BODY=%UTILPATH%SwithMail\Body_Text\FR_DIST_TXT.txt"
SET "SW_TO_EMAIL=%~1"
SET "SW_ATTACH="

PUSHD "%RPT_PATH%"

setlocal EnableDelayedExpansion
for %%i in (!RPT!) do if exist "%%i.pdf" set "SW_ATTACH=!SW_ATTACH!%%i.pdf|"
endlocal &set "SW_ATTACH=%SW_ATTACH:~,-1%"

CALL "%SW_MAIL_BIN%SwithMail.exe" /s /x "%SW_MAIL_BIN%SwithMailSettings.xml"^
   /SSL /to "%SW_TO_EMAIL%" /sub "%SW_SUB%" /btxt "%SW_BODY%"^
   /a "%SW_ATTACH%" && (
   
      ECHO Execute Control File ^- Line %LCNT% : Successful >>%LOGFILE%
      ) || (
      ECHO Execute Control File ^- Line %LCNT% : Failed >>%LOGFILE%
      SET "ERR=T"
)
POPD

GOTO :EOF

Steffen

Hi Steffen -

Testing went great again today - so thank you!

I was hoping you could help me with one additional requirement. There are times when the reports will have two recipients - therefore I need to be able to extract them and keep them separated by a comma.

Code: Select all

"manager1@client.com,manager2@client.com",Epicor_Report_001,Epicor_Report_002,Epicor_Report_003,Epicor_Report_004,Epicor_Report_005,Epicor_Report_006,Epicor_Report_007,Epicor_Report_008,Epicor_Report_009,Epicor_Report_010
emp1@client.com,Epicor_Report_001,,,,,,,,,
emp2@client.com,Epicor_Report_002,,,,,,,,,
emp3@client.com,Epicor_Report_003,,,,,,,,,
emp4@client.com,Epicor_Report_004,,,,,,,,,
emp5@client.com,Epicor_Report_005,,,,,,,,,

I've tried to adjust my tokens, but on the lines where there is only 1 recipient, it's pulling in the first report as a recipient too.

You'll also notice if the csv is opened in excel and two recipients are added, it quotes them. I also need to keep recipients to 1 column, I can't break them up into (2).

Can you help me adjust?

Thank you!

[Squashman]

You can't create the file so that it has a different delimiter for multiple emails?

[SIMMS7400]

Hi There -

I've been able to build this and it's working with success.

Would this be approach you'd take?

Code: Select all

SETLOCAL ENABLEDELAYEDEXPANSION
FOR /F "USEBACKQ Tokens=1-2* Delims=," %%A IN ( %CNTRL_FILE% ) DO (
SET "SKIP="

	SET "R1=%%A"
	ECHO %%B |  FINDSTR /C:"@" >nul 2>&1 && ( SET "R2=%%B"& SET "P1=!R1:"=!,!R2:"=!"& SET "SKIP=T" )

	IF DEFINED SKIP CALL :EMAIL "!P1!" "%%C"
	IF NOT DEFINED SKIP CALL :EMAIL "!R1:"=!" "%%B"
)

[aGerman]

There are several possibilities. Values that contain a comma are surroundet in quotation marks since the comma is the csv separator. Thus, the easiest way is to use the quotation mark as delimiter first and then check what you get. See the attached test environment.

Steffen

[SIMMS7400]

Hi Steffen -

Thank you so much! It's working as expected!

However, a new report name got introduced with special chracters.

I needed to put quotes arounf "!RPT!" and then remove them from the %%~i variables, however the single reports are now having an issue.

The new report name is:

Code: Select all

Restaurant PL Burst Report-[BJRD_Stores].[EN65527]

I placed it in your test.zip env and running into the same issue.

Here is my :EMAIL function.

Code: Select all

:EMAIL
SET "SW_SUB=Client : Finance Report Distribution"
SET "SW_BODY=%UTILPATH%SwithMail\Body_Text\FR_DIST_TXT.txt"

SET "SW_TO_EMAIL=%~1"
SET "RPT=%~2"
SET "SW_ATTACH="

SET /A "LCNT+=1"

PUSHD "%RPT_PATH%"

	SETLOCAL ENABLEDELAYEDEXPANSION
	FOR %%i IN ( "!RPT!" ) DO (
	SET "SKIP="
		IF EXIST "%%~i.pdf" (
			SET "SW_ATTACH=!SW_ATTACH!%%~i.pdf|"
		) ELSE (
			IF "%%~i"=="|" SET "SKIP=T"
			IF NOT DEFINED SKIP (
				SET "TEMP=%%~i"
				ECHO Control File ^- Line %LCNT%^|%~1 : Does not exist - "!TEMP:|=!.pdf" >>%LOGFILE%
				SET "ERR=T"
			)
		)
	)	
	ENDLOCAL &SET "SW_ATTACH=%SW_ATTACH:~,-1%"

	echo CALL "%SW_MAIL_BIN%SwithMail.exe" /s /x "%SW_MAIL_BIN%SwithMailSettings.xml"^
		/SSL /to "%SW_TO_EMAIL%" /sub "%SW_SUB%" /btxt "%SW_BODY%" /a "%SW_ATTACH%" && (
   
			ECHO Control File ^- Line %LCNT%^|%~1 : Successful >>%LOGFILE%
			) || (
			ECHO Control File ^- Line %LCNT%^|%~1 : Failed >>%LOGFILE%
			SET "ERR=T"
		)
POPD
pause
GOTO :EOF

THe issue is the special characters in the report name - and when I quote "!RPT!" I'm unable to grab the individual reports obviousy.

What's the best way to account for this?

Thanks!

[Squashman]

Hi Steffen -

Thank you so much! It's working as expected!

However, a new report name got introduced with special chracters.

I needed to put quotes arounf "!RPT!" and then remove them from the %%~i variables, however the single reports are now having an issue.

The new report name is:

Code: Select all

Restaurant PL Burst Report-[BJRD_Stores].[EN65527]

No batch-file special characters in that file name. What software generates the email and file list for you? Why is it putting quotes around it?