Import CSV data into an Appleworks database

Dec 01, '03 10:17:00AM

Contributed by: klieb2002

Appleworks 6.2.4 lacks the capability to directly import data from a comma-separated-value (csv) file (reportedly this is true of 6.2.7 as well). Many PC applications export data in csv format, for example. You can get around this problem in most situations, however; the basic idea is that you can use TextEdit to convert comma separated values into tab-delimited fields, which Appleworks will import into the database format. There is one wrinkle, which I will get to in a moment.

Use TextEdit to open the .csv file, either through the File -> Open dialog, or by drag and drop onto the icon. First you need to make sure that there aren't any commas embedded in your fields - if so, you'll have to manually change them into something else or remove them. Otherwise, when you change commas into tabs you'll be splitting fields. If you just have a few sprinkled here and there, I would do a one-by-one find and replace to change them into some unused character string like %% for now; later we will change them back.

Next, change all the field-separating commas to tabs. You can't enter Tab into the Find/Replace field directly, so you have to resort to a trick: open a second blank document in TextEdit, enter a Tab, then select the tabbed space and copy it to the clipboard. Switch back to the .csv document, and open the Find/Replace dialog. In the first field (Find) enter a comma, and in the second field (Replace) paste your tab copied from the other document. Click on Replace All.

Finally, Appleworks doesn't need to see all the fields enclosed by double-quotes, so do another Find and Replace to replace the double quotes with nothingness. If you had changed some commas within fields to another character like %%, do yet another global Find/Replace to change them back into commas. Save your file as a .txt file.

Within AppleWorks, you should set up an empty shell database with the fields you wish to import. You will use the Insert... dialog box to open your new tab-delimited text file. There are two drop down menus below the Column View of the file system; the first one specifies which type of document to open (in this case, database or spreadsheet). In the second drop down, choose ASCII text. Appleworks should recognize the tab-delimited fields, and present you with a new dialog box that allows to you match up fields from the insert file with fields already in the database. If you build your database shell in the correct order, the fields should line up, otherwise you will have to dink with it.

[robg adds: I've done a lot of stuff like this, and here's a possible timesaver ... instead of worrying about the embedded commas, search for ",", as that string will only exist at field separators (end of previous entry, separator, start of next entry). Any commas embedded within a field will not be replaced. Then do one more replace for the remaining " marks to replace them with nothing, and you'll be done.]

Comments (0)

Mac OS X Hints