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?