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
681 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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 81

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Nathan Brom/Bromy2004 Introduction There are numerous websites out there for any different type of program you can imagine.  Of those, you'll need to decide which ones are legitimate and aren't trying to steal your money or infect your comput…
Have you ever had a hard drive that you can't boot into, but need to change the registry? Here is the solution! This article guides you through accessing and editing a registry of a non-primary drive. To read registry information on a non-prim…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

687 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