Crash Course Delphi 14 [beta version]:
CSV Files - part 1

(Continued in Lesson 15)


Build your own Database Engine

In our next series of lessons, we'll show you how to set up a "flat file" database based on CSV files.

A full blown database system is often overkill for simple storage and retrieval of data. Just format your data according to some simple rules and save them in a text file. That's good news if you're on a small budget: you don't need the quite expensive professional edition of Delphi with its database components, a personal version will do.


The CSV File Format

The CSV (Comma Separated Value) format is often used to exchange data between different applications. CSV has become a pseudo standard throughout the IT industry.

CSV files are set up according to the following rules:

  • Each record is one line. The record separator is a carriage return and line feed pair.
     
  • Fields (columns) are separated by commas, as it says in the name "Comma Separated" ;)  Example:
    Doe,Mary,"99 Longstreet",Anytown,08123
     
  • Fields with embedded commas or spaces must be delimited with double-quotes.
    In the above example, "99 Longstreet" had to be delimited with double quotes because it has an embedded space.
     
  • Leading and trailing space-characters next to comma field separators are ignored. Space characters can be spaces or tabs. Example:
    Doe   , Mary   ,... is the same as: Doe,Mary,...
     
  • Fields that contain double quote characters must be surrounded by double-quotes, and the embedded double-quotes are represented by a pair of consecutive double quotes. Example:
    Doe "DelphiGirl" Mary becomes:
    Doe,"""DelphiGirl"" Mary"
     
  • The first record in a CSV file may be a header record containing field names, but there is no mechanism for automatically detecting if the first record is a header row: this will have to be done by your reading software. The header row is encoded just like any other CSV record. A header example:
    Lastname, FirstName, Address, City, Zipcode
    Doe, John, Cabin, "Desert Town", 12345
    Doe, Mary, "99 Longstreet", Anytown, 08123
    " Jackson ""NoFirstName""", ,"12 Shortstreet", Anytown, 08123
     
  • You can leave some fields blank, like mr. Jackson above, who has no FirstName field.

Semi-colon separated files

A quick way for building a CSV file, is saving a Microsoft Excel worksheet as a CSV file.

However, if you live in a country where a comma is used as decimal separator rather than the decimal dot (France, Belgium, Germany, Sweden,... versus USA, UK,...) Excel uses a semi-colon to separate the fields. For this, Excel looks at Windows' International settings. Example:

Partnumber;Article;Price;Stock
12005A;Calculator;20,49;26
12005B;"Calculator Pro";30,49;10

In the next lesson projects, we will stick to the original CSV format. We always use commas for the field separators and points (dots) for the decimal separators:

Partnumber,Article,Price,Stock
12005A,Calculator,20.49,26
12005B,"Calculator Pro",30.49,10

In order to make CSV files for testing, you can force Excel to use the comma delimiter if it uses the semi-colon: just temporarily change your Windows configuration. Or use a text editor to firstly replace the commas with dots, next replace the semi-colons with commas.
Later on, we'll write a conversion utility for non-standard CSV files with semi-colons.

Lesson 15 »