Sort csv file by three columns using command?

Question

I read many of article in stackoverflow, but I still cannot do the sorting.

The case is like this :

I have a csv file with 9 columns without header like below :

D,0000001,2016/01/01 01:00,111,0.000,0000008,10.000,NET,Computer
B,0000002,2016/01/01 01:30,111,0.000,0000001,10.000,NET,Computer
C,0000003,2016/01/01 02:00,121,0.000,0000001,10.000,ORG,Computer
E,0000001,2016/01/01 02:00,121,0.000,0000003,10.000,ORG,Computer
E,0000001,2016/01/01 02:00,121,0.000,0000003,10.000,COM,Computer
C,0000001,2016/01/01 02:00,121,0.000,0000002,10.000,COM,Computer

The output I want is order by 2 (000001) , 6 (0000008),8 (NET) columns and output to new csv file like below:

C,0000001,2016/01/01 02:00,121,0.000,0000002,10.000,COM,Computer
E,0000001,2016/01/01 02:00,121,0.000,0000003,10.000,COM,Computer
E,0000001,2016/01/01 02:00,121,0.000,0000003,10.000,ORG,Computer
D,0000001,2016/01/01 01:00,111,0.000,0000008,10.000,NET,Computer
B,0000002,2016/01/01 01:30,111,0.000,0000001,10.000,NET,Computer
C,0000003,2016/01/01 02:00,121,0.000,0000001,10.000,ORG,Computer

I have tried some code like this but not work :

@echo off
setlocal

for /F "tokens=1-9 delims=," %%a in (input.csv) do set a[%%b,%%a,%%c,%%d,%%e,%%f,%%g,%%h,%%i] =%%
for /F "tokens=2-10 delims=[,]=" %%a in ('set a[') do echo %%b,%%a,%%c,%%d,%%e,%%f,%%g,%%h,%%i

Does anyone can help me? I am new in typing command script.

Mofi · Accepted Answer · 2016-03-31 05:40:42Z

3

Your approach was quite good. Just the order of field values was not correct set to get the wanted sort order for output.

@echo off
if not exist input.csv goto :EOF
setlocal
for /F "tokens=1-9 delims=," %%a in (input.csv) do set "a[%%b,%%f,%%h,%%a,%%c,%%d,%%e,%%g,%%i]=%%"
del input.csv
for /F "tokens=2-10 delims=[,]=" %%a in ('set a[') do echo %%d,%%a,%%e,%%f,%%g,%%b,%%h,%%c,%%i>>input.csv
endlocal

The environment variables are created here with %%b,%%f,%%h,%%a,%%c,%%d,%%e,%%g,%%i instead of %%b,%%a,%%c,%%d,%%e,%%f,%%g,%%h,%%i which of course requires also %%d,%%a,%%e,%%f,%%g,%%b,%%h,%%c,%%i instead of %%b,%%a,%%c,%%d,%%e,%%f,%%g,%%h,%%i on output.

edited Mar 31, 2016 at 5:40

answered Mar 31, 2016 at 5:21

Mofi

49.9k19 gold badges91 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kason Over a year ago

Thanks a lot. I have tried to do myself. It is as same as yours. Thanks

Aacini · Accepted Answer · 2016-03-31 05:32:19Z

2

You almost have it:

@echo off
setlocal

for /F "tokens=1-9 delims=," %%a in (input.csv) do set "a[%%b%%f%%h]=%%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i"
for /F "tokens=2 delims==" %%a in ('set a[') do echo %%a

However, the result you show is not ordered as you described in the question. If you want an inverse order by %%f field, you may use this:

@echo off
setlocal EnableDelayedExpansion
for /F "tokens=1-9 delims=," %%a in (input.csv) do (
   set /A "invF=100000000-1%%f"
   set "a[%%b!invF!%%h]=%%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i"
)
for /F "tokens=2 delims==" %%a in ('set a[') do echo %%a

answered Mar 31, 2016 at 5:32

Aacini

67.7k12 gold badges74 silver badges114 bronze badges

Collectives™ on Stack Overflow

Sort csv file by three columns using command?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related