?
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
Medium Priority
?
683 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 1000 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 1000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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 82

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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…

770 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