Solved Reading text file line by line and do some operations

June 13, 2017 at 14:28:55
Specs: Windows 7
What I am trying to do here is to parse every row in filename.txt file and then run 2nd FOR loop to output it into another text file.

Filename.txt has multiple rows which looks something like this:

abc@gmail.com workbookname ViewName
xyz@gmail.com workbookname ViewName
pqw@gmail.com workbookname ViewName
and so on.

I want to use this file in a tabcmd export command where I will exporting the filename using workbookname.

tabcmd get "/views/workbookname/viewname.csv" -f "C:\file.txt"

Then I want to remove few columns and clean the data that I get from tabcmd export.

Finally I want to email this cleaned file to the email address from the Filename.txt.

So now I am able to do this only for the last row in the file while others are being ignored.

So the email part should look like:

sendemail -to %email% -from -attach %file%

message edited by anksravs


See More: Reading text file line by line and do some operations

Reply ↓  Report •

#1
June 13, 2017 at 15:22:44
Question: Why are you trying to break up reading the file and operating on the file into two separate FOR loops?
FOR /F "tokens=1,2 delims=|" %%a IN (C:\filename.txt) DO (
REM Some operations here
)

How To Ask Questions The Smart Way


Reply ↓  Report •

#2
June 13, 2017 at 15:31:21
Hey I have actually updated my question, if you dont mind can you please look into it?

Reply ↓  Report •

#3
June 13, 2017 at 16:16:08
✔ Best Answer
So basically something like this?
FOR /F "tokens=1-3" %%a IN (C:\filename.txt) DO (
tabcmd get "/views/%%b/%%c.csv" -f "C:\file.txt"
REM Your desired cleanup here
sendemail -to %%a -from -attach "C:\file.txt"
)

How To Ask Questions The Smart Way


Reply ↓  Report •

Related Solutions

#4
June 13, 2017 at 16:22:29
Hey, thanks a lot for the answer.

Reply ↓  Report •

#5
June 13, 2017 at 16:26:24
One more thing how can i check if I have reached the last line in the text file, I also want to mention that if last line=="" then goto :End

Reply ↓  Report •

#6
June 13, 2017 at 16:39:25
You'll know you're out of the file when you're out of the FOR loop. As for blank lines, FOR skips them, and it does not tell you.

If you MUST know if the last line of the file is blank, you'll have to get creative.

FOR /F "usebackq tokens=1,2 delims=]" %%a IN (`FIND /V /N "" ^< C:\filename.txt`) DO SET lastLine=%%b

How To Ask Questions The Smart Way


Reply ↓  Report •

#7
June 13, 2017 at 17:07:51
I am sharing the part I am struggling with , can I please request you to look into it and help me with how i can do this without breaking the loop.


for /f "tokens=1,2,3 delims= " %%a in (C:\testfolder\file1.txt) Do (
echo %%a %%b %%c>>C:\testfolder\URL.txt

Set Emailadd=%%a
Set Workbookname=%%b
Set Url=%%c
echo Worbookname is: %Workbookname! Url is: !Url! Email is :!Emailadd!


Pause
echo.

Set URL=!Workbookname!/!Url!

echo.

Pause
Call tableau directory path

tabcmd export "!URL!" --csv -f "C:\testfolder\!Emailadd!\testdemofinal.csv"

echo.

if ERRORLEVEL 0 (echo *** Tableau Report created and stored in the folder C:\testfolder)
if ERRORLEVEL 1 (echo *** error returned by tabcmd export & goto :tabcmd export)
))

:Remove combined column from the exported file
goto :3Param

::REM If statement to help choose where should the script proceed to.

:1Param
for /f "tokens=1,2,3* delims=," %%1 in (C:\testfolder\!Emailadd!\testdemofinal.csv) do (
echo (%%1,%%2,%%4)>>C:\testfolder\!Emailadd!\newfile.csv
)
goto :Dynamic Headers

:2Param
for /f "tokens=1,2,3,4* delims=," %%1 in (C:\testfolder\!Emailadd!\testdemofinal.csv) do (
echo (%%1,%%2,%%3,%%5)>>C:\testfolder\!Emailadd!\newfile.csv
)
goto :Dynamic Headers

:3Param
for /f "tokens=1,2,3,4,5* delims=," %%1 in (C:\testfolder\!Emailadd!\testdemofinal.csv) do (
echo (%%1,%%2,%%3,%%4,%%6)>>C:\testfolder\!Emailadd!\newfile.csv
)
goto :Dynamic Headers

)

:Dynamic Headers
Set ls=Lead State
Set dt=DeviceType

for /f "tokens=*" %%f in (C:\testfolder\!Emailadd!\newfile.csv) do (
set "line=%%f"
set string="Parameter3_Calculation"
set "line=!line:Parameter1_Calculation=%dt%!"
type "C:\testfolder\!!Emailadd!\newfile.csv" | find "%string%" > nul
if Errorlevel 0 (
set "line=!line:Parameter2_Calculation=%dt%!"
) Else (
REM No line:Parameter3_Calculation
)
type "C:\testfolder\!Emailadd!\newfile.csv" | find "%string%" > nul
if Errorlevel 0 (
set "line=!line:Parameter3_Calculation=%ls%!"
) Else (
REM No line:Parameter3_Calculation
)
echo !line!
)>> C:\testfolder\!Emailadd!\newfiledynamic.csv


:Use this to remove the string character

for /f "tokens=* delims=" %%a in (C:\testfolder\!Emailadd!\newfiledynamic.csv) do (
set oldName=%%a
set newName=!oldName:~1!
echo !newName!>>C:\testfolder\!Emailadd!\newfilename.csv
)

REM Pause
:logout
:: logout from server

echo. & echo Logging out of Tableau Server...
call Tableau Directory path
cls
:: suppress the *** 401 "Authorization Required" warning
:: tabcmd logout > nul 2>&1
:: Tableau Server 6.0.2 no longer outputs the 401 warning

tabcmd logout

echo *** Script completed.

echo.
echo *** Now we do the email part
echo.
Cd C:\
C:
blat -to !Emailadd! -subject "!Workbookname!_%Date%" -attacht C:\testfolder\!Emailadd!\newfilename.csv -body "Please see attached report. To explore or download the latest data visit sitename

if ERRORLEVEL 0 (echo *** Email Successfully sent)
if ERRORLEVEL 1 (echo *** error returned by blat & goto :erroremail)


goto :END

:sqlerror
Cd C:\
C:
blat -to !Emailadd! -subject "!Workbookname!_%Date%_Error" -body "Error in sql query"


goto :END

:subdirectoryerror
Cd C:\
C:
blat -to !Emailadd! -subject "!Workbookname!_%Date%_Error" -body "Error in subdirectory"

goto :END

:tabcmd export
Cd C:\
C:
blat -to !Emailadd! -subject "!Workbookname!_%Date%_Error" -body "Error in tabcmd export part"

goto :END

:erroremail
Cd C:\
C:
blat -to !Emailadd! -subject "!Workbookname!_%Date%_Error" -body "Error with sending email. Email may not be correct or may not exist."


goto :END

:END

message edited by anksravs


Reply ↓  Report •

#8
June 13, 2017 at 17:23:47
Is this "tableau" a batch script? If not, don't CALL it. If it is, "tableau" might be breaking your batch script.

Failing that:
if ERRORLEVEL 1 (echo *** error returned by tabcmd export & goto :tabcmd export)
Don't GOTO from a FOR loop unless you're trying to break out of the loop. CALL is generally okay.

How To Ask Questions The Smart Way


Reply ↓  Report •

#9
June 13, 2017 at 17:30:17
Call tableauDirectoryPath is just to change the directory to where tableau is stored on my machine.

How can i achieve the same without using goto?


Reply ↓  Report •

#10
June 13, 2017 at 19:07:29
I did say CALL was generally okay, but you should probably take that function into the FOR loop if it's only called by the FOR loop.

Really, no one sane does anything resembling complex logic in batch. Have you considered rewriting your script into VBScript or PowerShell?

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  Report •

Ask Question