Sort CSV file numericaly without header

Scripting Forums AppleScript | Mac OS X
#1

Hi there

I’d searched now a lot, but I coudn’t find a working solution for this problem:

I have a CSV file that looks like this:

[format]Campaign, Channel, Title
ABCD,Channel1, 9
ABCD,Channel2, 2
ABCD,Channel3, 1
ABCD,Channel4, 10
ABCD,Channel5, 7[/format]

I’d like to sort the file by ‘Title’ with apple-script so it should look like this:

[format]Campaign, Channel, Title
ABCD,Channel3, 1
ABCD,Channel2, 2
ABCD,Channel5, 7
ABCD,Channel1, 9
ABCD,Channel4, 10[/format]

It seems that ignore the header is not that easy. So I tried first the sorting thing.
What I have so far:


set filePath to quoted form of POSIX path of "/Users/user/Desktop/test.csv"
do shell script "sort -k3 -t ',' -u -r " & filePath & " -o  " & filePath

But this gives me this result:

[format]Campaign, Channel, Title
ABCD,Channel1, 9
ABCD,Channel5, 7
ABCD,Channel2, 2
ABCD,Channel3, 1
ABCD,Channel4, 10[/format]

Any Ideas what am I doing wrong, or is there another way to solve this?

Thank you very much in advance for your help.

Regards
Marth

#2

Hi Marth.

I think that -r option should be -n.

#3

Marth’s script with Nigel’s edit worked for me and it’s reasonably quick. When doing a numeric sort, if the sort column contains letters rather than a number, that line is included first in the sort. So, there shouldn’t be any issue with the header being moved. FWIW, the use of POSIX path in the first line of the script is not really necessary because filePath already has a POSIX path.

#5

Good catch!

#6

Fredrik71. Doesn’t the 3 in the following line of your script has to be a 2?

repeat with i from length of array to 3 by -1 --> go backwards

Your script appears to work as written but that may be a result of the order of items in the unsorted list. If you change the unsorted list as follows this can be seen:

set k to {{"ABCD", "Channel1", "9"}, {"ABCD", "Channel5", "7"}, {"ABCD", "Channel4", "10"}, {"ABCD", "Channel2", "2"}, {"ABCD", "Channel3", "1"}}

BTW, I’m continuing my study of sorting routines and that’s why I happened to notice this. It may appear otherwise, but I don’t intentionally try to find issues with scripts of other forum members, which is a bad practice.

#8

Fredrik71. I thought I might be missing something and I’m glad to find that’s not the case.

I occasionally need to sort 6 or 8 text items in a list, which will then be used, for example, in a choose-from-list dialog, and I wanted a sort routine that’s 1) written in basic AppleScript; 2) is fast enough; and 3) is relatively compact. That’s why I found your script of such interest. Just as a matter of personal preference, I deleted the tell statement, and, in testing with Script Geek, the first-run time was 0.4 milliseconds, which is certainly fast enough. Thanks for the post.

set unsortedList to {"Text Edit", "Script Editor", "Activity Monitor", "Safari", "FSNotes", "Script Geek", "Epson Scan", "Color Sync Utility", "FastScripts"}

set sortedList to sortList(unsortedList)

on sortList(u)
	repeat with i from (count u) to 2 by -1
		repeat with j from 1 to (i - 1)
			if item j of u > item (j + 1) of u then
				set {item j of u, item (j + 1) of u} to {item (j + 1) of u, item j of u}
			end if
		end repeat
	end repeat
	return u
end sortList

BTW, I thought the shell’s sort command might be an option but it was slow in testing. I guess that’s a reflection of do-shell-script and of calling the sort utility.

#10

Thanks Fredrik71–that thread has some good information.

FWIW, another basic AppleScript suggestion:

set theText to paragraphs of (read (choose file))
set theHeader to item 1 of theText as list
set theList to items 2 thru -1 of theText

set {ATID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ","}
set theList to bubblesort(theList)
set AppleScript's text item delimiters to linefeed
set theText to (theHeader & theList) as text
set AppleScript's text item delimiters to ATID

theText

on bubblesort(a)
	considering numeric strings
		repeat with i from (count a) to 2 by -1
			repeat with j from 1 to i - 1
				if text item 3 of item j of a > text item 3 of item (j + 1) of a then
					set {item j of a, item (j + 1) of a} to {item (j + 1) of a, item j of a}
				end if
			end repeat
		end repeat
	end considering
	return a
end bubblesort