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, use a query to generate and transform the data and then export from the query.

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

This 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 leads to misunderstandings between programmers who come from different backgrounds. Be very careful when you hear this sort of terminology. You will often hear a CSV file being described as a "comma delimited file". It can be used to mean two different things so make sure 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 do need to see a sample.

Other delimiters

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

This command will produce something like this file:

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 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 this sort of output:

then you will find Foxpro's TextMerge command useful.