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. The Copy To command will export the data from the current work area regardless of whether that work area holds a table or a cursor.

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

Copy To Town.csv Type CSV

If you have the Town table, or a cursor based on the Town table, open in the current work area then this code will create a text file of town data with commas between each field and with each record on a new row.

What does "delimited" really 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 before you start work. Ideally you should have a sample file so that you know exactly what is needed.

Some people would say that this snippet of text:

1,"Alvechurch"
2,"Bromsgrove"
3,"Catshill"
4,"Dudley"

is from 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 operating 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 and date values stand alone.

Other delimiters

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

Copy To Town.txt Type Delimited with $

This command will produce a text file having this structure:

1,$Alvechurch$
2,$Bromsgrove$
3,$Catshill$
4,$Dudley$

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:

Copy To Town.txt Type Delimited with Tab
Copy To Town.txt Type Delimited with Blank
Copy To Town.txt Type Delimited with Character _

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:

1   "Alvechurch"
2   "Bromsgrove"
3   "Catshill"
4   "Dudley"
1 "Alvechurch"
2 "Bromsgrove"
3 "Catshill"
4 "Dudley"
1_"Alvechurch"
2_"Bromsgrove"
3_"Catshill"
4_"Dudley"

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

Copy To Town.txt Type Delimited With $ With Character _

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

1_$Alvechurch$
2_$Bromsgrove$
3_$Catshill$
4_$Dudley$

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:

Copy To Town.txt Type SDF

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

1...Alvechurch.....¶
2...Bromsgrove.....¶
3...Catshill.......¶
4...Dudley.........¶

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

Matching 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:

1,[Alvechurch]
2,[Bromsgrove]
3,[Catshill]
4,[Dudley]

then you will find Foxpro's TextMerge command useful.

Tips for MS Access developers

Tips for VFP developers

General Tips

 

Entries related to csv files

FoxPro Functions

FoxPro has always had functions like FREAD and FWRITE to read and write files at a low level. They can handle files which defeat the STRTOFILE and FILETOSTR functions.

Read More

Visual FoxPro functions for file names

FoxPro functions to process names of files and folders

Read More

FoxPro functions for manipulating text

Foxpro commands and functions for text

Read More

FoxPro Gotcha

Common mistakes in Visual FoxPro

Read More

Close a low level file in FoxPro

FoxPro file handling - use FCLOSE() to close a low level file

Read More