I have a csv file in the following format:
COLUMN1,COLUMN2,COLUMN3
2,1,0
1,0,0
3,2,0
1,4,1
4,3,0
1,1,4
5,6,0
6,5,0
I want to sort it by 1st and 3rd columns using a bat file. The result I want to get is:
COLUMN1,COLUMN2,COLUMN3
1,0,0
1,4,1
1,1,4
2,1,0
3,2,0
4,3,0
5,6,0
6,5,0
In unix this can be done using sort -n -k1,1 -k3,3 xx.csv > sorted.csv. I tried to do sort /+1 /+3 xx.csv > sorted.csv in windows, but it didn't work... Thanks for your help
Perhaps this is the simplest and fastest way to achieve this:
@echo off
setlocal
set /P "header=" < input.txt
echo %header%
for /F "skip=1 tokens=1-3 delims=," %%a in (input.txt) do set a[%%a,%%c]=%%b
for /F "tokens=2-4 delims=[,]=" %%a in ('set a[') do echo %%a,%%c,%%b
Output:
COLUMN1,COLUMN2,COLUMN3
1,0,0
1,4,1
1,1,4
2,1,0
3,2,0
4,3,0
5,6,0
6,5,0
Previous program assume that there are not records with the same COLUMN1 and COLUMN3, and that the contents of the columns are just one digit. These limitations may be fixed, if needed.
There is probably a more elegant and robust solution, but this is my 5 minute solution.
@echo off
> sorted.csv set /p "=" <nul
> unsorted.tmp set /p "=" <nul
for /f "delims=" %%A in (unsorted.csv) do for /f "tokens=1,3 delims=," %%B in ("%%~A") do >>unsorted.tmp echo %%B,%%C,%%A
sort unsorted.tmp > sorted.tmp
for /f "tokens=2,* delims=," %%A in (sorted.tmp) do >>sorted.csv echo %%B
del unsorted.tmp
del sorted.tmp
This takes the first and third columns, prepends them to the beginning of the line, sorts the file, and then removes the added columns. Note that the sort command weights numbers before letters.
Output
1,0,0
1,4,1
1,1,4
2,1,0
3,2,0
4,3,0
5,6,0
6,5,0
COLUMN1,COLUMN2,COLUMN3
@ECHO OFF
SETLOCAL
SET "outfile=sorted.csv"
:: make a tempfile
:maketemp
SET "tempfile=%temp%\%random%"
IF EXIST "%tempfile%*" (GOTO maketemp) ELSE (ECHO.>"%tempfile%a")
SET "header="
(
FOR /f "delims=" %%a IN (xx.csv) DO (
IF DEFINED header (
CALL :proc %%a
) ELSE (SET header=Y&>%outfile% ECHO(%%a)
)
)>"%tempfile%a"
FOR /f "tokens=2delims=#" %%a IN ('sort ^<"%tempfile%a"') DO >>%outfile% ECHO(%%a
del "%tempfile%a"
GOTO :EOF
:proc
SET /a c1=%1+1000000000
SET /a c3=%3+1000000000
ECHO %c1% %c3%#%1,%2,%3
GOTO :eof
I've assumed that other than the header, your data is all-numeric and 3 columns.
You don't specify whether the numbers involved would exceed 9 or whether if they do, you want the sort-sequence in magnitude of the columns or not.
I've assumed that the three columns are to be sorted in order of magnitude, column 1 then column 3, that the data is all-numeric and there are no negative numbers. Also that the numbers involved are all less than 1000000000.
