ActionScript.org Flash, Flex and ActionScript Resources - http://www.actionscript.org/resources
Using MS Excel to quickly populate a text file
http://www.actionscript.org/resources/articles/622/1/Using-MS-Excel-to-quickly-populate-a-text-file/Page1.html
Ryan McDonald
I have been teaching English in Japan, for 5 years. Before that I was living the life of Peter Gibbons from the movie Office Space. I've done some ColdFusion programming, but only recently got into Flash.
 
By Ryan McDonald
Published on June 4, 2007
 


Introduction

I’m going to assume you can make files in Flash that can read from text files, but you don’t need to know anything about MS Excel. You could use any spreadsheet program such as OpenOffice Calc, but I’m going to talk about Excel for this article.

 

I teach English in Japan and have found flash to be great for drilling English topics and creating quizzes. I live in a small country town so most of my kids don’t have internet at home, or have limited 14.4 dialup (seriously). So if I want the kids to practice at home I have to make programs that read from text files, though I also use this process to make MySQL databases since some kids can use the internet. That will be part 2.

 

My quiz programs are simple and work from simple text files. One example is my ‘past tense’ quiz. I made a file with over 100 words (in Japanese) and then 4 possible spellings of the word in English, one of which being correct. Another example is ‘Many Much’. I have a list of about 250 things in English and students have to choose if they are countable or not and select ‘how many’ or ‘how much’. The latter requires 250 lines in a text file, with an ID, word, correct answer, wrong answer, even more if I gave an example sentence. I am far too lazy to type all of this out in the URL encoded format.

 

&word1=water&answer1=much&wrong1=many

&word2=English&answer2=much&wrong2=many

&word3=

 

It’s just too much for me, plus some of the entries seem to repeat and others seem to progressively increase. For situations like this where the data is similar and increments in a pattern, Excel can save you hours. Let’s get started.


The Spreadsheet

This project will be simple, but once you learn the concept you can create bigger files and databases. For this project we will have a text file with three variables, WORD, ANSWER, and WRONG. This will be for my quiz program that displays the word and gives two options where students select the correct answer. At the end I will show some other shortcuts that can be added to save even more time.

 

First you need to open your spreadsheet.

 

In column A, row 1 type the following:

 

&word1=

 

Now skip over to column C row 1 (from here called C1 etc) and type:

 

&answer1=

 

Now skip over to column E1 and type:

 

&wrong=

 

To clarify, all you are doing is clicking once on the cell and then you start typing. At this point you should have this:



Now you want to click and drag from A1 to E1. They should all be highlight as shown below.

 


Why the spreadsheet is helpful

Now we get to the part where the spreadsheet comes in handy. Move your mouse so the cursor is over the little square of the bottom right corner of all the selected cells. You will notice it changes when you are directly over this little box. However, be sure you are over the little square, if you go over a line the cursor changes to something else.

 

If you are over a border line of the cells, the cursor changes to a 4 point arrow. That allows you to physically move the group of selected cells, which is not what we want to do. We want the little box in the lower right corner. Below I drew a red circle around it.



Click and drag it down about 20 rows. Then you will see the magic. If there is a number in a cell with some text, and you click-drag this box it will automatically increment. If there is only a number, it will simply copy the number. If there are two cells with two different numbers like 1,2 it will also increment those. Likewise, if the numbers are further apart such as 50, 100 the next number will be 150. This also works for sequential words such as January, February….Monday, Tuesday….

 

Once you have dragged this box down, you should see the following:

 


A little typing

You can already see how easy and useful this can be. But it’s still not over, there are other things we can do that save even more time. Plus we have to concatenate them all together to make the file.

 

Now, to make use of the spreadsheet we need to group similar answers together. I am going to make this about “is it a verb or an adjective?” So the word will be either a verb or an adjective. I’ll have the first 10 as verbs and the last 10 as adjectives. I have to type in each one of these, but it won’t take that long.



Now back to the beauty of the spreadsheet

 

Type in the answer once in D1, and the wrong answer in F1. Then click our magic box and drag them down to row 10. Do this once each for both columns. You could also click drag all the boxes and hit CTRL-D which fills the rows down with whatever is in the top box. CTRL-D doesn’t increment, it simply copies. If there is nothing to increment, the little magic box will just copy also.



Getting closer

Now either reverse them and type the new correct answer D11 and vice versa for F11, or I would just copy and paste the group of cells from above into the opposite side as shown below. To copy the cells just click and drag and then hit CTRL-C or go to Edit-Copy.



The bluish highlighted area is the part I just pasted.

 

Ok, now we are getting really close, but we need to convert this into the url encoded style that Flash can read. Now we are getting into some heavier Excel stuff, but bear with me, it’s not so bad.

 


Go back up to the top and click on cell H1.

 

Now we are going to concatenate all these things into one space-less string.

 

In cell H1 type the following verbatim with no spaces:

 

=A1&B1

 

Then hit return and you will see the first part of your string.

 

&word1=run

 

Now go back to H1 and double click to start retyping in the cell, or use the function bar at the top, and type the following (we are just adding this on to the end of A1&B1:

 

=&C1&D1&E1&F1

 

So it should completely look like this:

 

=A1&B1&C1&D1&E1&F1

 

When you hit return, you will see the whole line as a Flash readable URL string.

 

&word1=run&answer1=verb&wrong1=adj

 

Now comes my favorite part. Click cell H1 and then find the little magic box and drag it down to row 20.

BOOYA!! Excel just did everything for you.





Final stretch

You simply click-drag the cells in column H and copy them into your text file. Each line has the &, the variable name, the =, and no spaces (apart from those in a variable such as &word=a dog).

 

Can we make things even easier?

 

Oh yes, we can. Let's say you have a file with 500 entries and you aren't able to put them in order like we did above, or you want to add to the files and when you add data it won't be in order either. For these situations, or just to make it easier, we would use some Excel functions, mainly the IF function.

 

In this case, in cell F1 rather than typing the wrong answer I would write an IF function that would see what is in the correct answer cell and insert the opposite. The syntax for that is:

 

= IF (condition, true action, false action)

 

So in F1, I typed the following:

 

=IF(D1=”verb”,”adj”,”verb”)



This means IF whatever is in D1 equals “verb” (the quotes make it a string), then show (echo) the string “adj”. Otherwise show the string “verb”. The quotes are important, without them you get an error. Since the string text D1 does equal “verb”, then the true action is to show the string text adj. Since the only things that will be in D1 are “verb” or “adj”, the false action only has to show “verb”. You can nest these IF functions pretty deep, but I'm not going to cover that.

 

So now just drag the magic box down in column F and it auto-fills all the cells with the new function. Note that when another cell, such as the function in the H column cells, refers to a cell with a function in it, like the IF in the F column, it refers to the result of the function. Basically, whatever ends up in the "IF(D1=" cell is what the H colum cell will use. Now all you have to do is add words and the correct answer and it will auto-update the wrong answer.

 

This can also be used for databases in situations where large amounts of the data repeat or increment-decrement. However, for use in databases, this requires a bit more on the Excel side. I'll cover that in part 2 as well as show a few other little Excel tricks.  I've also realized recently that Excel can be used to automatically make RPG map systems, since they are usually repeating 1, 0, [], and commas. I'll work on that for part 3.


See the attached spreadsheet if you want a closer look.