how to create batch file to extract data from excel to txt

November 10, 2020 at 07:09:07
Specs: Windows 7
Hello,
I m new here. I trying to create batch file. The purpose of this file it should retrieve specific data from the *.csv, example entire line that found percentage of 80 %. For example

Server Name Disk used Percentage
server A C 80 %
server B D 80 %
server C F 80 %
The entire row which containing of all 80 % should be export to text file.
My source of data would be *.csv or *.xls files.
Please advice me if this can done using batch file.


See More: how to create batch file to extract data from excel to txt


#1
November 12, 2020 at 03:10:24
:: percent.bat 2020-11-12 17:59:01.36
@echo off > OUT.TXT & setLocal enableDELAYedeXpansioN

copy my.csv IN.TXT > nul
for /f "tokens=1-5 delims= " %%a in (IN.TXT) do (
if %%d equ 80 echo.%%a %%b %%c %%d %%e
) >> OUT.TXT

goto :eof

=====================

M2


Reply ↓  Report •

#2
November 12, 2020 at 07:14:40
Thank you so much for this review. Will check this out and update you soon.
Just a clarity
copy my.csv IN.TXT > nul
my.csv - is my source file (example the *.cvs or *.xls files either one of this ?)
Thank you..so much...
When I prepare the *.bat and my sources file as my.csv and run the batch file it turns to this errors message
C:\capacity\percent>my_percent
The system cannot find the file IN.TXT.
Hope you can review this and let me know thank you so much...

message edited by babu_hu


Reply ↓  Report •

#3
November 12, 2020 at 23:48:08
Do you have MY.CSV in the same directory as the batch?

=====================

M2


Reply ↓  Report •

Related Solutions

#4
November 13, 2020 at 10:13:29
Hello,
Yes, I keep all the files in on folder before I execute the batch file.
All file is saved to C:\capacity\percent>my_percent.bat
the source file is saved as *,cvs
Let me know why I cant get the IN.TXT file copied from the source file my.cvs
Thank you...

Reply ↓  Report •

#5
November 13, 2020 at 10:47:33
the source file is saved as *,cvs
is not a filename.

Do this and post the list:
dir/b *.csv

=====================

M2


Reply ↓  Report •

#6
November 13, 2020 at 22:00:22
Hi,
Yes, all files including MY.csv is saved onto the same folder.
When I execute the scripts it showing giving this output.
All files I saved onto specific folder called percent. The actual bat file I execute as my_percent.bat

C:\capacity\percent>my_percent.bat -> when I execute

This is output I getting it
The system cannot find the file IN.TXT.


Reply ↓  Report •

#7
November 13, 2020 at 22:00:47
Please let me know whats wrong in here.

Reply ↓  Report •

#8
November 13, 2020 at 22:30:05
Do this and post the list:
dir/b *.csv

I can't do this for you; if you don't do it, it don't get done.

message edited by Mechanix2Go


Reply ↓  Report •

#9
November 14, 2020 at 01:47:49
Hello my apologise missed this..earlier...thanks for pointing...
Here is output
C:\capacity\percent>dir/b *.csv
my_csv.csv

when I run the percent.bat
C:\capacity\percent>my_percent.bat
The system cannot find the file IN.TXT.
The above is the error output then I getting on...

Thank you and appreciate your help....


Reply ↓  Report •

#10
November 14, 2020 at 01:55:16
:: percent.bat 2020-11-12 17:59:01.36
@echo off > OUT.TXT & setLocal enableDELAYedeXpansioN

copy my_csv.csv IN.TXT > nul
for /f "tokens=1-5 delims= " %%a in (IN.TXT) do (
if %%d equ 80 echo.%%a %%b %%c %%d %%e
) >> OUT.TXT

goto :eof

=====================

M2


Reply ↓  Report •

#11
November 14, 2020 at 02:05:36
Hi the file start to execute some output.

The scripts run without any error but the OUT.TXT file has no data captured.
The IN.TXT have date captured.

The below is the file run when return its has no errors reports except the output is not coming on OUT.TXT file.

C:\capacity\percent>my_percent.bat

message edited by babu_hu


Reply ↓  Report •

#12
November 14, 2020 at 02:10:20
Is OUT.TXT empty?





=====================

M2


Reply ↓  Report •

#13
November 14, 2020 at 02:38:02
Yes sir the file shown empty. No output.

Reply ↓  Report •

#14
November 14, 2020 at 02:57:23
Post your my_csv.csv



:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

=====================

M2


Reply ↓  Report •

#15
November 14, 2020 at 05:51:22
SERVER_POOL_PLATFORM MANUFACTURER RAW_CAPACITY_ TOTAL_CAPACITY_ TOTAL_CAPACITY_USED_TIB Percentage
V000011STO DELL 45 39.6504 23.9805 60
V000012STO TOSHIBA 109.1611 71.9023 58.1084 81
V000013STO NEC 1205 1081.4902 865.1104 80
V000011STO HP 307.9199 269.3496 162.0703 60
V000012STO HP 307.9199 269.3496 162.0703 60

Here is file. My intention is to capture all those showing "Percentage" count that is above > 80, output to txt file.This is only 5 lines, I need to scan multi excel file which is having more than 500K line
Please review this and let me know.
Thank you.


Reply ↓  Report •

#16
November 14, 2020 at 06:08:12
Fist we'll make it work on ONE file.
=================================================
:: percent3.bat 2020-11-12 17:59:01.36
@echo off > OUT.TXT & setLocal enableDELAYedeXpansioN

copy my_csv.csv IN.TXT > nul
for /f "skip=1 tokens=1-6 delims= " %%a in (IN.TXT) do (
if %%f geq 80 echo.%%a %%b %%c %%d %%e %%f
) >> OUT.TXT

goto :eof

=====================

M2


Reply ↓  Report •

#17
November 14, 2020 at 19:50:38
Hey M2!
Not a big fan of findstr, it can be frustrating, but couldn't pass up this possibility:
@echo off & setlocal
pushd  C:\capacity\percent
:: this is temporary dump to hold the output, so no naming conflicts arise
md testwork
for /f "tokens=1* delims=:" %%a in ('findstr  "\<[89][0-9]$ \<100$" *.csv') do >> testwork\%%a echo %%b

Of course, only works if format is consistent in the CSV files. I don't think batch can work with .XLS format files, but excel can export them to CSV

message edited by nbrane


Reply ↓  Report •

#18
November 15, 2020 at 00:53:03
nbrane,

I disliked findstr from the start.

=====================

M2


Reply ↓  Report •

#19
November 15, 2020 at 02:55:51
Hello Buddy,
I tried this scripts
:: percent3.bat 2020-11-12 17:59:01.36
@echo off > OUT.TXT & setLocal enableDELAYedeXpansioN
copy my_csv.csv IN.TXT > nul
for /f "skip=1 tokens=1-6 delims= " %%a in (IN.TXT) do (
if %%f geq 80 echo.%%a %%b %%c %%d %%e %%f
) >> OUT.TXT

goto :eof

=====================================================================
There is data on IN.TXT file but OUTPUT.TXT shown zero sizes and has no input captured.
LOCATION,SERVER_POOL_PLATFORM,MANUFACTURER,RAW_CAPACITY_,TOTAL_CAPACITY_,TOTAL_CAPACITY_USED_TIB,Percentage
London,V000011STO,DELL,45,39.6504,23.9805,60
Germany,V000012STO,TOSHIBA,109.1611,71.9023,58.1084,81
France,V000013STO,NEC,1205,1081.4902,865.1104,80
France,V000011STO,HP,307.9199,269.3496,162.0703,60
France,V000012STO,HP,307.9199,269.3496,162.0703,60



Reply ↓  Report •

#20
November 15, 2020 at 03:02:17
Hi, thanks for the review. This seems to provide output of my_cvs.cvs file and has resulted ; folder created and excel file which has anything reported above 80 % >.
Can I try another file which having more line ? Is there anything to modify in that scripts ?

message edited by babu_hu


Reply ↓  Report •

#21
November 15, 2020 at 03:44:31
[1] It shouldn't do anything with OUTPUT.TXT; it's not part of the script.

[2] When you first posted your csv it had no commas; now it does. Get your story straight.

=====================

M2


Reply ↓  Report •

#22
November 15, 2020 at 03:56:40
Hi Buddy,
I just review, I guess we look onto this right ?
this was statement wrote while ago during the conversation
"the source file is saved as *,cvs" -> here there was commas

Let me know why I cant get the IN.TXT file copied from the source file my.cvs
** there was commas but the source file is my.csv.csv
** the correction/corrected to *.csv

I saved the file *.csv and excel for purpose of checks.
Let me know how should I proceed with the scripts.
The sources file is *.cvs
Sorry for typo errors.
Thanks

message edited by babu_hu


Reply ↓  Report •

#23
November 15, 2020 at 07:18:04
You lost me




=====================

M2


Reply ↓  Report •

#24
November 15, 2020 at 09:10:46
Hello M2,
Its like this,
The source file is *.csv and file name I used is "my_csv.csv". The scripts that we discussed and test out it produce zero entries on output.txt file but on IN.TXT it capture all including those 60 % and 70 % that is incorrect. Our goal is to capture all those percent that is showing above 80 %. That all need...

The IN.TXT output it shown as below.
LOCATION,SERVER_POOL_PLATFORM,MANUFACTURER,RAW_CAPACITY_,TOTAL_CAPACITY_,TOTAL_CAPACITY_USED_TIB,Percentage
London,V000011STO,DELL,45,39.6504,23.9805,60
Germany,V000012STO,TOSHIBA,109.1611,71.9023,58.1084,81
France,V000013STO,NEC,1205,1081.4902,865.1104,80
France,V000011STO,HP,307.9199,269.3496,162.0703,60
France,V000012STO,HP,307.9199,269.3496,162.0703,60

And the source file is from *.csv. I opened and saved my excel into *.csv before execute the script.
The scripts runs and return the result as above not as what we need

Thank you....

message edited by babu_hu


Reply ↓  Report •

#25
November 15, 2020 at 20:47:30
Hello nbrane,
Thanks for reviewing. I appreciate from bottom of my heart for your review as well explain the details on those excel vs *.csv,
On this scripts
-------------------------------------------------------------------------------------------------------------------------------------------
@echo off & setlocal
pushd C:\capacity\percent
:: this is temporary dump to hold the output, so no naming conflicts arise
md testwork
for /f "tokens=1* delims=:" %%a in ('findstr "8.$ 9.$ 100$" *.csv') do >> testwork\%%a echo %%b
Of course, only works if format is consistent in the CSV files. I don't think batch can work with .XLS format files, but excel can export them to CSV
--------------------------------------------------------------------------------------------------------------------------------------------
The above scripts works on those 5 line; the output on those percentage that is above 80 % onward is captured.

Can I apply the same for those line which is more than 5 ; can I use the same scripts ? if not possible what is right one to use ? Please assist on (*-*)

Hence when I viewing my excel file (multiple files to speak) notice around 5 to 10 excel file having "percentage" reports on "column N" meaning to says, this particular field it has all those percentage for the system. What will be the scripts for me to look up for this column N for input and specifically scan for those above 80 % onward export to file.(can we make use of the same scripts ? is there any modification needed ?).....

Please help _/\_

Thanks


Reply ↓  Report •

#26
November 16, 2020 at 18:17:17
Hello Babu: There is no limit to the number of lines in any given CSV. The limitations on this current script is that the percent is the last item on each line of the CSV (as per your example). The other requirement is that the percentage field does not contain anything other than integer elements - ie: no decimal point or anything else. You mention column "N", which would be column 14, or field #14 in a CSV export. If that is not the final field, (of the CSV export file), then my script will not work as written.
If you try the script and it doesn't work, please post a "randomized" sample of the raw CSV files (ie: hash the data so nothing sensitive is posted, but keep the format strict.)

Reply ↓  Report •

#27
November 17, 2020 at 08:03:29
Hello nbrane,
thank you for the details note. I shall get you the sample details...

Reply ↓  Report •

Ask Question