# Solved: Read text file and use instr type command



## kt1978 (Mar 20, 2011)

Hi all

Completely new to Batch files and this is my first post regarding them.

I want to be able to do 3 things

1, loop through lines of text file - DONE (This is what I have so far. I can't get the mid function to work with a dynamic variable.


```
@echo off
setLocal EnableDelayedExpansion
for /f "tokens=* delims= " %%a in (myfile.txt) do (
  set /a N+=1
  set v!N!=%%a
  set str=%%a
  echo. %str:~51,10%
  if !N! == 4 goto labelA
)
:labelA
pause
```
2, use an instr command that will get position of certain text (FROM and THRU)
3, create variable using mid command from position of instr result

I have done this in excel and have put the code below to hopefully explain further what I want to do.


```
Open sFile For Input As #iFile
   While Not EOF(iFile)
   Line Input #iFile, WholeLine
 
      strSearch = "FROM:"
      If InStr(1, WholeLine, strSearch) > 0 Then
        strDFrom = Mid(WholeLine, InStr(1, WholeLine, strSearch) + Len(strSearch) + 1, 8)
        strTFrom = Mid(WholeLine, InStr(1, WholeLine, strSearch) + Len(strSearch) + 13, 8)
      End If
      strSearch = "THRU:"
      If InStr(1, WholeLine, strSearch) > 0 Then
        strDThru = Mid(WholeLine, InStr(1, WholeLine, strSearch) + Len(strSearch) + 1, 8)
        strTThru = Mid(WholeLine, InStr(1, WholeLine, strSearch) + Len(strSearch) + 13, 8)
        GoTo LabelA
      End If
 
   Wend
LabelA:
```


----------



## Squashman (Apr 4, 2003)

I have to ask why you are trying to do this in batch. Why didn't you just write it as a vbscript.


----------



## kt1978 (Mar 20, 2011)

Hi Squashman

I have already done with using excel VBA but I have some issues with doing it like that. I explain, so that you understand my reasons and maybe suggest where my failings are...

Due to restrictions at work I am unable to run a .bat file from excel i.e.

This will not work on work PC's

```
Call Shell(ThisWorkbook.Path & "\MYFILE.BAT")
```
I use a .bat file to get a spool file from a location then which is like this


```
open xxx.xx.xx.xx 
sam_ftp
ftppassxxx
cd /folder/info/data
get spool.30 myFile.txt
bye
```
Then in excel I run some code that opens the text file, gets the required info and then renames it. (My excel file then does lots of other stuff with the data, it is not just for renaming the file)

Ideally, I don't want the users to have to open excel just for the renaming of the file. I am also looking at the possibilty of having a spool file run automatically each day which would also rename the file. Hense, my original question.

Hope you can help...


----------



## Squashman (Apr 4, 2003)

Nope. Doesn't really help. Don't understand what you are trying to do.


----------



## kt1978 (Mar 20, 2011)

OK.

We use a Telnet based system at work that we can spool files into a txt file.

I then use the .bat file to 'get' the spool and put it into my folder and it calls it myFile.txt (for example)

I then use excel to import the text file. While doing this my vba script renames the txt file with the date info it gets from the text file.

e.g. myFile_210311_06:00.txt

*What I want to do*
Rename the spool file using a .bat file instead of excel
Basically, open the file loop through the first 4 lines, get the date.time info and rename the file.

*Why*
Because each time i use a .bat file to get the spool it always uses the same name (myFile.txt), which means that my excel process has to be run each time the .bat file is run.

I need my users to be able to run the .bat file several times on different spool dates and I don't want them to have to open excel everytime to do this so that the files are renamed.

I also don't want my users to have to rename any files manually... for obvious reasons.

Hope this makes more sense on Why I want to do this.


----------



## Squashman (Apr 4, 2003)

Doesn't really explain why you cant convert your excel vbmacro to a stand alone vbscript?


----------



## kt1978 (Mar 20, 2011)

Didn't realise that can be done. How would I do that?

Is the batch file not a good idea then? Can that not be done?


----------



## Squashman (Apr 4, 2003)

kt1978 said:


> Didn't realise that can be done. How would I do that?
> 
> Is the batch file not a good idea then? Can that not be done?


I think you are going to be limited on what you want to do if you code it as a batch file.


----------



## kt1978 (Mar 20, 2011)

My bat file requirement will stop at that. I just want to be able to rename the bat file by the info within it. I must be able to be done?!?!?!

We use the bat files everyday and they work really well. I just want that bit extra from them...

I will explore the vbscript as well to see if that is an alternative. Can anyone point me in the right direction on how I would code the following in a vb script.


```
open xxx.xx.xx.xx 
sam_ftp
ftppassxxx
cd /folder/info/data
get spool.30 myFile.txt
bye
```
Hope someone can assist with either the bat or vbscirpt.


----------



## Squashman (Apr 4, 2003)

kt1978 said:


> *What I want to do*
> Rename the spool file using a .bat file instead of excel
> Basically, open the file loop through the first 4 lines, get the date.time info and rename the file.


Well you already have the loop written in your initial post and renaming shouldn't be that difficult. You just use the rename command.

I will try and help you with what you want to do but I don't know what you want help with first.

Just curious as to why you are using a counter variable and then exiting the For Loop when it equals 4?


----------



## kt1978 (Mar 20, 2011)

Hi



> I will try and help you with what you want to do but I don't know what you want help with first.
> 
> Just curious as to why you are using a counter variable and then exiting the For Loop when it equals 4?


Ok

The reason for the loop exiting at 4 is because the info required is going to be on either line 2 or line 3, so it doesn't need to continue from there.

The renaming should be ok once I have the variables stored.

How do I assign values to variables in the for loop to achieve the following...

1, I need to use the instr type command to find the position of the text "FROM" in the line
2, I then need to use the Mid function to get the date info which starts after "FROM"
3, I need to assign the value(s) gathered from above to variable(s) so I can generate a new filename to rename the file to.

If you look at my very first post again, you can see what I have done in excel but need to do in a bat file.

Hope this makes sense...


----------



## Squashman (Apr 4, 2003)

kt1978 said:


> The reason for the loop exiting at 4 is because the info required is going to be on either line 2 or line 3, so it doesn't need to continue from there.


So you can't use the skip option in the For Loop?



> 1, I need to use the instr type command to find the position of the text "FROM" in the line
> 2, I then need to use the Mid function to get the date info which starts after "FROM"


This is where batch is going to fail you. That is why I told you to use VB. Batch doesn't have native functions to do that. You basically have the VB code written in your Excel macro. This is why I told you VBscript would be better for this.

It certainly would not hurt to have a combination of Batch and Vb. You can call a batch or Vbscript from another batch file or vbscript.

I am not a VBscript type of guy. Never bothered to learn it. So I would have a hard time re-writing your vb builtin functions to work as pure batch. Batch does not have a MID function and I don't know what the *instr type command* is.

There may be a better way to do this with batch. If you can post an example of what is in text file I may be able to come up with something to make it work in pure batch but I can't guarantee it.


----------



## kt1978 (Mar 20, 2011)

> So you can't use the skip option in the For Loop?


Ideally, i would just use a goto command to exit the for loop once both variables were assigned as I do in vb.



> This is where batch is going to fail you. That is why I told you to use VB. Batch doesn't have native functions to do that


Ok, at least I know that it can't be done as opposed to just not knowing how to do it?



> There may be a better way to do this with batch. If you can post an example of what is in text file I may be able to come up with something to make it work in pure batch but I can't guarantee it.


Here is a sample of the kind of info in the first 3 lines. I would want to get the date and time info.


```
AB1234                                       REPORT NAME                            21/02/11 13:03    1
ABC 06 ROOM 08                            FROM: 18/02/11 AT 06:00
                                                  THRU: 19/02/11 AT 05:59
```



> That is why I told you to use VB!


Back to the drawing board. I am going to see if I can do the file rename bit in vb.

The way I think this would then work would be to run the bat to 'get' the file and then have the bat run the vb script.

Wasn't expecting such a long post, but like I originally said, new to bat files.

Thanks for the info you have given...


----------



## Squashman (Apr 4, 2003)

If the format of line 2 & 3 is always consistent you can use the delimiter option on the for loop to parse out the date from those two lines.


----------



## kt1978 (Mar 20, 2011)

Hi Squashman

You certainly made me work for this one... but thanks for guiding me in the right direction.

I have done what I wanted. Here is my solution if anyone else is interested.


```
@ECHO OFF
setLocal EnableDelayedExpansion
SET filename=myfile
FOR /f "tokens=1-8 delims= " %%a IN (!filename!.txt) DO (
 SET var=
 SET var=!var!%%a
 IF "!var!" == "ABC" (
  SET var1=!var1!%%f_%%h
  SET var1=!var1:/=!
  SET var1=!var1::=!
 )
 
 IF "!var!" == "THRU:" (
  SET var2=!var2!%%b_%%d
  SET var2=!var2:/=!
  SET var2=!var2::=!
  SET var=!var1!_!var2!
  GOTO :LabelA
 )
)
:LabelA
ECHO.
ECHO.     Information...
ECHO.
ECHO.     !filename! will now be renamed to !filename!_!var!
ECHO.
ECHO.     PRESS 1 to Continue
ECHO.     OR
ECHO.     PRESS 2 to Cancel 
ECHO.
SET /P M=Option: 
IF %M%==1 GOTO cont
IF %M%==2 GOTO canc
:cont
RENAME !filename!.txt !filename!_!var!.txt
ECHO msgbox"File renamed!">a.vbs&a.vbs&del a.vbs
GOTO end
:canc
ECHO msgbox"Operation Cancelled!">a.vbs&a.vbs&del a.vbs
:end
```
In my actual file I have not go the options bit as its not required, but left it in here as I was just playing around to see what else I could do with it and learn a bit more...

I will look into the vbs more as that seems like i'll be able to do a lot more with it and also pick it up a lot quicker as I'm used to vba.

Thanks again...:up:


----------

