Copying data to CSV and other text files from FoxPro

Migrating data between different applications can be a time-consuming job. XML promises to make the work easier but there are still a lot of older applications which can't work with XML. Sometimes the only way of exchanging data is to reduce it to a plain text format. Various versions of FoxPro's Copy To command let you generate many different types of text file.

Each version of Copy To takes data from the current work area and exports it to a new file. You can use the Fields clause to specify which fields you want and the For clause to select the records. If you need a more complex selection of the records or more sophisticated processing, then use a query to select and process the data into a cursor and then export the data from the cursor.

It's very easy to generate a straightforward comma-separated (csv) file:

This example will create a text file with commas between each field and with each record on a new row.

What does "delimited" mean?

This is an important question and one which can lead to misunderstandings between programmers who come from different backgrounds. Be very careful when you hear this sort of terminology. You will sometimes hear a CSV file being described as a "comma delimited file". This phrase can be used to mean two different things so make sure that you know what is wanted and ask for a sample of the data before you start work.

Some people would say that:

is a comma delimited file. What they mean is that there is a comma between each data value; they take the quotes around the names for granted because that's almost a standard in the PC world. People working on other computer systems might use different terms. They might say that that output was separated by commas but that each string value was delimited by double quotes. You're unlikely to make a mistake when you're talking about commas and quotes but if someone asks you to create a file delimited with underscores or pipes then you really do need to see a sample.

Note that it's only the string values which have delimiters. The numeric values stand alone.

Other delimiters

The extended form of the Copy To command starts by following one of these conventions but then contradicts itself:

This command will produce a text file having this structure:

It's obvious that Fox is taking "delimited" to mean the characters marking the strings here. Three further forms of the command take the opposite meaning and take "delimiter" to mean the separator between fields:

These three commands will produce text fields with tabs, spaces and underscores between the fields respectively. All of them retain double quotes as delimiters around the strings:

Finally, we can mix the two terminologies and specify two different "delimiters", one between the fields and another around the strings:

This will give us underscores between the fields and dollar symbols around each string:

It might look a little confusing but with a bit of experimentation you should find that you can match most formats used to export data as plain text.

Fixed width data

The SDF format generates files where there are no delimiters between the fields. Instead, each field is output with trailing spaces at its full width:

If the Town ID fields are four characters long and the town names are 15 characters long then this example will generate:

In this example the spaces have been shown as '.' for clarity and the paragraph markers have been displayed.

Limitations of Copy To

These different forms of the Copy To command let us generate a wide range of text formats but they won't let us generate a text file which has paired separators such as [ and ] or HTML tags like <b> and </b>. If you need to generate a more complicated format such as this example:

then you will find Foxpro's TextMerge command useful.