CODEWITHSUNDEEP
windowssortingcsvbatch-filecommand-line
Kason
Kason

Reputation: 797

Sort csv file by three columns using command?

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.

Upvotes: 3

Views: 755

Answers (2)

Mofi
Mofi

Reputation: 49097

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.

Upvotes: 3

Aacini
Aacini

Reputation: 67216

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

Upvotes: 2

Related Questions