AS3, Flash, ,

This is going to be a simple post, based on a request, and it’s all about CSV files. Comma seperated value files are exactly what they sound like, a file of rows and in each row are a set of values seperated…by commas. Another aspect you may not be aware of is that XLS or excel files from the Office (the boring not funny type) can also be saved directly as CSV files.

Very often you will also see XML files being used as data sources for files, and to be honest they are a slightly more advanced solution allowing very structured data and not restricted to just a single nxm 2 dimensions. On the down side they are complicated to look at, can be very easy to break and are not something we would let a client loose on, as opposed to giving them a spreadsheet.

As a consequence, for simple data updates to a site or tool where we don’t want the customer to have to have to ring us up every 5 minutes just to change the contents of a dropdown we might give them some very somple instructions along the lines of:

1. open the local version of the file in excel
2. make changes / add / remove etc
3. save the file and close it (more on this)
4. Use the upload protocol to place the file onthe server

And bang the customer has updated the tool without having to ring you up with annoying questions.

So, how is it done ? REALLY simple but witha few caveats to remember. The base premise relies on a standard

var loader:URLLoader = new URLLoader();
loader.addEventListener(Event.COMPLETE, dataLoaded);

var request:URLRequest=new URLRequest("data.csv");

loader.load(request);

couldn’t be easier, could it. This will load the entire file into the data entity of the loader and then we can parse it and get the values back pout again and into some data structures. So, let’s talk about that first.

You will need to create the data source suitable for your project and most of the time you would probably just read the file and store the data in arrays as they are read in. For this example I am going to introduce a slightly more advanced structure which I use a lot and is based on good old fashioned linked lists. While we will store our data in arrays, we are going to use a seperate array structure to store the indices of the structure and then whenever we need to move, add, change or delete an item row we are going to use this indice structure.

a simple example might be indice[i] holding the indices and data[j] holding the data. look at the structure below.

x[0] = 1, x[1] = 5, x[2] = 14, x[3] = 16 and then data[1] = bob, data[5] = susan, data[14] = mickey, data[16] = john

now let’s say we want to move the order a bit and place susan in from of bob. We never touch the data[] structure and instead just swap the index values of the indice array so we now have:

x[0] = 5, x[1] = 1, x[2] = 14, x[3] = 16 and data remains the same at data[1] = bob, data[5] = susan, data[14] = mickey, data[16] = john

Next let’s say we want to remove mickey from the list, we wouldn’t need to touch the data array at all, just clear the index element:

x[0] = 5, x[1] = 1, x[2] = 16 there are array methods to help you do this like unshift, splice, pop, push and others. They are worth taking a look at and playing with. In this case x.pop() would clear the last element.

Why on Gods green earth would anyone want to do this you are probably asking. The example is for a simple 1 tier structure, but imagine if there were 20 columns for each row, this method still only changes the indexing array as opposed to having to move and change all 20 arrays.

In the example for this post the excel spreadsheet csv file has three columns so the folowing code makes up the structure.

var Data_ID:Array = new Array();
var Data_name:Array = new Array();
var Data_sort:Array = new Array();

The allRows stores the id’s only of all the rows in the right order – which would then give us access to the data

var allRows:Array=new Array()

then as we read them in we assign the id to the index array and place the data in the data arrays at the id index (I can use ID here as it is always unique – if it were not unique I would need to make up a new index key with a numeric stepper)

Next we need to load the data from the loader class.

loader.load(request);

and then in the completion function set up earlier we can start to parse the information in the file.

function dataLoaded(evt:Event):void {

var myData:String=loader.data; // contains the data
var myDataRows:Array=myData.split("r"); // splits the rows into a seperate array

var smallArray:Array;
var newID:int;

for (var ii:int = 0; ii< myDataRows.length0; ii++) { smallArray = new Array(); smallArray=myDataRows[ii].split(","); // splits the individual elements by the seperator comma newID=smallArray[0]; // this is the indexer value allRows[ii]=newID; // assign the indexer to the indexing array in order // next place the data into the data arrays at the position of the indexer, which is the id Data_ID[newID]=int(smallArray[0]); Data_name[newID]=String(smallArray[1]); Data_sort[newID]=int(smallArray[2]); } }

and that's it, simple.

A few small caveats that you need to think about finally. If you are going to include headers for your customer you would start the for loop at 1 not zero but remember to offset in the allArray by subtracting 1 from the loop index. Make sure when you are testing locally that the CSV file is not still open

in excel as this will cause an error. Sometimes excel saves an extra row or rows of empty cells at the bottom of the CSV file. You need to play with this and test and then adjust your code to make sure it works, all to do with end of file markers.

The simple rule is to build it and then test it to death to make sure it works. Enjoy the sample file in rar format