Welcome guest. Before posting on our computer help forum, you must register. Click here it's easy and free.

Author Topic: Sort a Phone List by Last Name.  (Read 7308 times)

0 Members and 1 Guest are viewing this topic.

Geek-9pm

    Topic Starter

    Mastermind
  • Geek After Dark
  • Thanked: 1026
    • Gekk9pm bnlog
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 10
Sort a Phone List by Last Name.
« on: January 03, 2018, 05:28:31 PM »
This should be easy. Or maybe not.
I have a phone list in made in notepad, no commas, items are seperated only by spaces. I want the list to be sorted by last name.
Here is a sample of what the list might be:

Jerry Johnson    707 555 1234
Bob Anderwes    415 555 1234
Patty Zonk         208 555 1234
Rene Kellr          203 555 1234

Now I could try this:
Quote
sort <old.txt >new.txt
But it sorts on the first name. I want it to sor on the last name.
I tried this
soer /?
and did not find an ansawer.
So, how do you do it without resorting to VBScript or something like that.  :)
 




Squashman



    Specialist
  • Thanked: 134
  • Experience: Experienced
  • OS: Other
Re: Sort a Phone List by Last Name.
« Reply #1 on: January 04, 2018, 11:13:10 AM »
You could do it in pure batch but you would have to create some temporary files to do so.

Easy enough to do with a one liner calling out to powershell.
Code: [Select]
powershell "get-content file.txt |sort-object {$_.split()[1]}"
Code: [Select]
C:\Powerhsell\Sort>type file.txt
Jerry Johnson 707 555 1234
Bob Anderwes 415 555 1234
Patty Zonk 208 555 1234
Rene Kellr 203 555 1234

C:\Powerhsell\Sort>powershell "get-content file.txt |sort-object {$_.split()[1]}">filenew.txt

C:\Powerhsell\Sort>type filenew.txt
Bob Anderwes 415 555 1234
Jerry Johnson 707 555 1234
Rene Kellr 203 555 1234
Patty Zonk 208 555 1234

C:\Powerhsell\Sort>

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #2 on: January 04, 2018, 11:30:05 AM »
Don't use the anaemic Windows sort, use a Windows port of the Linux sort utility. The one in the GNU Core Utils is as good as any other. You can get the Core Utils as a zip here

http://gnuwin32.sourceforge.net/downlinks/coreutils-bin-zip.php

I extracted sort.exe from the zip and renamed it gnusort.exe to avoid confusion. Use gnusort (or whatever you call it) with these parameters:

-t "char" the column separator character(s), in this case space(s).
-kX,Y where X is the first token (column) to sort on and Y is the last, in your case these are 2 and 5

GNU Core Utils is a big bag of goodies! All the ones you want like awk, sed, etc. I have a folder for them on my PATH, suitably renamed where necessary. I use date.exe (now gnudate.exe) lot. They mostly have command line help where you type

command --help

C:\Batch>type names.txt
Jerry Johnson 707 555 1234
Bob Anderwes 415 555 1234
Patty Zonk 208 555 1234
Rene Kellr 203 555 1234
Mike Smith 201 122 1234
Joe Adams 212 122 1234
Bill Briggs 334 122 1234
Ann Delaney 456 122 1234
Mary Ellis 789 122 1234
Frank Fogg 997 122 1234

C:\Batch>type names.txt | gnusort -t " " -k2,5 > sorted.names.txt

C:\Batch>type sorted.names.txt
Joe Adams 212 122 1234
Bob Anderwes 415 555 1234
Bill Briggs 334 122 1234
Ann Delaney 456 122 1234
Mary Ellis 789 122 1234
Frank Fogg 997 122 1234
Jerry Johnson 707 555 1234
Rene Kellr 203 555 1234
Mike Smith 201 122 1234
Patty Zonk 208 555 1234
« Last Edit: January 04, 2018, 11:41:35 AM by Salmon Trout »

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #3 on: January 04, 2018, 11:35:02 AM »
Squashman, I see you have the same problem with Powerhsell that I have with spreadhseets.

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #4 on: January 04, 2018, 01:14:46 PM »
You can do it purely in batch... just read each line, switch tokens 2 and 3 so each line starts with the surname, write the line to a temp file, sort the temp file, then switch the tokens back again so each line starts with the person's first name.

@echo off
if exist temp*.txt del temp*.txt
if exist names.sorted.txt del names.sorted.txt
for /f "tokens=1-5" %%A in (names.txt) do echo %%B %%A %%C %%D %%E >> temp1.txt
sort temp1.txt > temp2.txt
for /f "tokens=1-5" %%A in (temp2.txt) do echo %%B %%A %%C %%D %%E >> names.sorted.txt
del temp1.txt
del temp2.txt


Squashman



    Specialist
  • Thanked: 134
  • Experience: Experienced
  • OS: Other
Re: Sort a Phone List by Last Name.
« Reply #5 on: January 04, 2018, 04:18:59 PM »
You could also use Dave Benham's JSORT.
Code: [Select]
C:\BatchFiles\JSORT>jsort file.txt /t 2 /d " "
Bob Anderwes    415 555 1234
Jerry Johnson    707 555 1234
Rene Kellr          203 555 1234
Patty Zonk         208 555 1234

Geek-9pm

    Topic Starter

    Mastermind
  • Geek After Dark
  • Thanked: 1026
    • Gekk9pm bnlog
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #6 on: January 04, 2018, 04:23:38 PM »
Wow! You guys are great. I did recall taht the GNU thing works very good. I did not know there was a portof the GNU sort to woindows.Good information!  :)

The swap of the tokens and a temp file wasvery clever.  ;D

Thanks to both of yhou.I will bookmark this thread.   8)


BTW: CH has a nice tutorial on h ow to use the sort toool in Linux.

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #7 on: January 06, 2018, 03:46:15 AM »
Of course, if you have up to date 64 bit Windows 10 you can use the Windows Subsystem for Linux and you have all the (real) Linux tools available, and they can see your Windows files and folders:

salmon@fishpool:~$ cat /mnt/c/Batch/names.txt | sort -t ' ' -k2,5
Joe Adams 212 122 1234
Bob Anderwes 415 555 1234
Bill Briggs 334 122 1234
Ann Delaney 456 122 1234
Mary Ellis 789 122 1234
Frank Fogg 997 122 1234
Jerry Johnson 707 555 1234
Rene Kellr 203 555 1234
Mike Smith 201 122 1234
Patty Zonk 208 555 1234


and in that case you can also do this from Windows command line:

C:\>bash -c "cat /mnt/c/Batch/names.txt | sort -t ' ' -k2,5"
Joe Adams 212 122 1234
Bob Anderwes 415 555 1234
Bill Briggs 334 122 1234
Ann Delaney 456 122 1234
Mary Ellis 789 122 1234
Frank Fogg 997 122 1234
Jerry Johnson 707 555 1234
Rene Kellr 203 555 1234
Mike Smith 201 122 1234
Patty Zonk 208 555 1234

C:\>


« Last Edit: January 06, 2018, 04:39:52 AM by Salmon Trout »

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #8 on: January 07, 2018, 08:33:04 AM »
I did some timing experiments. I assembled from Web sources, a list of 88,800 names and added (dummy) phone numbers.

Chong Zywiec 717 123 456789
Peter Zyskowski 717 123 456789
Jane Zysk 717 123 456789
(...)
Abby Aadland 717 123 456789
Paul Aaby 717 123 456789
Sylvester Aaberg 717 123 456789

I made each method sort the file into ascending alphabetical order of surname:

88,800 names
input:  sorted Z-A
output: sorted A-Z
GNU sort 0.40 sec
Benham jsort 6.13 sec
Batch method 664.99 sec (11 min 4.99 sec)

Input & output files on a Samsung Evo 840 SSD, Windows 10 Pro 64 bit; CPU i7 4790; 16 GB RAM

If I was doing this for real (and I do at work) I would use Microsoft Excel.


Geek-9pm

    Topic Starter

    Mastermind
  • Geek After Dark
  • Thanked: 1026
    • Gekk9pm bnlog
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #9 on: January 07, 2018, 08:49:55 AM »
WoW!
But what is the GNU sort?

Also , I just got a an old Gateway with  Windows 10 Pro 64 bit.
So, how do I start bash in Windows?

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #10 on: January 07, 2018, 09:00:21 AM »
But what is the GNU sort?
I told you above. It's in the GNU Core Utilities.

Quote
I just got a an old Gateway with  Windows 10 Pro 64 bit.
So, how do I start bash in Windows?

If you want to go that way, see here

https://docs.microsoft.com/en-us/windows/wsl/install-win10

Geek-9pm

    Topic Starter

    Mastermind
  • Geek After Dark
  • Thanked: 1026
    • Gekk9pm bnlog
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #11 on: January 07, 2018, 01:35:11 PM »
Salmon Trout,
I downloaded the ZIP file and then did the extract and I got this folder:
E:\Kohn-II\Downloads\coreutils-5.3.0-bin
When Iopen thefolder, thereare more folders, but in install or setup thing.
There is a readme file, but I am not sure of what it wants me todo.

I guess tahtI am missing a DLL file taht ought to be in Windows XP.

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #12 on: January 07, 2018, 02:12:25 PM »
You are not missing anything. That is how the Core Utilities are distributed. There is no installer or setup.exe or stuff like that. They are command-line ports, for Windows, of common Linux/Unix utilities. Because they are Windows executable programs, they have an .exe extension. Having unzipped the downloaded Zip archive to a folder, in that folder you should see five new folders called bin, contrib, man, manifest, and share. The one you are mainly interested in is the bin folder because that is where the utility .exe files are located. I hope you can see my screen shot below which shows the first few in their folder on my system. There are 98 altogether.

So you need to make sure you know the full path to the bin folder, it may be E:\Kohn-II\Downloads\coreutils-5.3.0-bin\bin - you should determine this. You can either move them to another folder of your choosing or leave them where they are. To use them in your scripts or at the Windows Command prompt, you can use the full path and utility name which might look like this

E:\Kohn-II\Downloads\coreutils-5.3.0-bin\bin\date.exe

 (the .exe is, of course, optional in most cases)

You should see something like this:

Sun Jan  7 21:00:43 GMT Standard Time 2018

You can use the customary --help option to get guidance for most if not all. This is like the standard Windows /? option (e.g. dir /?).

The other thing you can do is put them in a folder on your PATH. If you do not know what this means, let me know and I will clarify. A problem with this that can arise is that some of them (sort, date, dir, sleep, for example) have the same names as existing Windows utilities or internal commands, so you could get clashes where you get the Windows version when you wanted the GNU version, or vice versa. I got around that by renaming the GNU ones that clashed with 'gnu' at the start, e.g. gnudate.exe, gnusort.exe, gnudir.exe etc.

Or you can copy an .exe that you want to use, into a folder you are working in, and call it directly.

The man folder (man means "manual") has folders with help text files for each utility, and will repay examination.

Also, the overwhelming majority of Web guidance and discussions about Linux/Unix utilities will apply equally to the GNU versions for Windows.

If anything is not clear, please come back and I will try to help.

Have fun!



Squashman



    Specialist
  • Thanked: 134
  • Experience: Experienced
  • OS: Other
Re: Sort a Phone List by Last Name.
« Reply #13 on: January 07, 2018, 03:48:34 PM »

I made each method sort the file into ascending alphabetical order of surname:

88,800 names
input:  sorted Z-A
output: sorted A-Z
GNU sort 0.40 sec
Benham jsort 6.13 sec
Batch method 664.99 sec (11 min 4.99 sec)

Input & output files on a Samsung Evo 840 SSD, Windows 10 Pro 64 bit; CPU i7 4790; 16 GB RAM

If I was doing this for real (and I do at work) I would use Microsoft Excel.
Yes. Any compiled executable is normally going to be faster.
Can you do a quick test with the Powershell code I posted.  I think it will be around the same time as Dave's Jsort.

Geek-9pm

    Topic Starter

    Mastermind
  • Geek After Dark
  • Thanked: 1026
    • Gekk9pm bnlog
  • Certifications: List
  • Computer: Specs
  • Experience: Expert
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #14 on: January 07, 2018, 06:51:08 PM »
Salmon Trout,
OK, got it to work.
I have a hard time with long path names. I found the Path  and added this:
;E:\BIN
(I use taht folde rfor a few things I have made  earolier.)

Then I looked in tghe core 5 folder and found sortg.exe
and copied itto the E:\BIN  folder. Ialready have sed.exe in taht folder .
 
I had no idea so man GNU files had been ported to Windows. Really, there are too many. I will put only a seslect few in my E:\BIN folder . When everI have a need for something, I will just  putitinto that folder.

Thanks again.

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #15 on: January 08, 2018, 12:34:00 PM »
Can you do a quick test with the Powershell code I posted.  I think it will be around the same time as Dave's Jsort.

88,800 names
input:  sorted Z-A
output: sorted A-Z
GNU sort       0.40 sec
Benham jsort   6.13 sec
Powershell     8.49 sec
Batch method 664.99 sec (11 min 4.99 sec)




Sidewinder



    Guru

    Thanked: 139
  • Experience: Familiar
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #16 on: January 08, 2018, 02:18:17 PM »
Technically this is a Powershell one-iner, but I broke it down to 4 physical lines for readability. If you type this at the Powershell command prompt, just keep typing when the line wraps.

Code: [Select]
(Get-Content .\phone.txt) -replace '(.*?\d{3})\s(.*?)', '$1-$2' |
  ConvertFrom-Csv -Delimiter ' ' -Header First,Last,Phone |
  Sort-Object Last |
  Format-Table * -AutoSize

You can change the path and file name in the first line.

 8)
The true sign of intelligence is not knowledge but imagination.

-- Albert Einstein

Sidewinder



    Guru

    Thanked: 139
  • Experience: Familiar
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #17 on: January 08, 2018, 02:23:18 PM »
Technically this is a Powershell one-liner. I broke it into 4 physical lines for readability. If you do type this in a Powershell window, type all 4 lines as a single line and just keep typing when the line wraps. The interpreter will understand.

Code: [Select]
(Get-Content .\phone.txt) -replace '(.*?\d{3})\s(.*?)', '$1-$2' |
  ConvertFrom-Csv -Delimiter ' ' -Header First,Last,Phone |
  Sort-Object Last |
  Format-Table * -AutoSize

The path and file name can be changed as needed.

 8)
The true sign of intelligence is not knowledge but imagination.

-- Albert Einstein

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #18 on: January 08, 2018, 03:33:54 PM »
It did it, but it echoed the sorted output to the console.


Sidewinder



    Guru

    Thanked: 139
  • Experience: Familiar
  • OS: Windows 10
Re: Sort a Phone List by Last Name.
« Reply #19 on: January 09, 2018, 06:07:18 AM »
Powershell has cmdlets for outputting to a file, however in this case redirection might be the simpler way to go.

Code: [Select]
(Get-Content .\Phone.txt) -replace '(.*?\d{3})\s(.*?)', '$1-$2' |
  ConvertFrom-Csv -Delimiter ' ' -Header First,Last,Phone |
  Sort-Object Last |
  Format-Table * -AutoSize -HideTableHeaders > .\Phone.new

If the preference is to have the headers in the output file, remove the -HideTableHeaders parameter from the Format-Table cmdlet.

 8)
The true sign of intelligence is not knowledge but imagination.

-- Albert Einstein

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #20 on: January 09, 2018, 12:44:21 PM »
Added a timer:

Code: [Select]
$t = Measure-Command {
(Get-Content .\Notabs_names-rev-sorted.names.txt) -replace '(.*?\d{3})\s(.*?)', '$1-$2' |
  ConvertFrom-Csv -Delimiter ' ' -Header First,Last,Phone |
  Sort-Object Last |
  Format-Table * -AutoSize > out.txt
  }
echo "Time: $t"

Result:

Code: [Select]
Time: 00:00:13.2834694
The script is clearly doing more work than just sorting: for example it is justifying the columns (input file: 2.6 MB output file: 7.8 MB)


Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #21 on: January 10, 2018, 02:28:36 PM »
Python

88,800 names
0.138 seconds!

Code: [Select]
python sortfile.py > sorted.txt
2018-01-10 21:24:18.494000
2018-01-10 21:24:18.632000

Code: [Select]
from __future__ import print_function
from datetime import datetime
import csv
import operator

tstart = datetime.now()
reader = csv.reader(open("Notabs_names-rev-sorted.names.txt"), delimiter=" ")

for line in sorted(reader, key=operator.itemgetter(1)):
     print(" " . join(line))

tend = datetime.now()
print (tstart)
print (tend)

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #22 on: January 11, 2018, 10:18:17 AM »
Better Python (27)

Code: [Select]
from __future__ import print_function
from datetime import datetime
import csv
import operator
tstart = datetime.now()
f = open('output.txt', 'w')
reader = csv.reader(open("input.txt"), delimiter=" ")
for line in sorted(reader, key=operator.itemgetter(1)):
    print(" " . join(line), file=f)
f.close()
tend = datetime.now()
print ("Elapsed", tend - tstart, "seconds")

88,800 names, 5 runs:

Code: [Select]
Elapsed 0:00:00.172000 seconds
Elapsed 0:00:00.156000 seconds
Elapsed 0:00:00.172000 seconds
Elapsed 0:00:00.157000 seconds
Elapsed 0:00:00.172000 seconds


Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #23 on: January 11, 2018, 10:54:50 AM »
Here are 88,800 names sorted randomly:



[attachment deleted by admin to conserve space]
« Last Edit: January 11, 2018, 11:14:31 AM by Salmon Trout »

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #24 on: January 11, 2018, 11:13:20 AM »
Here's the other, 88,800 names sorted alphabetically by column (2) in reverse order. I notice that the sorted file compresses better.




[attachment deleted by admin to conserve space]

Salmon Trout

  • Guest
Re: Sort a Phone List by Last Name.
« Reply #25 on: January 11, 2018, 12:00:36 PM »
It's quicker to sort the reverse-sorted file than the randomly sorted file.

Code: [Select]
reverse 0:00:00.156000 seconds
random  0:00:00.265000 seconds