Solved removing columns from csv file under cmd

June 7, 2018 at 01:50:45
Specs: Windows 10
I found some posts on this subject, but unfortunately none of them helped me, something did not work. I would like to, from a csv file containing 145 columns separated by comma, only a few of them remain and the rest is erased. columns numbers I would like to keep 13,14,17,20,21,35,38

@echo off>output.csv & setlocal enabledelayedexpansion
cls

for /f "tokens=13,14,17,20,21,35,38 delims=," %%A in (input.csv) do (
    echo %%A,%%B,%%C,%%D,%%E,%%F,%%G>> output.csv
)

This script works almost perfect but in two last columns i received %F %G

message edited by diilmac


See More: removing columns from csv file under cmd

Report •

✔ Best Answer
June 11, 2018 at 17:30:55
@M2 & MMC: yeh, I always thought batch could go through two alphabets, and
any number of tokens. I apparently over-estimated Microsoft's diligence, especially
where batch support is concerned. Oh well...
This tries to get around the funky batch problem using 'shift':
@echo off & setlocal enabledelayedexpansion
SET n=13,14,17,20,21,35,38
for /f "tokens=*" %%a in (input.csv) do call :xx %%a
goto :eof

:xx
rem echo incoming: %*
set x=
set dlm=
for %%z in (%n%) do call :sh %%z %*
echo %x%
goto :eof

:sh
set c=%1
for /l %%y in (1 1 %c%) do shift
set x=%x%%dlm%%1
set dlm=,
goto :eof

And for anyone else who wants to mess with this, and wants to avoid paste-bin's
advertisements, here's a 10-line snip of the data that I used for testing:

target columns: 13,14,17,20,21,35,38
ruler:
    1           2         3  4       5         6   7      8  9  10 11 12      13        14  15           16 17  18 19     20           21     22 23 24 25 26 27 28 29 30 31    32   33 34      35   36  37     38
begin actual data:
"642432316","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-11-38-B01","","NOINVENTORY","","0","","","06/06/2018","03:56:13","","","","","","","","","","","NIGHT","","","1446799","0","","GONGAXMGE","","","RDT4633","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432320","Stock Check","MGEC","MISSG","10057399005","4781359","MISS01","LAWLESS HIGHWAIST SUPERSOFT SUPERSKINNY","","","","GOOD","M1-05-31-E02","24","","SUSPENSE","-1","","","06/06/2018","03:56:19","UnLocked","","","","","","","","","","NIGHT","","","1446646","205","100289","ALAHDAAMGE","","","RDT4609","","","","","","","","","","","","","","","","","CARTON","1U","","","","N","","","","","","","","","","","","","","","","","","","","","","","N","N","N","N","GCGI-M1L1B1","4500007089","","","","4500007089","05062018","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","XX/D00000001","XX/D00000001","","","","NB","","","","","","N","","","N","","","","","Y",
"642432322","Stock Check","MGEC","MISSG","10052511002","1923977","MISS01","LONG SLEEVE BELTED WRAP DRESS","","","","GOOD","MG-03-25-C09A","1","","SUSPENSE","0","","","06/06/2018","03:56:20","UnLocked","","","","","","","","","","NIGHT","","","1446929","9","","DIENGAMGE","","","RDT4635","","","","","","","","","","","","","","","","","CARTON","1U","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","4","","","","","","","","","","","","","","","","","","","","","","","","","","","","XX/D00000001","XX/D00000001","","","","NB","","","","","","N","","","N","","","","","Y",
"642432324","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C07","","NOINVENTORY","","0","","","06/06/2018","03:56:19","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432325","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C08","","NOINVENTORY","","0","","","06/06/2018","03:56:21","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432326","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C09","","NOINVENTORY","","0","","","06/06/2018","03:56:23","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432327","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D01","","NOINVENTORY","","0","","","06/06/2018","03:56:25","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432331","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D02","","NOINVENTORY","","0","","","06/06/2018","03:56:27","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432337","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D03","","NOINVENTORY","","0","","","06/06/2018","03:56:29","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432338","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D04","","NOINVENTORY","","0","","","06/06/2018","03:56:30","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",

message edited by nbrane



#1
June 8, 2018 at 19:52:24
That just means that %%f and %%g don't exist in your input-file (tokens 35, 38). Best if you can post a sample of a test-data input-file "detail lines" (not headers or breaks).

Report •

#2
Report •

#3
June 9, 2018 at 07:20:57
nbrane,

I also got high-centered with %%f & %%g.
The more I dug, the worse it got.
Supposedly you can do 52, a to z & A to Z.
Beats me.

This seems to work:


::======  script starts here  =================
@echo off > new.csv & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=* delims=" %%a in (my.csv) do (
set S="%%a"
set S=!S:,=","!
call :sub1 !S!
) >> new.csv
goto :eof

:sub1
set/a N=0 & set C=
for %%i in (%*) do (
set/a N+=1
  for %%T in (13 14 17 20 21 35 38) do (
    if !N! equ %%T set C=!C!, %%~i
  )
)
set C=!C:~3!
echo.!C!
goto :eof
::======  script ends here  =================

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

M2 Get custom script or take private lessons


Report •

Related Solutions

#4
June 9, 2018 at 08:13:27
At the beginning I would like to thank you for your interest in my problem,
I made a script in powershell, unfortunately it turned out that in the place where the script is to be used powershell is completely inaccessible, so I need a script in cmd.


ok, so with this sample data what i post script works good, ignoring the fact that in first column script cuts first sign :

rom Location
rom_loc_id
G-05-24-A09

unfortunately, with more rows, the script generates completely unexpected results

secondly, the whole idea of the script consists in relieving excel, data processing by the script recommended by you takes unexpectedly a lot of time.


Report •

#5
June 9, 2018 at 08:20:52
If you want to process a csv it might be a REAL GOOD IDEA to show the csv

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

M2 Get custom script or take private lessons


Report •

#6
June 9, 2018 at 08:53:08
Above I've added an example data.

Here it is another example of the 1500 lines.

https://drive.google.com/open?id=1h...


Report •

#7
June 9, 2018 at 09:14:06
That's not a csv

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

M2 Get custom script or take private lessons


Report •

#8
June 9, 2018 at 09:18:15
so, what is it?

Report •

#9
June 9, 2018 at 11:49:08
so, what is it?

Looks like a spreadsheet, possibly Excel or Google Sheets.

MIKE

http://www.skeptic.com/


Report •

#10
June 9, 2018 at 11:57:51
As a work around, you could just open the CSV file in Excel,
which understands what a csv file is and
will separate out the rows & columns for you.

Then simply delete the columns you don't want
and re-save the file as a csv file type.

If need be, a Macro could be created to automate the process.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#11
June 9, 2018 at 17:58:49
This is comma separated values file, for the purpose of providing you examples, I uploaded the file on google drive, which is why it may look like spreadsheet, Excel or Google Sheet.

I apologize for my ignorance, I am giving you an example in a more acceptable form for you.

https://pastebin.com/WqH5N6zK


Report •

#12
June 10, 2018 at 05:00:45
I'm not very good at Batch, but in hunting around found this page:

https://ss64.com/nt/for_f.html

If you look down the page under the heading TOKENS you will find:

"A single FOR /F command can never parse more than 31 tokens, to use more requires a workaround with multiple FOR commands."

That would seem to be the reason your batch script fails with the last two column numbers.

The above page does give a link to a workaround, but I'm not sure how to modify it for your problem.

Hopefully someone with more batch expertise will offer a suggestion.

MIKE

http://www.skeptic.com/


Report •

#13
June 11, 2018 at 17:30:55
✔ Best Answer
@M2 & MMC: yeh, I always thought batch could go through two alphabets, and
any number of tokens. I apparently over-estimated Microsoft's diligence, especially
where batch support is concerned. Oh well...
This tries to get around the funky batch problem using 'shift':
@echo off & setlocal enabledelayedexpansion
SET n=13,14,17,20,21,35,38
for /f "tokens=*" %%a in (input.csv) do call :xx %%a
goto :eof

:xx
rem echo incoming: %*
set x=
set dlm=
for %%z in (%n%) do call :sh %%z %*
echo %x%
goto :eof

:sh
set c=%1
for /l %%y in (1 1 %c%) do shift
set x=%x%%dlm%%1
set dlm=,
goto :eof

And for anyone else who wants to mess with this, and wants to avoid paste-bin's
advertisements, here's a 10-line snip of the data that I used for testing:

target columns: 13,14,17,20,21,35,38
ruler:
    1           2         3  4       5         6   7      8  9  10 11 12      13        14  15           16 17  18 19     20           21     22 23 24 25 26 27 28 29 30 31    32   33 34      35   36  37     38
begin actual data:
"642432316","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-11-38-B01","","NOINVENTORY","","0","","","06/06/2018","03:56:13","","","","","","","","","","","NIGHT","","","1446799","0","","GONGAXMGE","","","RDT4633","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432320","Stock Check","MGEC","MISSG","10057399005","4781359","MISS01","LAWLESS HIGHWAIST SUPERSOFT SUPERSKINNY","","","","GOOD","M1-05-31-E02","24","","SUSPENSE","-1","","","06/06/2018","03:56:19","UnLocked","","","","","","","","","","NIGHT","","","1446646","205","100289","ALAHDAAMGE","","","RDT4609","","","","","","","","","","","","","","","","","CARTON","1U","","","","N","","","","","","","","","","","","","","","","","","","","","","","N","N","N","N","GCGI-M1L1B1","4500007089","","","","4500007089","05062018","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","XX/D00000001","XX/D00000001","","","","NB","","","","","","N","","","N","","","","","Y",
"642432322","Stock Check","MGEC","MISSG","10052511002","1923977","MISS01","LONG SLEEVE BELTED WRAP DRESS","","","","GOOD","MG-03-25-C09A","1","","SUSPENSE","0","","","06/06/2018","03:56:20","UnLocked","","","","","","","","","","NIGHT","","","1446929","9","","DIENGAMGE","","","RDT4635","","","","","","","","","","","","","","","","","CARTON","1U","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","4","","","","","","","","","","","","","","","","","","","","","","","","","","","","XX/D00000001","XX/D00000001","","","","NB","","","","","","N","","","N","","","","","Y",
"642432324","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C07","","NOINVENTORY","","0","","","06/06/2018","03:56:19","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432325","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C08","","NOINVENTORY","","0","","","06/06/2018","03:56:21","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432326","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-C09","","NOINVENTORY","","0","","","06/06/2018","03:56:23","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432327","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D01","","NOINVENTORY","","0","","","06/06/2018","03:56:25","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432331","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D02","","NOINVENTORY","","0","","","06/06/2018","03:56:27","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432337","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D03","","NOINVENTORY","","0","","","06/06/2018","03:56:29","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",
"642432338","Stock Check","","","NOINVENTORY","","MISS01","","","","","","M2-17-15-D04","","NOINVENTORY","","0","","","06/06/2018","03:56:30","","","","","","","","","","","NIGHT","","","1446779","0","","SHIBKAYMGE","","","RDT4611","","","","","","","","","","","","","","","","","","","","","","N","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","N",

message edited by nbrane


Report •

#14
June 11, 2018 at 18:51:58
Just an FYI on the data file uploaded to Google by diilmac,
in the upper right corner there is a download arrow,
if you download the file, it is actually a CSV file type,
Apparently Google, in it's wisdom, is displaying the data in a spreadsheet format.

There is also one other solution that will work in a Batch
get the Unix CUT command, which has been ported to Windows and use that.
The whole script is one line:

cut -d , -f 13,14,17,20,21,35,38 input.csv >> output.csv

Using the Unix utilities makes life so much easier.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#15
June 11, 2018 at 21:53:09
@mmc: good call. sourceforge kicks butt. I assumed he couldn't use that, (why also I avoided vbscript with the "split" method). Yes, SO much easier! ;)

Report •

#16
June 11, 2018 at 23:44:09
Once again, I would like to thank you for your commitment, unfortunately all the time you try to avoid the problem of tossing me other methods. Once again, the whole procedure must be done in cmd.
Btw. even by the unix 'cut' method you will receive an undesirable effect.

So far the most helped me mmcconaghy, interestingly, more than people who give 'private lessons'.

https://www.dostips.com/forum/viewt...

I think the answer to my problem is at this address. I think that it is enough to adjust accordingly file ForTokens.bat.

Is anyone would be able to finally solve my problem?


Report •

#17
June 12, 2018 at 05:25:37
the whole procedure must be done in cmd.

The Unix CUT command, which has been ported to windows,
will run either from the command line or within a batch file.

Btw. even by the unix 'cut' method you will receive an undesirable effect.

What were the undesirable effects?
I ran the command in both Windows 7 and Window XP
and all seemed correct.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#18
June 12, 2018 at 15:12:08
*Downloads file*
*Opens file in Notepad++*
*Turns on end-of-line indicators*

Your file has UNIX style line endings. If you want to use batch, you need to make sure your file conforms to Windows' standards first.

How To Ask Questions The Smart Way


Report •

#19
June 12, 2018 at 15:24:48
Razor2.3
Thanks for the heads up,
I was unaware that the CUT command would make that specific change,
Will keep it in mind.

MIKE

http://www.skeptic.com/


Report •

#20
June 12, 2018 at 15:28:36
Depends on the implementation of CUT, since no version of Windows comes with the program bundled.

How To Ask Questions The Smart Way


Report •

#21
June 12, 2018 at 18:06:33
Depends on the implementation of CUT

And it is a relatively easy fix with several different ways to correct the line ending
I like this one:

cut -d , -f 13,14,17,20,21,35,38 test2.csv | tr -d '\15\32' >> output.csv

It's been a looong time since I had to convert Unix to DOS,

MIKE

http://www.skeptic.com/


Report •

#22
June 12, 2018 at 19:21:46
Well, gave it my shot and no feedback. Good luck!

Report •

#23
June 12, 2018 at 20:59:28
nbrane,
I ran your script, it's not the fastest, but it works.
Nice idea using "shift"

MIKE

http://www.skeptic.com/


Report •

#24
June 13, 2018 at 22:47:47
nbrane, script work fine, the only drawback is its speed, thank you very much.

Report •

#25
June 15, 2018 at 14:06:25
The slowness is because the shifts have to start from the beginning of the line for each target column. The only way I know to speed it up is to have the batchfile write another batch file with the series of shifts before opening and processing the csv file. It was a pain, and I was done with this, but I went ahead just to see if it would work. It's definitely faster, but whether it's better I'm not sure:

@echo off&setlocal
if exist shifter.bat goto :ok
set items=13,14,17,20,21,35,38
set k=1
::build the 'slave' batchfile
>shifter.bat echo set b=
for %%a in (%items%) do call :xx %%a
>>shifter.bat echo echo %%b%%
:ok
for /f "tokens=*" %%a in (input.csv) do (
call shifter %%a
)
goto :eof

:xx
set t=%k%
set z=%1
set k=%1
set /a z-=1
echo shifts: %t% 1 %z%
for /l %%a in (%t% 1 %z%) do >>shifter.bat echo shift
>>shifter.bat echo set b=%%b%% %%1


Report •

#26
June 16, 2018 at 13:30:28
try this:

::======  script starts here  =================
@echo off > new.csv & setLocal enableDELAYedeXpansioN

:main
for /f "tokens=* delims=" %%a in (BIG.csv) do (
call :sub1 %%a
) >> new.csv
goto :eof

:sub1
set/a N=0 & set C=
for %%i in (%*) do (
set/a N+=1
  for %%T in (13 14 17 20 21 35 38) do (
    if !N! equ %%T set C=!C!, %%i
  )
)
set C=!C:~2!
echo.!C!
goto :eof
::======  script ends here  =================

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

M2 Get custom script or take private lessons


Report •

#27
June 16, 2018 at 19:24:43
Yes, another novel approach. You could optimize speed further by jumping out after the last item has been reached (since the csv has 145 columns):
set max=38
...

for %%i in (%*) do (
set/a N+=1
  for %%T in (13 14 17 20 21 35 38) do (
       if !N! equ %%T set C=!C!, %%I
       if !N! gtr %max% goto :xit
  )
)
:xit
set C=!C:~2!
echo.!C!
goto :eof


At least, I think my interp. is correct. This really helped the speed, but only if short set of initial columns (38 in this case) are targeted.

M2's script got around my original attempts' problem of trying to run two for-loops basically in parallel.


Report •

#28
June 18, 2018 at 08:27:02
My #25 won't fly; try this:

::====== script starts here ===============
::
:: G9.bat 2018-06-18 20:56:04.36
@echo off > NEW.CSV & setLocal enableDELAYedeXpansioN

copy WQH5N6ZK.TXT myfile > nul

:main
for /f "tokens=* delims= " %%a in (myfile) do (
set S=%%a
set S=!S:~0,2000!
call :sub1 !S!
) >> NEW.CSV
goto :eof

:sub1
set/a N=0 & set C=
for %%i in (%*) do (
set/a N+=1
for %%T in (13 14 17 20 21 35 38) do (
if !N! equ %%T set C=!C!, %%i
)
)
if defined C set C=!C:~2!
echo.!C!
goto :eof
::====== script ends here =================

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

M2 Get custom script or take private lessons


Report •

Ask Question