# Solved: Batch file question



## rrichmond (Jun 22, 2009)

Hi,

I have a csv file, that I need to modify and then output again as a csv file.
I would like to read each line of the file, and then modify it.

The file looks like this:

"B03570","Joe ","Blow"
"B02328","Mary","Smith"
"B02187","Homer J","Simpson"

What I require in the end, is for the user to enter the filename and a Letter (for example G) and then the batch file to add the letter A in front of it so we end up with:

"AGB03570","Joe ","Blow"
"AGB02328","Mary","Smith"
"AGB02187","Homer J","Simpson"

So I thought the input could look like (I used the letter X here so you get the idea):

textfile.csv X

(which contains

"B03570","Joe ","Blow"
"B02328","Mary","Smith"
"B02187","Homer J","Simpson" )

which outputs:

textfile-modified.csv

(which contains:

"AXB03570","Joe ","Blow"
"AXB02328","Mary","Smith"
"AXB02187","Homer J","Simpson" )


This should be possible, but how?


----------



## Squashman (Apr 4, 2003)

I am going to assume you only want it to insert this into the First Field and at the beginning of that field. 
What I am not understanding is how we are getting two letters inserted. Will the letter "A" always be constant"?
Will the codes always be 1 alpha character and the remaining characters are numeric?

Should be easy enough to do but what I am wondering is what will happen if this file is passed thru the batch file a second time.


----------



## rrichmond (Jun 22, 2009)

Squashman said:


> I am going to assume you only want it to insert this into the First Field and at the beginning of that field.


Yes. Correct.



Squashman said:


> What I am not understanding is how we are getting two letters inserted. Will the letter "A" always be constant"?


Correct again.



Squashman said:


> Will the codes always be 1 alpha character and the remaining characters are numeric?


Yes. In fact the first letter will always be "B"



Squashman said:


> Should be easy enough to do but what I am wondering is what will happen if this file is passed thru the batch file a second time.


I want it to output the data into a new filename, so the original file is left untouched. There will never be any need to modify this file once it has been changed.


----------



## Squashman (Apr 4, 2003)

```
@echo off & setlocal enableextensions enabledelayedexpansion
if "%1"=="" goto usage
if "%2"=="" goto usage
if "%1"=="/?" goto usage


FOR /F "Tokens=1* delims=," %%A IN (%1) DO (
     set _Tvar1=%%A
     set _Tvar2=!_Tvar1:~1!
     echo.^"A%2!_Tvar2!,%%B>>newfile.csv
     )
goto :EOF

:usage
echo.Batch file should be executed as:
echo. addcodes.bat inputfile.csv X
echo. X is the letter you want to add
```


----------



## Squashman (Apr 4, 2003)

Forgot about the output file name. Changed it so that it will be the input file name plus modified.

```
@echo off & setlocal enableextensions enabledelayedexpansion
if "%1"=="" goto usage
if "%2"=="" goto usage
if "%1"=="/?" goto usage


FOR /F "Tokens=1* delims=," %%A IN (%1) DO (
     set _Tvar1=%%A
     set _Tvar2=!_Tvar1:~1!
     echo.^"A%2!_Tvar2!,%%B>>%~n1-modified.csv
     )
goto :EOF

:usage
echo.Batch file should be executed as:
echo. addcodes.bat inputfile.csv X
echo. X is the letter you want to add
```


----------



## ghostdog74 (Dec 7, 2005)

if you have Gawk for windows (see my sig)

```
BEGIN{
	printf "Enter letter: "
	getline letter <"-"	
	OFS=FS=","	
}
{
 	$1="\"A"letter substr($1,2)
 	print $0 > "newcsv.csv"
}
```
save the above as myscript.awk and on command line
output

```
C:\test>gawk -f test.awk  file.txt
Enter letter: X

C:\test>more newcsv.csv
"AXB03570","Joe ","Blow"
"AXB02328","Mary","Smith"
"AXB02187","Homer J","Simpson"
```


----------



## rrichmond (Jun 22, 2009)

Squashman ...You are brilliant!! :> Where the thank option on this board??


----------



## rrichmond (Jun 22, 2009)

I have come across a different problem.

When I use this in my script:

ECHO Quote = ">> %ctlfile%

(It outputs the line into a file)

I keep getting output on the display:

Quote = ">> Filename.ctl

But the rest of the file ends up in the ctl file just fine. Is there an escape character or something you can use in batch scripts so it sees the quotation mark to be added to the file ??


----------



## TheOutcaste (Aug 8, 2007)

Squashman said:


> echo.*^*"A%2!_Tvar2!,%%B>>%~n1-modified.csv
> [/code]


Use the *^* in front of the quote as Squashman did.


----------



## Squashman (Apr 4, 2003)

rrichmond said:


> Squashman ...You are brilliant!! :> Where the thank option on this board??


Not really. I learned everything from ^ that guy.


----------



## rrichmond (Jun 22, 2009)

Ok..... I have almost completed the batch file. (I have a number of options the user can choose from)

With my last option, I need to take two different csv files, and merge some of the details.

The first file (let's call it file1.csv) contains the following data:

"AGB03570","Joe ","Blow"
"AGB02328","Mary","Smith"
"AGB02187","Homer J","Simpson"

The second file called file2.csv contains the following data

.AGB03570.2009.RC,g3uui946
.AGB02328.2009.RC,eorq1ert
.AGB02187.2009.RC,bp0913e5

What I require in the final file is

UserID,Surname,FirstName,Password
AGB03570,Blow,Joe,g8d7fj1j
AGB02328,Smith,Mary,eorq15e3
AGB02187,Simpson,Homer J,bp09164a

The quotation marks do not need to be included from the first file. (The Surname and Firstname fields are switched). The first line can be added simply by:

ECHO UserID,Surname,FirstName,Password> %finalfile%

How can I do the rest?? Any ideas?


----------



## Squashman (Apr 4, 2003)

Easy enough to do.
Just a nested For Loop to match the First Token(Variable) of each file.


----------



## Squashman (Apr 4, 2003)

Only bad thing about this batch file is it has to parse the entire file2.csv every time. Not sure how to work around that. We could start by sorting the files first. That would probably help a bit. Then you could set a counter inside the second for loop to set a skip variable so that it wouldn't always start with the first record on file2.csv. You would then add the skip option to the for loop. If your file is only going to be a couple hundred or even a couple thousand names then time is pretty relative. It will finish rather quickly regardless.

```
@echo off & setlocal enableextensions enabledelayedexpansion

ECHO UserID,Surname,FirstName,Password>finalfile.csv
FOR /F "Tokens=1,2,3 delims=," %%A IN (file1.csv) DO (
     set _Tvar1=%%A
     set _Tvar2=!_Tvar1:^"=!
     FOR /F "Tokens=1,2,3,4 delims=.," %%I IN (file2.csv) DO (
      IF !_Tvar2!==%%I (
       set _Tfinal=%%I,%%C,%%B,%%L
       set _Ofinal=!_Tfinal:^"=!
       echo.!_Ofinal!>>finalfile.csv
      )
     )
)
```


----------



## ghostdog74 (Dec 7, 2005)

what you need is a good text processing tool...if you have gawk for windows, 

```
BEGIN{
 	FS="[,.]"	
	print "UserID,Surname,Firstname,Password"
}
NR==FNR{t[$2]=$NF;next}
{	
	gsub("\"","",$1)
	print $1","$3","$2","t[$1]
}
```
save the above as test.awk and on command line,

```
C:\test>gawk -f test.awk file2.txt file1.txt
UserID,Surname,Firstname,Password
AGB03570,"Blow","Joe ",g3uui946
AGB02328,"Smith","Mary",eorq1ert
AGB02187,"Simpson","Homer J",bp0913e5
```


----------



## rrichmond (Jun 22, 2009)

Squashman said:


> ```
> @echo off & setlocal enableextensions enabledelayedexpansion
> 
> ECHO UserID,Surname,FirstName,Password>finalfile.csv
> ...


I ran it.... and it looks like its doing something, but the output file only contains:

UserID,Surname,FirstName,Password

I have attached the files I have used and the output (I changed them to txt files so I could upload them)


----------



## rrichmond (Jun 22, 2009)

The only problem with this is that gawk needs to be installed and as the software will be run from different PCs, its easier to use a batch file. Am I correct?


----------



## ghostdog74 (Dec 7, 2005)

rrichmond said:


> The only problem with this is that gawk needs to be installed and as the software will be run from different PCs, its easier to use a batch file. Am I correct?


gawk.exe is very small sized executable file (only ~350Kb). just download it ONCE. Then bring to your PCs, just like you bring your bat files to your PCs. What's so difficult about that? A computer is there to improve your productivity. you should not restrict yourself but learn how to use good/ easy to use and appropriate tools for your job.


----------



## TheOutcaste (Aug 8, 2007)

Squashman said:


> Only bad thing about this batch file is it has to parse the entire file2.csv every time. Not sure how to work around that. We could start by sorting the files first. That would probably help a bit. Then you could set a counter inside the second for loop to set a skip variable so that it wouldn't always start with the first record on file2.csv.


Sort, count, and skip would probably be the easiest. If the file isn't too big, it should be cached in memory, so re-reading it won't actually be accessing the hard drive. You could also simulate an array and read the file into variables so it will be in memory, though if the file is larger than the amount of RAM available that could actually slow it down.

You can use the ~ loop modifier to remove the surrounding quotes and simplify it a little. Don't even need delayed expansion with this

```
@echo off & setlocal enableextensions enabledelayedexpansion

Echo UserID,Surname,FirstName,Password>finalfile.csv
FOR /F "Tokens=1-3 delims=," %%A IN (file1.csv) DO (
     FOR /F "Tokens=1-4 delims=.," %%I IN (file2.csv) DO (
         IF %%~A==%%~I Echo.%%~I,%%~C,%%~B,%%~L>>finalfile.csv
     )
)
```
Both this version and Squashman's worked for me. I'll try them with your sample files, see if that makes a difference. My first though is a typo in the last output line; if you type fnialfile.csv for example, you'd see only the header line in the output file, and might not see the missnamed file in explorer.

Gawk would work. There's lots of tools that handle text files easier than batch does, but for something this simple I don't see the need to have 3 files to copy to each PC; gawk, the gawk script, and the batch file.

Jerry


----------



## TheOutcaste (Aug 8, 2007)

Ah, the data in the first file doesn't start with AG as the sample data you posted does. And the 2nd file has 4 period delimited fields instead of three:
"B03570"
Instead of
"AGB03570"
and
.AGB03570.2009.STU.RC
Instead of
.AGB03570.2009.RC

If the data format will vary, that will make it very difficult to parse the data. You'd need to know all possible formats, and test to see which format is being used before you can start to process the files.

Will the first file never start with AG, or does it sometimes start with AG? Or maybe start with something else? Same question for the 2nd file. Or do you just need to compare the last 6 characters?
Will the second file be either 3 or 4 items delimited by periods, or possibly 5 or 6? Or just 1 or 2? Will there always only be one comma in the 2nd file, or more? Will the password always be the last item on the line in the 2nd file?

Do you want the output to be
 AGB03570,Blow,Joe ,g8d7fj1j
or
B03570,Blow,Joe ,g8d7fj1j


----------



## ghostdog74 (Dec 7, 2005)

TheOutcaste said:


> I don't see the need to have 3 files to copy to each PC; gawk, the gawk script, and the batch file.


this should be the least important things to come to mind. How much effort does it take anyway. one should instead consider how much time one is going to waste NOT using such productivity tools.

if 3 files is too many, zip them up and bring to PC....what's so difficult? In real life, downloading commandline tools and using them in batch happen everyday, no big deal.


----------



## Squashman (Apr 4, 2003)

ghostdog74 said:


> this should be the least important things to come to mind. How much effort does it take anyway. one should instead consider how much time one is going to waste NOT using such productivity tools.
> 
> if 3 files is too many, zip them up and bring to PC....what's so difficult? In real life, downloading commandline tools and using them in batch happen everyday, no big deal.


Trying rolling that out to a couple hundred PC's.

I agree with you that the Unix based utilities are much better at doing data processing and I do data processing for a living. I actually have a script that is posted on Novell's Cool solutions website for something I did when I worked for the school district. I come from a Unix and Novell background. Learning batch was something I had to do after the fact.

The problem I have with some of the unix based shell utilities is that sometimes the syntax is different from the shell you were working on in Unix to the Windows enviroment. Even the syntax can be different in different Unix Shell Environments.

Now if this is a script that a lot of people will be using, doing you really want to have to train a couple hundred people how to use all the Unix shell utilities.

I have been tempted to start writing all my script in PowerShell just because people tell me it is much more Powerful. But I just don't have the time to learn it right now.


----------



## ghostdog74 (Dec 7, 2005)

Squashman said:


> Trying rolling that out to a couple hundred PC's.


what's the difference with rolling a batch file to those couple of 100 PCs? you still have to go to them to install the batch. If there are more than 100PCs, i would find it strange if the company doesn't install some sort of software that can distribute applications to individual PCs automatically.



> I agree with you that the Unix based utilities are much better at doing data processing and I do data processing for a living. I actually have a script that is posted on Novell's Cool solutions website for something I did when I worked for the school district. I come from a Unix and Novell background. Learning batch was something I had to do after the fact.
> 
> The problem I have with some of the unix based shell utilities is that sometimes the syntax is different from the shell you were working on in Unix to the Windows enviroment. Even the syntax can be different in different Unix Shell Environments.


you have to code with a standard ,like POSIX. there is also books published that talks about writing portable shell scripts. for me, i rather use a programming language like Python or Perl instead of shell scripts. I don't have to worry too much about different shell syntaxes.



> Now if this is a script that a lot of people will be using, doing you really want to have to train a couple hundred people how to use all the Unix shell utilities.


no..the people using the script will be people just using them...they will not be the ones to meddle with the code or even care about the code. Only the developer/administrator will maintain the script.



> I have been tempted to start writing all my script in PowerShell just because people tell me it is much more Powerful. But I just don't have the time to learn it right now.


powershell only works in Windows. I would rather suggest you to learn Python or Perl. they are cross platform and have lots of modules (for Win32 and *nix) for every day sysadmin. For myself, Python is the tool i used most, from simple to complex tasks. One tool fits all.


----------



## Squashman (Apr 4, 2003)

TheOutcaste said:


> You can use the ~ loop modifier to remove the surrounding quotes and simplify it a little.


How did I miss that. I look at that dang help file everyday and it is the first example in the help file. I feel like a retard!


----------



## Squashman (Apr 4, 2003)

ghostdog74 said:


> powershell only works in Windows. I would rather suggest you to learn Python or Perl. they are cross platform and have lots of modules (for Win32 and *nix) for every day sysadmin. For myself, Python is the tool i used most, from simple to complex tasks. One tool fits all.


I wouldn't go as far to say as Perl being completely cross platform. I have encountered Perl scripts dying on Windows machines that worked just fine on my Linux servers.

I only work on two systems now. Mainframe and Windows. We dumped our Novell servers and they don't let me touch the Unix box.

The scripts I write have to be understandable to my entire department and I would think that goes for most any environment that does data processing.

The user we are helping can barely understand Batch Files, so why should we try and confuse him, with GNU Utilities, Python or Perl.

As far as PowerShell being only for Windows, what is so bad about that. How many Desktop PC's are not Windows where you work. If you are managing Windows Desktops it is the perfect tool for the job.


----------



## Squashman (Apr 4, 2003)

TheOutcaste said:


> Ah, the data in the first file doesn't start with AG as the sample data you posted does. And the 2nd file has 4 period delimited fields.


Agreed. The data files he posted do not match the example in his previous post.


----------



## ghostdog74 (Dec 7, 2005)

Squashman said:


> I wouldn't go as far to say as Perl being completely cross platform. I have encountered Perl scripts dying on Windows machines that worked just fine on my Linux servers.


at least it doesn't have to encounter different Perl versions with different syntax. Internally, regex engines and functions are quite standard. i would tend to believe its the modules you use that is causing the problem.



> The scripts I write have to be understandable to my entire department and I would think that goes for most any environment that does data processing.


if that's the case, write your scripts in one universal language. Just like speaking english. In my previous company, i changed all shell scripts to Python as its syntax is clean and readable so its beneficial to all, and i recommended our system developers to understand it. this way, its standardized and system admins won't have to take care of different kinds of scripts.



> The user we are helping can barely understand Batch Files, so why should we try and confuse him, with GNU Utilities, Python or Perl.


doesn't understand ? nobody does in the beginning right? (and that sounds like an excuse). it takes studying and practice to understand things. anyway if it so confusing using different tools, then just use one good one.



> As far as PowerShell being only for Windows, what is so bad about that. How many Desktop PC's are not Windows where you work. If you are managing Windows Desktops it is the perfect tool for the job.


for PCs with older version of windows, you will still need the .NET components in order to run your powershell script. i am not saying its bad, but for a sys admin taking care of windows and unix machine simultaneously, choosing one good programming language to use certainly lessen the learning curve.


----------



## Squashman (Apr 4, 2003)

I believe in using the right tool for the right job.
Batch takes care of this quite efficiently without having to install any 3rd party utilities.

We had an application developer where I worked who loved to program Python. Problem was he was the only one who did it. Then he moved on to another job and the application needed to be updated to accomodate some new government regulations but nobody else in the IS dept knew Python. So we were screwed for about a 2 weeks because of that. The application was rewritten as a GUI with .NET.

We also ended up re-writing one of his Python Scripts to a Batch file. 

Law of economics. You do what you do best.


----------



## ghostdog74 (Dec 7, 2005)

Squashman said:


> I believe in using the right tool for the right job.
> Batch takes care of this quite efficiently without having to install any 3rd party utilities.


true, using the right tool for the job. Batch can be used only to a certain extent. beyond that , code gets ugly, hard to read and complicated (you can see the difference in the number of lines of code between the gawk version and the batch one). Also, batch can lead to inefficient code due to lack of program control. I have programmed complex tasks in batch as well so i know. Batch just doesn't cut it. anyway, that said, to each his own.



> We had an application developer where I worked who loved to program Python. Problem was he was the only one who did it. Then he moved on to another job and the application needed to be updated to accomodate some new government regulations but nobody else in the IS dept knew Python. So we were screwed for about a 2 weeks because of that. The application was rewritten as a GUI with .NET.


this is question that needs to be answered by your superiors. 
1) for back end job and automation, what language should we use. ?
2) for doing desktop applications , what language should we use?
3) for doing web applications, what platform should we use.?
set a standard, then, go for training. that's the correct way to go.

i am curious, don't tell me your application developers can use any kind of programming language that they knew best to do their task? if yes, it will be a mess.


----------



## Squashman (Apr 4, 2003)

We are kind of hijacking richmond's thread here. All our Business applications on the PC's are written in .NET now. 
Majority of our data processing is done on a Mainframe. They write all those apps in PL1 or Cobol. But the Majority of our data processing is done with a Pre-Packaged application written by another company. That app was written in Assembler and it still works awesome today.


----------



## rrichmond (Jun 22, 2009)

TheOutcaste said:


> Ah, the data in the first file doesn't start with AG as the sample data you posted does. And the 2nd file has 4 period delimited fields instead of three:
> "B03570"
> Instead of
> "AGB03570"
> ...


Ah. Yes..That was a typo... sorry.



TheOutcaste said:


> If the data format will vary, that will make it very difficult to parse the data. You'd need to know all possible formats, and test to see which format is being used before you can start to process the files.


No. The data will always be in the format:

.AGB03570.2009.STU.RC

The only items that will change is the first and second items. STU.RC will always be this way.

Changes that are possible will be AXB0XXXX.XXXX.STU.RC



TheOutcaste said:


> Will the first file never start with AG, or does it sometimes start with AG? Or maybe start with something else? Same question for the 2nd file. Or do you just need to compare the last 6 characters?
> 
> Will the second file be either 3 or 4 items delimited by periods, or possibly 5 or 6? Or just 1 or 2? Will there always only be one comma in the 2nd file, or more? Will the password always be the last item on the line in the 2nd file?


Hmmm..... Do we need all this info? How about the output I supplied below?



TheOutcaste said:


> Do you want the output to be
> AGB03570,Blow,Joe ,g8d7fj1j
> or
> B03570,Blow,Joe ,g8d7fj1j


Starting with (in File1.csv):

AGB03570,Blow,Joe
AGB02328,Harridge,Scott Mitchell
AGB02187,Jaeschke,Benjamin Michael

And in File2.csv

.AGB03570.2009.STU.RC,8mh4j7vy
.AGB02328.2009.STU.RC,7z908myl
.AGB02187.2009.STU.RC,4wfmd2b2

I need the finalfile (finalfile.csv) to be:

UserID,Surname,FirstName,Password
AGB03570,Blow,Joe,8mh4j7vy
AGB02328,Harridge,Scott Mitchell,7z908myl
AGB02187,Jaeschke,Benjamin Michael,4wfmd2b2

Does this make sense?

I just tested it again and I still end up with...

UserID,Surname,FirstName,Password

And thats it :<

How can I troubleshoot this? Can I add somethingthat would show me what is occurring perhaps?


----------



## Squashman (Apr 4, 2003)

As long as the first variables match, it will output something. But since you now have 5 delimited fields in your second file the current batch file will output the RC instead of the password. So you need to add an additional Token to the Second For Loop and change the %%L to %%M.

Which batch file did you use. Mine or Jerry's?

You changed the format of your input file 1 again. Now you have no quotes on input. With Jerry's batch it shouldn't matter but I think mine might choke on that. I will have ot test it out when I have a second.


----------



## rrichmond (Jun 22, 2009)

I tired both yours and Jerry's with the same result... :< I will try your suggestion later today as well.


----------



## Squashman (Apr 4, 2003)

I just ran it with my code and Jerry's code and they both worked.


----------



## Squashman (Apr 4, 2003)

Another data discrepancy on your end.

Your first example you gave us, you said the data would be the First Name, Lastname and we needed to output it as Lastname, Firstname. 

Now your last example you posted, you have the input as Last Name, Firstname on the input file.

So if the data is coming in Last Name,First name then you need to switch the %%C and %%B, so that they are in order. %%B,%%C in my batch file or %%~B,%%~C in Jerry's batch file.


----------



## rrichmond (Jun 22, 2009)

They both work?? I wonder what is occuring at this end to stop them then?

Here is my test case... (Now I have left the files alone as they come out of the software I am using).

file1.csv


```
"B03570","Callum     ","Burns"
"B02328","Scott Mitchell","Harridge"
"B02187","Benjamin Michael","Jaeschke"
"B02158","Samuel James","Lee"
"B04262","Morgan Jonathan","Lewis"
"B04266","Christopher John","Miller"
"B03582","Andrew Bryce Graham","Nuttall"
"B02009","Matthew Taylor","Pimm"
"B04271","Kieran James","Rice"
"B04903","Daniel","Selhorst"
"B02194","Peter James","Stringfellow"
"B02170","Jamie Leigh ","Williams"
```
file2.csv


```
.AGB03570.2009.STU.RC,g8d7fj1j
.AGB02328.2009.STU.RC,eorq15e3
.AGB02187.2009.STU.RC,bp09164a
.AGB02158.2009.STU.RC,3w30w37a
.AGB04262.2009.STU.RC,pp80294d
.AGB04266.2009.STU.RC,j01dvs8k
.AGB03582.2009.STU.RC,j52l1wnp
.AGB02009.2009.STU.RC,x9c4veio
.AGB04271.2009.STU.RC,k64sxj69
.AGB04903.2009.STU.RC,y774outq
.AGB02194.2009.STU.RC,83ib4081
.AGB02170.2009.STU.RC,sxaz52r8
```
(Note: in both files there is a final return at the end...I just noticed this..not sure if it makes any difference)

I created two batch files (so I could test each) in the same directory as file1.csv and file2.csv

The code is:

squashman.bat

```
@echo off & setlocal enableextensions enabledelayedexpansion

ECHO UserID,Surname,FirstName,Password>finalfile.csv
FOR /F "Tokens=1,2,3 delims=," %%A IN (file1.csv) DO (
     set _Tvar1=%%A
     set _Tvar2=!_Tvar1:^"=!
     FOR /F "Tokens=1,2,3,4 delims=.," %%I IN (file2.csv) DO (
      IF !_Tvar2!==%%I (
       set _Tfinal=%%I,%%C,%%B,%%L
       set _Ofinal=!_Tfinal:^"=!
       echo.!_Ofinal!>>finalfile.csv
      )
     )
)
```
and the other:

TheOutcaste.bat

```
@echo off & setlocal enableextensions enabledelayedexpansion

Echo UserID,Surname,FirstName,Password>finalfile.csv
FOR /F "Tokens=1-3 delims=," %%A IN (file1.csv) DO (
     FOR /F "Tokens=1-4 delims=.," %%I IN (file2.csv) DO (
         IF %%~A==%%~I Echo.%%~I,%%~C,%%~B,%%~L>>finalfile.csv
     )
)
```
When I test either I simply get:

finalfile.csv

```
UserID,Surname,FirstName,Password
```
I am running Vista Business edition, but have also tried XP Pro just in case.

It's is like it is never even running the For Loop??
In fact, if I remove the line:


```
Echo UserID,Surname,FirstName,Password>finalfile.csv
```
from my testing of the Outcaste code.... there seems two be two problems?

1) the line


```
IF %%~A==%%~I Echo.%%~I,%%~C,%%~B,%%~L>>finalfile.csv
```
Does not output anything, unless I remove


```
IF %%~A==%%~I
```
In which case I get 12 lines for each user of:


```
AGB03570,Burns,Callum     ,RC
AGB02328,Burns,Callum     ,RC
AGB02187,Burns,Callum     ,RC
AGB02158,Burns,Callum     ,RC
AGB04262,Burns,Callum     ,RC
AGB04266,Burns,Callum     ,RC
AGB03582,Burns,Callum     ,RC
AGB02009,Burns,Callum     ,RC
AGB04271,Burns,Callum     ,RC
AGB04903,Burns,Callum     ,RC
AGB02194,Burns,Callum     ,RC
AGB02170,Burns,Callum     ,RC
```
So is it that


```
IF %%~A==%%~I
```
is failing and not writing anything as a result? (Also I note that it is collecting "RC" and not the password)

Would it perhaps be simpler, to merge the two files into one file first and then just pick the items on each line and output that?

E.G.


```
"B03570","Callum     ","Burns",.AGB03570.2009.STU.RC,g8d7fj1j
"B02328","Scott Mitchell","Harridge",.AGB02328.2009.STU.RC,eorq15e3
etc...
```
End then create the file that looks like:


```
UserID,Surname,FirstName,Password
"AGB03570","Burns","Callum     ","g8d7fj1j"
"AGB02328","Harridge","Scott Mitchell","eorq15e3"
etc....
```
This code is soooo close to the answer...


----------



## ghostdog74 (Dec 7, 2005)

well , if you can't download tools, here's a native vbscript

```
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArg = WScript.Arguments
strFile1 = objArg(0)
strFile2 = objArg(1)
Set objFile = objFS.OpenTextFile(strFile2)
Set d = CreateObject("Scripting.Dictionary")
Do Until objFile.AtEndOfStream
	strLine = objFile.ReadLine	
	s = Split(strLine,",")
	ind = Split(s(0),".")
	If Not d.Exists(Mid(ind(1),3)) Then	
		d.Add Mid(ind(1),3),s(1)
	End If 
Loop
Set objFile=Nothing 
a = d.Keys
Set objFile = objFS.OpenTextFile(strFile1)
Do Until objFile.AtEndOfStream
	strLine = objFile.ReadLine	
	s = Split(strLine,",")
	o = s(0)
	s(0) = Replace(s(0),"""","")
	If d.Exists(s(0)) Then
		WScript.Echo o&","&s(2)&","&s(1)&","&	d.Item(s(0))
	End If 
Loop
Set objFile=Nothing
```
save the above as myscript.vbs and on command line

```
C:\test>cscript /nologo myscript.vbs file1.txt file2.txt
"B03570","Burns","Callum     ",g8d7fj1j
"B02328","Harridge","Scott Mitchell",eorq15e3
"B02187","Jaeschke","Benjamin Michael",bp09164a
"B02158","Lee","Samuel James",3w30w37a
"B04262","Lewis","Morgan Jonathan",pp80294d
"B04266","Miller","Christopher John",j01dvs8k
"B03582","Nuttall","Andrew Bryce Graham",j52l1wnp
"B02009","Pimm","Matthew Taylor",x9c4veio
"B04271","Rice","Kieran James",k64sxj69
"B04903","Selhorst","Daniel",y774outq
"B02194","Stringfellow","Peter James",83ib4081
"B02170","Williams","Jamie Leigh ",sxaz52r8
```


----------



## TheOutcaste (Aug 8, 2007)

OK, I think I see where the confusion is, at least for me. This is actually a 2 step process.
The first step takes the input file, adds AX to the User ID, swaps the name order, and removes quotes.
The 2nd step takes the *output* of the first step and works with it. We've all been looking at taking the first _input_ file as the input, instead of the first _output_ file for this 2nd step.
rrichmond did state this in post 11 and I totally missed it. Overlooked that part then got hung up on the *STU* part missing and didn't notice it until just now.

My apologies to rrichmond for the confusion.

The comparisons are case sensitive, so if you enter the G _lowercase_, you'll get nothing in the output. I've added a step to convert the entered letter *X* to an upper case letter. If file2.csv could be a mix of upper and lowercase, say AGBXXXXX on one line, and AgBXXXXX on another, the If statements will need to be made case insensitive by adding the /I switch (If /I 1st == 2nd)

So let's see if I have it straight now:

We have SourceFile 1 (_SF1) which is 3 fields, delimited by commas:
UserID,FirstName,Surname
"B03570","Joe ","Blow"
"B02328","Mary","Smith"
"B02187","Homer J","Simpson"

Step one modifies the UserID by pre-pending the letter A and a user supplied letter *X*, swaps the Name order, and removes surrounding quotes, producing Output File 1 (_OF1):
Mod-UserID,Surname,FirstName
A*X*B03570,Blow,Joe 
A*X*B02328,Smith,Mary
A*X*B02187,Simpson,Homer J

Step 2 takes _OF1 as the input, and takes the Modified UserID from each line and searches Source File 2 (_SF2).
_SF2 consists of five fields, delimited by _both_ period and comma:
ModUserID.Year.Field3.Field4,Password
.A*X*B03570.2009.STU.RC,g3uui946
.A*X*B02328.2009.STU.RC,eorq1ert
.A*X*B02187.2009.STU.RC,bp0913e5
.A*Y*B03864.2009.STU.RC,zw8712y8

Note that _SF2 can contain different Code letters (Y)

If a match is found for the Modified UserID, it creates a line in Output File 2 (_OF2) in this format:
ModUserId,Surname,FirstName,Password
A*X*B03570,Blow,Joe ,g3uui946 
A*X*B02328,Smith,Mary,eorq1ert
A*X*B02187,Simpson,Homer J,bp0913e5

So, the following batch file:


Asks for a file name.
Drive and path should be specified if it's not in the same folder as the batch file.
Spaces are allowed, but the user should not surround it with quotes
Verifies the file exists exists
Gets a single letter from the user
Takes just the 1st letter if the user enters more
Makes sure it's uppercase and valid
Creates the first Output File in same folder as the entered file name
The Output File name is created by pre-pending Mod- to the entered filename.
*This file will be deleted if it exists*
Processes the created Output File to create the Final file.
The Source File 2 is specified at the start of the batch file
The Final Filename is specified at the start of the batch file
This file will be created in the same folder as the batch file unless a path is specified
*This file will be deleted if it exists*


```
@Echo Off
Setlocal EnableDelayedExpansion
Set _SF2=[COLOR=DarkRed]file2.csv[/COLOR]
Set _OF2=[COLOR=Blue]FinalFile.csv[/COLOR]
Cls
:_Resp1
Set /P _SF1=Enter the [drive:][path]filename (unquoted): 
If NOT Exist "%_SF1%" (Echo Unable to locate "%_SF1%", please re-enter)&Goto _Resp1
Call :_SetOF1 "%_SF1%"
If Exist "%_OF1%" Del "%_OF1%"
If Exist "%_OF2%" Del "%_OF2%"
:_Resp2
Set _Code=
Set /P _Code=Enter a single letter code (A-Z): 
If NOT Defined _Code Goto_Resp2
Set _Code=%_Code:~0,1%
Call :_Upper _Code
If "%_Code%" GEQ "A" If "%_Code%" LEQ "Z" Goto _Process1
Echo You entered ^>%_Code%^<
Echo Please enter a letter between A-Z
Goto _Resp2
:_Process1
For /F "UseBackq Tokens=1-3 delims=," %%A In ("%_SF1%") Do Echo A%_Code%%%~A,%%~C,%%~B>>"%_OF1%"
>"%_OF2%" Echo UserID,Surname,FirstName,Password
For /F "UseBackq Tokens=1-3 delims=," %%A In ("%_OF1%") Do (
  For /F "UseBackq Tokens=1-5 delims=.," %%I In ("%_SF2%") Do IF %%~A==%%~I Echo.%%~I,%%~C,%%~B,%%~M>>"%_OF2%"
)
Goto :EOF
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: Subroutines
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:_Upper
For %%I In ("a=A" "b=B" "c=C" "d=D" "e=E" "f=F" "g=G" "h=H" "i=I" "j=J" "k=K" "l=L" "m=M" "n=N" "o=O" "p=P" "q=Q" "r=R" "s=S" "t=T" "u=U" "v=V" "w=W" "x=X" "y=Y" "z=Z") Do Call Set "%1=%%%1:%%~I%%"
Goto :EOF
:_SetOF1
Set _OF1=%~dp1[COLOR=Red]Mod-[/COLOR]%~nx1
Goto :EOF
```
Jerry


----------



## rrichmond (Jun 22, 2009)

Ghostdog74,

Hi, This might be the best way perhaps? The only thing I need to fix it so it's correct is change it to the following.

Add:


```
UserID,Surname,FirstName,Password
```
to the top of the file and replace


```
"B03570","Burns","Callum     ",g8d7fj1j
```
with


```
"AB03570","Burns","Callum     ","g8d7fj1j"
```
So the final output is:


```
UserID,Surname,FirstName,Password 
"AGB03570","Burns","Callum     ","g8d7fj1j"
"AGB02328","Harridge","Scott Mitchell","eorq15e3" 
"AGB02187","Jaeschke","Benjamin Michael","bp09164a"
"AGB02158","Lee","Samuel James","3w30w37a"
"AGB04262","Lewis","Morgan Jonathan","pp80294d"
"AGB04266","Miller","Christopher John","j01dvs8k"
"AGB03582","Nuttall","Andrew Bryce Graham","j52l1wnp"
"AGB02009","Pimm","Matthew Taylor","x9c4veio"
"AGB04271","Rice","Kieran James","k64sxj69"
"AGB04903","Selhorst","Daniel","y774outq"
"AGB02194","Stringfellow","Peter James","83ib4081"
"AGB02170","Williams","Jamie Leigh ","sxaz52r8"
```
The AGB0# comes from file2.csv

e.g.


```
.AGB03570.2009.STU.RC,g8d7fj1j
```
Is this possible?


----------



## rrichmond (Jun 22, 2009)

TheOutcaste said:


> If a match is found for the Modified UserID, it creates a line in Output File 2 (_OF2) in this format:
> 
> UserId,Surname,FirstName,Password
> AXB03570,Blow,Joe ,g3uui946
> ...


The output you have here is correct (The quotes are nice, but I have found they are not neccesary).

Your file still doesn't make this however. I don't need the user to enter the filename. In the script I have written, these values are already supplied, so all I need is a way to add the filenames via %file1%, %file2% and %output%...the names here aren't important..I can modify this myself.

I also do not need the user to enter a letter as this is also supplied in the file2.csv. The value changes, but it is supplied.


----------



## ghostdog74 (Dec 7, 2005)

rrichmond said:


> [/code]Is this possible?


yes of course

```
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objArg = WScript.Arguments
strFile1 = objArg(0)
strFile2 = objArg(1)
Set objFile = objFS.OpenTextFile(strFile2)
Set d = CreateObject("Scripting.Dictionary")
Set e = CreateObject("Scripting.Dictionary")
Do Until objFile.AtEndOfStream
	strLine = objFile.ReadLine	
	s = Split(strLine,",")
	ind = Split(s(0),".")
	If Not d.Exists(Mid(ind(1),3)) Then			
		d.Add Mid(ind(1),3),s(1)
		s_splitted = Split(s(0),".")
		e.Add Mid(ind(1),3),""""&s_splitted(1)&""""
	End If 
Loop
Set objFile=Nothing 
a = d.Keys
Set objFile = objFS.OpenTextFile(strFile1)
WScript.Echo  "UserID,Surname,FirstName,Password"
Do Until objFile.AtEndOfStream
	strLine = objFile.ReadLine	
	s = Split(strLine,",")
	s(0) = Replace(s(0),"""","")
	If d.Exists(s(0)) Then
		WScript.Echo e.Item(s(0))&","&s(2)&","&s(1)&","&	d.Item(s(0))
	End If 
Loop
Set objFile=Nothing
```
i expect you to , after this, go read up vbscript manual to understand how it works.


----------



## TheOutcaste (Aug 8, 2007)

OK, that's even easier if you don't need the intermediate file. Just enter the file names in the first three set statements.
If you want the quotes, they can easily be added on the output line:
Echo.*"*%%~I*"*,*"*%%~C*"*,*"*%%~B*"*,*"*%%~M*"*

```
@Echo Off
Setlocal EnableDelayedExpansion
Set _SF1=File1.csv
Set _SF2=file2.csv
Set _OF2=FinalFile.csv
>"%_OF2%" Echo UserID,Surname,FirstName,Password
For /F "UseBackq Tokens=1-3 delims=," %%A In ("%_SF1%") Do (
  For /F "UseBackq Tokens=1-5 delims=.," %%I In ("%_SF2%") Do (
    Set _ID=%%~I
    IF %%~A==!_ID:~2! Echo.%%~I,%%~C,%%~B,%%~M>>"%_OF2%"))
```
Jerry


----------



## Squashman (Apr 4, 2003)

All I got to say is OMG.
You kept changing your criteria thru all these posts and just confusing everyone.
I hope you at least learn how the code works and understand it so that you can at least change it if you ever need to.

You kept giving us examples that the first varaible of each file was going to be the same. That is what we were checking for. What did you think we were trying to code for.



rrichmond said:


> The output you have here is correct (The quotes are nice, but I have found they are not neccesary).
> 
> Your file still doesn't make this however. I don't need the user to enter the filename. In the script I have written, these values are already supplied, so all I need is a way to add the filenames via %file1%, %file2% and %output%...the names here aren't important..I can modify this myself.
> 
> I also do not need the user to enter a letter as this is also supplied in the file2.csv. The value changes, but it is supplied.


Again, you asked us to do this for you in your first example. So why would we have changed it for you unless you tell us too.

This should have been your very first post in this thread. I have this file and this file and I need it too output this way.


rrichmond said:


> Here is my test case...
> 
> file1.csv
> 
> ...


This whole thread wouldn't have been knocked down to 5 posts.


----------



## Squashman (Apr 4, 2003)

TheOutcaste said:


> OK, I think I see where the confusion is, at least for me. This is actually a 2 step process.
> The first step takes the input file, adds AX to the User ID, swaps the name order, and removes quotes.
> The 2nd step takes the *output* of the first step and works with it. We've all been looking at taking the first _input_ file as the input, instead of the first _output_ file for this 2nd step.
> rrichmond did state this in post 11 and I totally missed it.


I don't see how you got that out of Post 11. He just says File1 will be this and File2 is this and I need it to output this. That is how I coded it as.


----------



## Squashman (Apr 4, 2003)

TheOutcaste said:


> OK, that's even easier if you don't need the intermediate file. Just enter the file names in the first three set statements.
> If you want the quotes, they can easily be added on the output line:
> Echo.*"*%%~I*"*,*"*%%~C*"*,*"*%%~B*"*,*"*%%~M*"*
> 
> ...


Based on the users very last File1.csv and File2.csv examples above this batch file does work. Barring any other idiosynrocies this thread should be solved.


----------



## rrichmond (Jun 22, 2009)

Thank-you all for helping. I understand I might not have been as clear as required, but you all persisted through with me and we got there in the end. I really do appreciate your effort.

Squashman, I appreciate your help as well. I think most of the confuion came from me not being clear enough....next time (If there ever is one :>) I will provide examples of each section of code.

TheOutcaste, Thankyou. Your code works perfectly.
ghostdog74, Also, thankyou. Your code works perfectly as well, and I will start to learn VBScript. I never realised just how handy it could be for something such as this..If I needed to I could always output the code with the variables from my batch script, run it, and then delete the file, and leave the remaining file....quite cool!!


So Thankyou all. You work was great!


----------



## rrichmond (Jun 22, 2009)

I have been studying the batch script:


```
@Echo Off
Setlocal EnableDelayedExpansion
Set _SF1=File1.csv
Set _SF2=file2.csv
Set _OF2=FinalFile.csv
>"%_OF2%" Echo UserID,Surname,FirstName,Password
For /F "UseBackq Tokens=1-3 delims=," %%A In ("%_SF1%") Do (
  For /F "UseBackq Tokens=1-5 delims=.," %%I In ("%_SF2%") Do (
    Set _ID=%%~I
    IF %%~A==!_ID:~2! Echo.%%~I,%%~C,%%~B,%%~M>>"%_OF2%"))
```
What does the ~ mean in


```
Set _ID=%%~I
```
and what does the !_ID:~2! mean in

```
IF %%~A==!_ID:~2!
```
I have figured out the rest....


----------



## TheOutcaste (Aug 8, 2007)

%%*~*I the tilde modifier removes surrounding quotes.

See help for For and Call in a command prompt (For /?, Call /?) for a complete list of modifiers.

!_ID:~2!
The exclamation marks mean use Delayed Expansion to expand the variable (%var% does not use delayed expansion, !var! does). Delayed expansion is needed as the variable is being modified inside a For loop.
The :~ means we are extracting a substring. The 2 is the starting position (0 is the first character). No length is specified, so it uses the rest of the string.
See the help on the Set command (Set /?) for info on using variable substitution and substrings, and some info on Delayed Expansion.

Jerry


----------



## rrichmond (Jun 22, 2009)

Thanks Jerry.


----------

