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 the Copy To command 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 any of the scope clauses to select the records. If you need a 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.

The Copy To command will export the contents of the current work area regardless of whether this is a table, a cursor or a view. The limitation is that the command is restricted to the current work area so you may have to change work areas before the export and change back afterwards.

Plain CSV file

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

This snippet of code will export all the fields of the current table into a text file with commas between each field and with each record on a new line.

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 these few lines:

are typical of the syntax of a comma delimited file. There are two fields in the table and each record has its two values on a separate line.

There is a comma between each data value but you've probably taken the double quotes around the names for granted because that's something of a standard in the PC world. The danger is that we often have to use a csv file when we're sharing data with other computer systems and people working on other operating systems might have a different understanding of the term "comma delimited". They might say that that the output here was separated by commas but that each string value was delimited by double quotes. You're unlikely to have any confusion 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 so that you're sure that you understand what is needed.

Note that it's only the string values which have delimiters. Numeric and date values stand alone. Don't put braces around date values because that's FoxPro syntax. Access for example expects a # character either side of a date.

Other delimiters

The extended syntax of the Copy To command starts by following one of these conventions for the meaning of "delimiter" but then contradicts itself. For example, if we specify a dollar sign as the delimiter:

then this command will produce a text file having the following 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 does not use delimiters or separators. Instead it generates files where 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.

Using matched pairs of delimiters

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.

Memo fields in a csv file

None of the forms of the Copy To command will copy memo fields to a csv file. The problem is that the memo field is very likely to contain multiple lines of text and there's no recognised way of storing line feeds and carriage returns in a memo field. One approach might be to store the memo field as HTML. This will fit into the CSV format as plain text and it's easy to convert the carriage returns and line feeds of the memo field into HTML:

This gives us a memo field of plain text but still leaves us with the problem that the the Copy To command will not process a memo field. The solution again is to use the TextMerge function inside a scan loop.

If you do have to create a csv file which includes memo fields then you must talk to the person who'll be receiving and processing the file. They too are going to have to do some preprocessing before they can import your data into their application and they might have restrictions which make the whole process impossible. For example, the FoxPro memo field might be holding more text than their import routine can accept.