Types of Delimited Files

Data files must have a structure to allow users to determine what kind of data they are looking at. One way to structure such files is to insert delimiters between data fields and at the end of records. Different types of files have different delimiters. The delimiter used depends on the kind of data the file and the data fields contain. Database programs can often recognize the type of delimited file, and convert it to the standard 

 

 

 

 

1. Basic File Structure

   • Delimited files contain data in fields and records. The delimiters indicate which data belongs together in a field, and which fields make up a record. A field is an individual data point while a record is a collection of data points that refer to the same element. A typical example is a customer list that has fields showing name, age, address and phone number. These fields make up a record referring to the same customer. Most data files have one record per line, but separate fields with a variety of delimiters.

   Comma Delimited Files

   • The most common type of delimited file is a file with comma-separated-values or .CSV. The files have one record per line, and the fields have commas between them. Most database programs can read .CSV files, convert them into their own native format and export them for use in other programs. A key problem for .CSV files is including data that contains commas. Data processors must either remove the extra commas during data validation or use non-standard work-arounds such as enclosing the field in quotation marks.

   Tab Delimited Files

   • Data with complex text structure often needs commas to be comprehensible. For this reason, many major databases use delimiters other than commas. A popular alternative is the tab-delimited or tab-separated-value (.TSV) file. Most database programs can read these files, and they have the advantage that they generate neat columns, with the left sides of the data lined up, when you open them in word processors.

   Other Delimiters

   • The next most common delimited files are those using colons as delimiters. Colons suffer from the same problem as commas if the data contains colons in its text. Similar other alternative delimiters are semi-colons and spaces. Data files use these delimiters depending on whether the data contains the delimiter. Some programs allow you to choose any character as the delimiter for a particular file, but these files may not be compatible with other database programs.