revUp - Updates and news for the LiveCode community
Issueu 119 | September 29th 2011 Contact the Editor | How to Contribute

Convert data into an array
Follow this step by step lesson to learn how to get your tab delimited data into an array

by Bjoernke von Gierke

Converting tab delimited data to an array can be confusing at first. Read about a few ways one could go about it, including reordering of tabular data, simple two row tables and a complex example.

Attached Files

Create some sample tabbed data

To start off, I have created this example data and put it into a stack. Normally one would of course have preexisting data, but this'll do for starters.

Name    Type
Calvin    Boy
Fritz    Cat
Garfield    Cat
Jerry    Mouse
Mickey    Mouse
Snoopy    Dog
Tintin    Adult

This is the most basic tabbed data, just a header row that describes each of the two columns with content.

Note: If you are copying and pasting this example, then you have to insert the tabs manually.

Split it all up

Media_1265236799566_display

To put that into an array, the most obvious way is to use split. The following example script does just that, and then tries whether it was successful, by returning one of the keys of the resulting array in the messagebox. See how the code says "by return". That part specifies the element delimiter, where "and tab" tells Revolution what the delimiter between the key of each element, and the contents of each element is.

on mouseUp
    put field 1 into theData
    delete line 1 of theData --do not want header row
    split theData by return and tab
    put theData["fritz"] --shows "cat" in the message box
end mouseUp

Note: "return" is also a command, that's why it has a different colorisation then "tab" in the script editor. Don't let yourself be confused by the coloring.

Important: If your first column contains entries that have the same value, then this will not work. Each key needs to be unique. So if the first column where numbered from 1 to 9 but there'd be two 3's, then only one of them would end up in your array!

So what about some more columns?

Media_1265244338870_display

That was easy... but what if there are several rows of data? In such a case, it is best to know a bit more about the data. Here we have a quite common case, created in thousands of excel files all over the world, every hour of the day.

Number    Name    Type    Source    Year
1    Fritz    Cat    Fritz the Cat    1965
2    Mickey    Mouse    Mickey Mouse    1928
3    Snoopy    Dog    the Peanuts    1950
4    Jerry    Mouse    Tom & Jerry    1940
5    Garfield    Cat    Garfield    1978

Note: If you are copying and pasting this example, then you have to insert the tabs manually.

I said it is necessary to know the data. That is because one has to decide beforehand about how one will put that into an array. Will there be a cat/mouse/dog array that contains the entries as subarrays? Maybe one want's the numbers first, because they're unique, and everything else retained as tab delimited text. Possibly one just wants to split the columns or rows, for further processing or reordering. The possibilities are endless.

Let us assume we want the Type to contain the Names, which then would in turn contain everything else. But first we will look a bit at how to reorder stuff by using split and combine. We want the Number, Source and Year columns be after each other, so that we later can put them into our arrays. In addition, we want the Type and the Name to be at the beginning. That is why we need to reorder the columns first. Luckily, we can use the "by column" form of split to do that.

    on mouseUp
        put field 2 into theData
        split theData by column
        put theData[1] into temporary
        put theData[3] into theData[1]
        put temporary into theData[3]
        combine theData by column
        put theData
    end mouseUp

Sub-arrays a plenty

Media_1265244487791_display

To create the array, we could try to use the fact that split always uses the first occurence of a char as delimiter between keys and content, just as we did in the first step. But right now, that would be bad, because there's several lines that are cats or mouses. If we'd split now, only one of the mouses and one of the cats would survive, because an array can not have several keys with the same name. That is why we are using a repeat loop and items instead.

    global theResult
    on mouseUp
        -- first reordering
        put field 2 into theData
        split theData by column
        put theData[1] into temp
        put theData[3] into theData[1]
        put temp into theData[3]
        combine theData by column
        -- now the repeat loop
        delete line 1 of theData -- do not want description row
        set the itemdelimiter to tab
        repeat for each line theLine in theData
            put item 3 to -1 of theLine into theResult[item 1 of theLine][item 2 of theLine]
        end repeat
        put theResult["mouse"]["mickey"] --shows the content of one of the subarrays
    end mouseUp

Check it out

Media_1265293095435_display

Try to look at different array contents. The easiest way to do that, is to declare the output variable as a global (as we did above), because then you'll be able to access it from the message box, and even better, the "variables" tab of the script editor. There you can "browse" our newfangled array, and get a feel about where things got stored.

About the Author

Björnke von Gierke is a software developer, long time LiveCode community member and likes cats almost as much as charts.

 

Main Menu

What's New

RunRevLive.12 conference EarlyBird