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
679 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

In a hurry?.. scroll down to "HERE's HOW TO DO IT" Section. Greetings All, I was going to post this as question/solution, but its seems more appropriate as an article considering its length.  I felt it important to illucidate all the details c…
The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
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…

739 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