Ticket Options

Question Profile

Question Details

TICKET ARCHIVE -> Database software

corinnex - Feb 9, 2007 - 8:24 am

I wonder if anyone can recommend a software item that will allow me to put lists in alphabetical order ie a databse. I use Pages software for word processing and that does not have a database option and my Appleworks freezes too often to be of much use.

Thanks

Corinne

stottm - Feb 10, 2007 - 8:25 am

If all you want to do is sort a list of text you could do that from the command prompt for free. (i.e. Unix prompt)

1. Put your unsorted list into a text file in your home folder.
2. Open the Terminal - /Applications/Utilities/Terminal
3. Type the following:

sort -f unsorted.txt -o sorted.txt

Open the sorted.txt and you will see it sorted alphabetically. If you type "man sort", without the quotes in the Terminal, you will receive the manual page for the sort command. There are many options and configurations on how to sort. The -f command flag means to ignore case so "b" and "B" are treated the same. The -o command flag means to output to a file and you provide the filename to output to immediately after the -o command, "sorted.txt".

There are literally hundreds of text processing commands from Unix included in Mac OS X. There is an extreme amount of power present on the command line. Just about all commands have a manual page so typing "man commandname" will give you the options and how the command works.

The basic philosophy behind Unix is to have a lot of small commands that do one thing well and then combine multiple commands together by using the pipe character " | " which means to take the output of the command on the left and pass it to the command on the right.

So if you wanted the sort command to not only sort a list in a text file but also then open that text file in TextEdit you could do this:

sort -f unsorted.txt | open -f textedit

The " | " pipe character passes the output of the sort command to the open textedit command. The -f for the open command tells it to take the input passed to it. When you run the command string above you will get your sorted list open in TextEdit.

Try it with this list of unsorted names containing A-Z letters.

Timmy
Henry
Dick
Johnny
Zed
Albert
Quincy
Uberto
Igor
Victor
Oscar
Macy
Walter
Charlie
Yanni
George
Paul
Xavier
Sam
Kelly
Edgar
Lucy
Robert
Benny
Frank
Nancy

stottm - Feb 10, 2007 - 8:45 am

Be careful with table lists. i.e. multiple columns when sorting. You will have to play with it a bit to understand how it works and try the various options in sort. You might have to find other commands and script them together using one of the many script languages available on Mac OS X. i.e. Perl, Python, Ruby, Bash, AppleScript, Automator, etc.

If I had to recommend one script language to someone who knows nothing about scripting, I would recommend Ruby of which there are many new books available to learn Ruby. It's a very easy language to learn. AppleScript is also very easy.

Now you might want more then just sorted lists. You might want to ask questions from a list and only get back a partial list. i.e. I have a table with a multiple columns. I have a column for name, email, and age. I want a list of everyone over the age of 16 and I want that list sorted alphabetically and I only want to see the name and email address, but I don't need to see the age as long as they are all over the age of 16.

This is the sort of task designed for a database. On Mac OS X the easiest user friendly database is called FileMaker Pro. (http://www.filemaker.com/)
It's not cheap but it gives you a nice graphical interface and you don't need to know advanced database technical speak (SQL - Structured Query Language).

There are many free databases but they all require knowing SQL and reading a lot of documentation and getting a book, etc. They don't generally do reporting or allow you to create data entry forms, etc. These free databases are like the big commercial enterprise databases such as Oracle or IBM's DB2/UDB or Sybase. These advanced databases are command line only and if you want a graphical interface then a team of programmers will create it. FileMaker allows you to do all that on your own without knowing programming and it's fairly easy for a novice to get things done.

However, if you are adventurous there are two main free (open source) databases available for Mac OS X. They are PostgreSQL and MySQL. Both are fast and both are popular. However they both have a steep learning curve. I would say that PostgreSQL is a bit more professional and powerful then MySQL but lately, MySQL has been getting better. PostgreSQL is similar to Oracle and MySQL is unlike most other databases, it's popularity is with web developers who just need a list and they need it very very fast.

Again, using PostgreSQL or MySQL is a very advanced topic. It will require a book it will require patience and understanding. It's not for the weak of heart. Most people who delve into these applications are programmers or web application designers. These two databases power hundreds of thousands of website backends and if properly configured can handle 4,000+ database transactions per second. i.e. 4,000 queries from 4,000 individual website visitors all at the same time.

So I come back to FileMaker, just remember you can save a lot of money if you are willing to learn PostgreSQL or MySQL. The money you pay to FileMaker is to make life easier for you. You don't have to know all the underlying technology, you just point, click, drag & drop, type and save.

stottm - Feb 10, 2007 - 8:55 am

I almost forgot, Microsoft Excel is like a digital accounting ledger. It has cells which are organized in columns and rows. Many people find this to be a poor man's database. It's capable of organizing lists quite effectively and if you need to perform mathematics on those lists it excels at that task, no pun intended...

However, you can get an open source free replacement for Microsoft Excel or even FileMaker (although those applications are more powerful and better over all ) called Open Office, the Mac OS X port is called Neo Office:
http://www.neooffice.org/

It's free and includes a full suite of applications that competes with Microsoft Office and AppleWorks as well. It is very capable.

corinnex - Feb 10, 2007 - 1:25 pm

Gosh! I am overwhelmed with the detail that you have taken the trouble to give me and am grateful. However the first bit of your answer looked as if it would solve my problem (I'll come back to that later) as I looked up the Filemaker thing and that was much too expensive for me. I am worried though about the neoffice download - if it competes with Filemaker how come it is free? There must be a snag ...? Anyway the Unix suggestion that you started with had all sort of things I did not understand (sorry - my level is more basic than I realised!) I managed to find Terminal and then typed in what you recommended but I did not know how to open sorted.txt or how to make the connection between my little file of jumbled names in Text Edit and the terminal thing. I do not know where the command line is and I did not even know that Unix was something that applied to my Mac. So you can either quit now - which I shall understand - or go back a few steps for me.

Thanks again

stottm - Feb 10, 2007 - 7:31 pm

NeoOffice is an Open Source project. This means that programmers donate their free time and assist in building the software. They give it away but more importantly they give away the original programming code. Which is called the source code. This is the way software was first distributed in the beginning until some decided to start charging for software. The Open Source movement is a rebellion against those who charge for software. The Linux operating system and the same BSD Unix that is built into Mac OS X is all open source software.

Now back to the basics:

Open up TextEdit copy and paste the list of names I gave you and save the file to your home folder name it unsorted.txt. Then open the Terminal and try the command again. The Terminal is located in the Applications folder and inside the Utilities folder.

When you are at a command prompt with a dollar sign symbol. Try the command again. Good Luck, if it's still too much. Try NeoOffice.

corinnex - Feb 11, 2007 - 12:09 pm

I am going to keep going for a bit with your Terminal suggestion. So the next problem is that I have cut and pasted your list to a Text Edit document and when I try to save it as unsorted.txt. it says "You cannot save this doc with the extension .txt at the end of the name. The required extension is .rtf You can choose to use both so that your file ends in .txt.rtf" It then offers me the option to use both. Should I or am I doing something wrong? Suppose I did achieve that bit I do not understand the next bit "Then open the Terminal and try the command again" Where is the command? This is what the terminal screen looks like Last login: Sun Feb 11 12:25:14 on ttyp1
Welcome to Darwin!
corinnes-computer:~ corinne$ sort -f unsorted.txt -o sorted.txt
sort: unsorted.txt: No such file or directory
corinnes-computer:~ corinne$ /Users/corinne/unsorted.txt.rtf /Users/corinne/unsorted.txt $
-bash: /Users/corinne/unsorted.txt.rtf: No such file or directory
corinnes-computer:~ corinne$
You probably wish you had not opted for this query!

thank you

.

stottm - Feb 11, 2007 - 12:13 pm

Doah! My Bad... TextEdit can do both RTF and Text files but the default is RTF. In TextEdit click on the Format menu and click Make Plain Text. That will allow you to save it to a text file... Sorry, forgot about that...

You can change the default of RTF to TXT in the preferences in TextEdit, i.e. Rich Text vs. Plain Text.

stottm - Feb 11, 2007 - 12:14 pm

You got the terminal part right, you just need to save the file as plain Text. Rich Text is not going to work.

stottm - Feb 11, 2007 - 12:16 pm

I regret nothing... I do tech support for a living and was doing it long before getting paid in a full time job. I enjoy helping folks use their computers more effectively. I'll send you some Unix tutorial links later on. Once you get the hang of it, you will be amazed at how much you can accomplish in the Terminal. It's raw unbridled power! ;-)

stottm - Feb 11, 2007 - 12:34 pm

Here's a good place to start reading, nothing overly complex or dangerous
http://www.osxfaq.com/Tutorials/LearningCenter/#unixtut

Just don't ever use "rm -rf" it means Remove (delete) all files recursively from the folder you are currently in to all sub-folders and forcefully delete all files without warning. Many a system administrator have done just that in the wrong place and wiped out their system! However, it's unlikely you would wipe out anything more then your user files as you won't have full permission to shoot yourself in the foot like that. You can use the "rm" command just don't use the optional flags "-rf" along with it. The "-f" command generally means Force and it's handy when you know what you are doing. i.e. it stops the system from asking if you want to delete every single file one at a time. This is useful if you really do want to delete 500 files but dangerous if you do it by accident in the wrong folder. So if you ever use the "rm" command be very very careful as it does not use the Trash can so there is no undoing!

The tutorials I just linked to don't discuss the "rm -rf" scenario and that is why I've explained it here. It's a definite GOTCHA to watch out for. Something so powerful as the Terminal also makes it semi-dangerous. Again, the security in OS X will stop you from destroying anything really important but that doesn't mean you can't delete all your documents by accident!

It's highly unlikely that you will do this completely by accident. So don't get scared! Just don't use the "rm" command, better to delete using the Finder in OS X then to delete files using the Unix Terminal Command Line's "rm" command. It doesn't backup the files in the Trash and it's effect is immediate and irreversible.

Don't panic and give up, just be aware of this one dangerous command and avoid it... ;-)

stottm - Feb 11, 2007 - 12:35 pm

BTW, if you do need to reach me outside of http://macosx.com/ just send an email to stottm@mac.com.

Matthew Stott

corinnex - Feb 12, 2007 - 11:09 am

Well after many unsuccessful attempts I managed to do the sort -f unsorted.txt | open -f textedit with your list and I was really pleased with myself. I could not do the first one sort -f unsorted.txt -o sorted.txt - I am not sure why. I did not realise about the dollar sign so that is more progress! Anyway my next question is going to sound silly but if I wanted to sort a list that was not your unsorted but one that I have of church members, do I have to put it in a textedit document and call it unsorted? Or does the command somehow change and I put in "church' or whatever according to what I am sorting?

I did look at that UNIX tutorial and I think that I will work on it at some stage because you have got me quite excited with the potential but maybe I will ask you the questions as I come up against problems now until I am more confident.

Thank you again
Corinne

stottm - Feb 12, 2007 - 9:03 pm

Congratulations, you figured it out. Not sure why the -o didn't work but it's not important. Yes, you can copy the list of church members and save it to a text file. You don't have to call it unsorted.txt, you can call it whatever you want. i.e. us_members.txt as an example.

So it would be:

sort -f us_members.txt | open -f textedit

Basically, it works like this, you have a command followed by options followed by a filename piping the output to another command with an option and then the application to open.

[command] [options] [filename] | [command] [option] [application name]

sort -f us_members.txt | open -f textedit

This means run command "sort" with option "-f" on input filename "us_members.txt" pass (pipe) the output of this command to the next command "open" with option "-f" and give it the application name "textedit" as a parameter.

Then you copy the text from TextEdit and paste it back into iWorks Pages.

This basic concept of [command] [options] | [command] [options] is the way all Unix commands work at a basic level. When you type "man sort" you will see a file that starts with the following line:

SYNOPSIS
sort [-cmus] [-t separator] [-o output-file] [-T tempdir] [-bdfiMnr]
[+POS1 [-POS2]] [-k POS1[,POS2]] [file...]
sort {--help,--version}

Sort is the command name, [-cmus], etc. are the possible options. Scrolling down the manual page explains what each option does.

I just tested sorting a short list of names with spaces.

John Smith
Alex Jones
Mark Simpson
Jane Doe

and the sort works by default and doesn't break up the names. So this will work for a list of names rather nicely.

Think this is fun? Oh you're just scratching the surface... Keep digging this is fun stuff!

Keep my email address as it's about time we close this problem ticket.

stottm - Feb 12, 2007 - 9:26 pm

Baby steps... Thankfully, Unix is broken down into very small commands that all do one thing very very well and that's it. You combine these commands together to accomplish a goal much like building blocks.

There are scripting languages that act like glue or mortar between the blocks. BASH (Bourne Again Shell) yeah it's cute, especially to us Christians... ;-) AppleScript, Ruby, Python, Perl, etc.

The BASH shell is the prompt with the dollar sign where you are entering commands. It provides a variety of things that are more complicated then we need to get into at this stage. Suffice to say, it can process bash scripts where you can provide logic such as:

If this condition is true do this series of commands if not skip these commands and run this other set of commands. Shell scripting is really a simple form of programming. When you need more you step up to a full script language like Ruby which can do a whole lot more then just string Unix commands together in logical ways.

But before I lose you completely, try watching this video which explains Automator and gives a bit of a taste of what's possible for even the novice:

http://www.twit.tv/mb3

BTW, this is called a video podcast, so you can subscribe to MacBreak in iTunes and it will download this wonderful T.V. show. It's a shame but Leo Leporte was on cable TV in the US but now he's only in Canada and online.