FoxPro Tips (1): Use Excel to Write Your Codes

Figure 1: File Layout for a Source Text File
Do you ever think of using Excel or other spreadsheet application to help you to write your FoxPro program? Yes, when you need to write a set of tedious coding, you may find out Excel may be a great helper to you.

For example, in order to convert a source text file into DBF format, I need to create a table structure based on the file layout of the text file (just like Figure 1). However, the problem is the text file consists of 200+ fields - I would definitely look stupid if I copy the field names & field properties one-by-one into the SQL coding.

Your guess is right, I completed the relevant coding (CREATE TABLE (Field1 C(1),Field2 C(8),...)) within a few minutes by using Excel. Let me share with you how I did the coding:

STEP 1: Copy the file layout to Excel
Figure 2: Copy file layout to Excel
I copied field name, field type and field width from file layout Word document and pasted value into Excel. Of course, in order to do so, you need to first ensure that your file layout consists of the following info and is organized as per the format needed.

STEP 2: Build SQL coding for individual fields
Figure 3: Build SQL coding for individual fields 
Now I used the magic operator "&" to write SQL coding for each individual field, which I was going to include in the bracket after CREATE TABLE - should be in Fieldname FieldType(Field Width)  format. The formula I entered in cell E1 was:

=A1&LEFT(B1,1)&" ("&C1&")"

Then I copied and pasted the formula for other cells. You can see the formula and results as per Figure 3.


STEP 4: Combine coding for individual fields

Figure 4: Combine coding for individual fields (10 fields per line)
As it would be too long if I place the coding for all individual fields in a single row, I decided to break the coding into several lines - each line consists of the coding for 10 fields. After the end of the10th field of each line, I need to add an ";" in order to break the coding into the next line. The formula I entered in cell H2 was:

=IF(F2=1,"",H1)&E2&","&IF(F2=10,";","")

In column F, I included counters from 1 to 10 repetitively - in order to identify the first line & last line. Then I copied and pasted the formula for other cells.

STEP 4: Filter rows with counter = 10

Figure 5: Filter rows with counter = 10
I filtered the counter column by selecting only the row with counter = 10. I copied the cells in column H and pasted directly to my FoxPro program. Of course, as the counter for my last row of coding was not equal to 10, I manually copied the last row of coding and pasted into my FoxPro program.

Does it look easy to use this tip? Do practice more and you will become more familiar in making Excel as your little programming helper. Actually, you can use Excel to write Excel formula as well - guess how I do it?

Comments

Post a Comment

Popular posts from this blog

Other Ways to Prepare Prophet Model Point Files? Try FoxPro!

Image
If you are a frequent Prophet user, you must be definitely familiar with "Model Point Files" ("MPF"), i.e. the policy data that you compile in a specific format that Prophet can recognize as inputs for Prophet runs. If my guess is right, most probably you use Data Conversion System ("DCS") to convert the source data you download from your policy administration system (may be in various format, e.g. fixed ASCII / fixed width, comma delimited, tab delimited, etc.) into the required MPF, based on the definitions you specified in your DCS programs. In case your source data are divided into various files, you may need to carry out more steps to prepare MPF, for example: Open different source files in Excel Combine required fields from different source files by using commands (such as) VLOOKUP, SUMIF & MATCH. You may have automated this process by using VBA. Convert the required files into text files Use DCS to convert those text files into MPF  How...
Read more

How do You Setup Indicators in Your Prophet Model?

Image
In my previous post, I have discussed areas that you may consider in setting up input variables . Before I proceed my discussion on core variables, I would think that it is good for use to take a look on indicators - as indicators play an important role in setting core variables with various formulas (known as "variable definitions" in a Prophet model).  The funny thing about indicators is: it DOES NOT carry any formulas that you need to use in calculating a variable! Basically, they are just some texts that you can combine using different logical operators (AND, OR, NOT), such as "ORD_PAR AND SING_PREM" for a single premium ordinary participating product. 
Read more

How Should I Do My UAT? (1): Program / Spreadsheet / Model

Image
If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies. In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes...
Read more