Command query on excel file

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
ibirp
Posts: 2
Joined: 16 Mar 2021 20:39

Command query on excel file

#1 Post by ibirp » 16 Mar 2021 20:51

Hi. I have a sample excel sheet with 3 (or more) columns below:

cat1 kitty black
cat2 furry yellow
cat3 missy black
dog1 baxter white
dog2 ellon red
fish1 gold yellow
fish2 fin red


I need a command/script/batch file that queries from a certain column (say 1st column), then displays the result from selected columns only (say 1st and 2nd).
Example:

command: findpet.bat dog
result:
dog1 baxter
dog2 ellon

i only need the results to be displayed on the console. thanks!

Squashman
Expert
Posts: 4268
Joined: 23 Dec 2011 13:59

Re: Command query on excel file

#2 Post by Squashman » 17 Mar 2021 07:39

.BAT file cannot read excel files. You would need to use Vbscript or Powershell to do such a task.
If your file was a text file that was delimited by a specified delimiter then it should be fairly easy to do with a .BAT file.

ibirp
Posts: 2
Joined: 16 Mar 2021 20:39

Re: Command query on excel file

#3 Post by ibirp » 17 Mar 2021 10:16

Yes, i can convert the Excel sheet to a delimited text file.
If you have a sample bat i can work with, that would be great. Thanks!

ShadowThief
Expert
Posts: 1015
Joined: 06 Sep 2013 21:28
Location: Virginia, United States

Re: Command query on excel file

#4 Post by ShadowThief » 17 Mar 2021 20:15

Code: Select all

@echo off
if "%~1"=="" exit /b
set "source_file=data.csv"
for /f "tokens=1,2 delims= " %%A in ('findstr /C:"%~1" %source_file%') do echo %%A %%B
This assumes that
  1. Your source file is called data.csv
  2. Your data is separated by spaces (despite the C in CSV standing for "comma")
  3. You're fine with hardcoding the data filename

Compo
Posts: 527
Joined: 21 Mar 2014 08:50

Re: Command query on excel file

#5 Post by Compo » 19 Mar 2021 09:47

ShadowThief, It would be safer to perform your search against the content of the target field, not over the entire data record. What happens if, the fish is a catfish, or dogfish? the dog is named Catlin? or the color of the cat is dogs liver?

ShadowThief
Expert
Posts: 1015
Joined: 06 Sep 2013 21:28
Location: Virginia, United States

Re: Command query on excel file

#6 Post by ShadowThief » 19 Mar 2021 11:54

Compo wrote:
19 Mar 2021 09:47
ShadowThief, It would be safer to perform your search against the content of the target field, not over the entire data record. What happens if, the fish is a catfish, or dogfish? the dog is named Catlin? or the color of the cat is dogs liver?
I put in as much effort as ibirp put into their question

Post Reply