Solved

Batch script to seach a CSV file for specific text, if found, read all words on that line into seperate variables.

Posted on 2013-05-20
7
677 Views
Last Modified: 2013-05-20
Thanks in advance for this one....

 I'm working on a batch file that does several different things, but have run into a bump in the road I can't seem to get passed. I need to add the ability to open a csv file for a code which the user will be prompted to enter, and if the code is found in the csv file, I need each comma delimited field on that same line read into separate variables so that the batch file can use that information for other functions.

The CSV file contains information on building locations and looks like the following: Building_Name,Street_Address,City,Number,Building Code,

The user will be prompted to enter the building code which is the 5th field in the CSV file, then if that is found, I need to have all fields on the same line read into separate variables to be used later.

Wish we could award as many points as we wanted, 500 doesn't seem like enough some time.

Thanks so much,
RBennett
0
Comment
Question by:Ryan Bennett
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 4

Assisted Solution

by:mouseware
mouseware earned 250 total points
ID: 39182592
where seven items per line, and looking for "green" in test.txt (echo vars rather than setting them:


for /f "tokens=1-7 delims=, " %%a in ('findstr /c:"green" /i test.txt') do (
	echo first: %%a
	echo second: %%b
	echo thrid: %%c
	echo fourth: %%d
	echo fifth: %%e
	echo sixth: %%f
	echo seventh: %%g
	)

Open in new window

0
 
LVL 26

Accepted Solution

by:
pony10us earned 250 total points
ID: 39182655
Slight modification on mouseware's code to allow for user input:

@echo off

SETLOCAL
SETLOCAL EnableDelayedExpansion
SETLOCAL EnableExtensions

SET /P Input=Please enter the Building Code [0-9]

for /f "tokens=1-7 delims=, " %%a in ('findstr /c:%Input% /i test.csv') do (
        echo %%a
	echo %%b
	echo %%c
	echo %%d
	echo %%e
	echo %%f
	echo %%g
)

Open in new window


Change the SET /P Input= to reflect what you want it to read as a prompt.  There is no error checking in this code so right now it will take anything typed.
0
 

Author Closing Comment

by:Ryan Bennett
ID: 39182831
Thanks to both of you. I almost had it though!
This is what I had, but couldn't get it to work....

set /p fndcode= Enter the Building Code for this machine:
for /F "tokens=1-5 delims=," %%a in ('"Building Code 2012-08-22.csv" ^| find /i "%fndcode%"') do (
set building=%%a
set address=%%b
set city=%%c
set number=%%d
set bcode=%%e
 )

Silly me, I was stuck on the thought of piping to find.

Thanks again.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Expert Comment

by:pony10us
ID: 39182838
@rbennett

You were very close.  

As for the points, I think mouseware really deserved to recieve them.  I simply made a change to allow for the input which you already had.  I have no objection to your having the question re-opened and assign the total to mouseware.

Either way, I am glad you got it working.  :)
0
 
LVL 4

Expert Comment

by:mouseware
ID: 39182845
ha, no worries, i left out the user prompt part =)
0
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 39182886
Here is a powershell solution.

#question from http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_28133759.html

$filename = Read-Host "What is the input filename?" 
if ((Test-Path -Path $filename) -eq $true) { get-input($filename) }
else {
    Write-Host("File not found")
    }

function get-input($filename) {
    $BuildingCode = Read-Host  -Prompt "Building Code To Find"
    $BuildingCode = $BuildingCode.ToLower()
    $myobjects = Import-Csv -Path $filename
    $found = $false
    foreach ($object in $myobjects) {
        $test = $object.Building_Code.tolower()
        if ($test -eq $BuildingCode) {
            $found = $true
            $object
            #
            # here is the start for the batch file or executable that requires the output from the CSV using the Building_code
            #

c:\test\output.bat "$object.Building_Name" "$object.Street_Address" "$object.City" "$object.Number" "$object.Building_Code"
            }
        else { }
    }
 if ( $found -eq $false) { Write-Output("Building Code Not Found")}
    else { Write-Output ("Code Found") }
 }

Open in new window


@echo %1 %2 %3 %4 %5 %6 %7

Open in new window

"Building_Name","Street_Address","City","Number","Building_Code"
Hampstead Inn,Any Street, Cambridge, 123,Code1
Experts Lounge,Main Street, Los Angeles,999,010

Open in new window

0
 

Author Comment

by:Ryan Bennett
ID: 39182948
I would be glad to do change the points if you think that would be fair, although I'm not sure I know how to do that. I wish I could give you both the full points.

 And Ve3ofa that is interesting that you would post a powershell script, as I am just getting into that but just barely starting. So that was very nice to see a real world script all laid out. Any tips on books, sites, things to help the beginning wannabe?

 Thanks all
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My purpose is to describe the basic concepts of virtual memory as implemented in a modern Windows-based operating system. I will also describe the problems inherent in older systems and how virtual memory solves them. The dark ages - before virtu…
The way I use Experts Exchange to assist me in analyzing and diagnosing a problem is I first enter a Verbose Question at Experts Exchange like: Office 2007 will hang when opening and saving files I then launch WordPad (any text editor will do) an…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question