How to create a .bat file that will delete mutli columns?

September 11, 2016 at 14:52:50
Specs: Windows 7
I have a folder containing 100+ .csv files, I want to delete multiple specific columns(ie same in every .csv) from every file and save it with same name and format as before. Manually I can do this by opening every file in excel; however this can take a looong time to accomplish.

I would like to be able to place an .bat file within this folder and run the .bat script so that it automatically deletes the identified columns for all of the .csv files within the mentioned folder.

Can any one help in creating such code...?

Specifically:
Can you show me how to create a similar .bat file that will delete the content(if any) of any columns that proceed column M...?

Please advise.


See More: How to create a .bat file that will delete mutli columns?

Report •

#1
September 11, 2016 at 22:12:29
::====== script starts here ===============
::
:: Xcolumns.bat 2016-09-12 11:59:41.42
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
call :sub1 %%a
move NEWFILE %%~Na-NEW.CSV
)
goto :eof

:sub1
for /f "tokens=1-3,5-8 delims=," %%i in ('type %1') do (
echo.%%i, %%j, %%k, %%l, %%m, %%n, %%o
) >> NEWFILE
goto :eof
::====== script ends here =================

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

M2 Get custom script or take private lessons


Report •

#2
September 12, 2016 at 10:37:58
Hi Mechanix2Go ,

Thanks for your support, im running into a slit problem. I re-wrote the code to suit my needs however here is the issue im running into:
- i need columns a-m (ie. tokens=1-13)
- columns n-v has content that i donot need, and need to be deleted
- therefore it starts with a-m starting with "%%w in" etc
- im noticed that i cannot use aa, ab, ac etc. and that where i pause,

i very new to .bat and ive tried multiple times and based on the explanation above; below you will see the code that i re-write to the best of by current knowledge.

please advise.....

====================================================
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN
:main
pushd "C:\My_Folder"
for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
call :sub1 %%a
move NEWFILE %%~Na-NEW.CSV
)
goto :eof
:sub1
for /f "tokens=1-13 delims=," %%w in ('type %1') do (
echo.%%w,%%v, %%w, %%x, %%y, %%z, %%v, %%w, %%x, %%y, %%z, %%v, %%w
) >> NEWFILE
goto :eof
====================================================

message edited by guruleadsource


Report •

#3
September 12, 2016 at 11:04:56
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN
:main
pushd "C:\My_Folder"
for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
call :sub1 %%a
move NEWFILE %%~Na-NEW.CSV
)
goto :eof
:sub1
for /f "tokens=1-13 delims=," %%w in ('type %1') do (
echo.%%w, %%x, %%y, %%z, %%aa, %%ab, %%ac, %%ad, %%ae, %%af, %%ag, %%ah, %%ai
) >> NEWFILE
goto :eof

message edited by guruleadsource


Report •

Related Solutions

#4
September 12, 2016 at 22:39:05
Make :sub1

:sub1
for /f "tokens=1-13 delims=," %%i in ('type %1') do (
echo.%%i, %%j, %%k, %%l, %%m, %%n, %%o, %%p, %%q, %%r, %%s, %%t, %%u
) >> NEWFILE
goto :eof

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

M2 Get custom script or take private lessons


Report •

#5
September 12, 2016 at 23:32:07
is it just me, or is there a conflict / discrepancy here:
"Can you show me how to create a similar .bat file that will delete the content(if any) of any columns that proceed column M...?"

"- i need columns a-m (ie. tokens=1-13)
- columns n-v has content that i donot need, and need to be deleted
- therefore it starts with a-m starting with "%%w in" etc"

or is it case sensitive, as "m" is not same as "M"?

Best shot is if you can post a demo of two CSV lines of dummy data, "before" and "after" processing. When you say "delete" I assume you mean "cleared to null" and not actually deleted from CSV matrix, but I may be wrong. Cleared column two: One,Two,Three will be: One,,Three. Deleted column two will be: One,Three. All depends on your specific requirements.


Report •

#6
September 13, 2016 at 04:17:45
:: before:
:: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,
:: after:
:: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
::====== script starts here ===============
::
:: Xcolumns.bat 2016-09-12 11:59:41.42
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=* delims= " %%a in ('dir/b *.csv') do (
call :sub1 %%a
move NEWFILE %%~Na-NEW.CSV
)
goto :eof

:sub1
for /f "tokens=1-13 delims=," %%i in ('type %1') do (
echo.%%i, %%j, %%k, %%l, %%m, %%n, %%o, %%p, %%q, %%r, %%s, %%t, %%u
) >> NEWFILE
goto :eof
::====== script ends here =================

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

M2 Get custom script or take private lessons


Report •

#7
September 13, 2016 at 08:09:48
- That is completely my mistake, I meant 'come after' M as in the number 13 column.
- Clear to null is correct.


Please view the google doc drive link below. it has an example file. All I need to keep is the data on the spreadsheet from Columns A through Columns M (as is), everything esle after can be cleared to null

Link: https://docs.google.com/spreadsheet...


Report •

#8
September 13, 2016 at 08:17:29
Regarding the last code you posted. I also tried that yesterday aswell, one issue that I saw was that if any of the cells from columns A-M were empty it was filled with the next cell to the right that had content etc etc. So in other words the cells were being shifted in the the wrong position if that row contained an empty cell.

The other issue that arose was that columns N-V had unneeded content and there were also being push over in the the Columns with empty content within Columns A-M.

message edited by guruleadsource


Report •

#9
September 13, 2016 at 09:52:06
Obviously, I'm not smart enough to do this type of work.

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

M2 Get custom script or take private lessons


Report •

#10
September 13, 2016 at 10:21:32
lol, i just wanted to state the obvious...

im the newbie and i couldnt get past the mention issue above. I sure that you can assit. i've seen some of your other coding. =)

thanks in advance..

message edited by guruleadsource


Report •

#11
September 13, 2016 at 18:21:40
Ha! My track record deteriorates with each post... And, I ASSUmed" that "proceed" meant "precede", (typo or language issue) but technically guru was correct. My mistake there. M2 nailed it except for a bunch of empty fields to append:
:: before:
:: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22,
:: after:
:: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,,,,,,,,,

@echo off > NEWFILE & setLocal enableDELAYedeXpansioN
:main
for /f "tokens=* delims=" %%z in ('dir/b *.csv') do (
call :sub1 "%%z"
move NEWFILE %%~Na-NEW.CSV
)
goto :eof


:sub1
rem "names have been change to protect the innocent" or in this case match the excel columns
for /f "tokens=1-13 delims=," %%a in ('type %1') do (
echo.%%a, %%b, %%c, %%d, %%e, %%f, %%g, %%h, %%i, %%j, %%k, %%L, %%m,,,,,,,,,
) >> NEWFILE


Report •

#12
September 14, 2016 at 00:39:03
We need to see a few lines of the problem CSV.

That's what we shoulda did 10 replies ago. Keeping in mind shoulda don't help.

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

M2 Get custom script or take private lessons


Report •

Ask Question