CSV: what changed

Revision 3 to 4. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		## Overview
2	2
3	3		CSV means Comma-Separated Values, and is a popular file format that is unfortunately not completely standardized.
4	4
5	5		It is a text based file format with
6	6
7	7		- data fields separated by commas (or, in variants, other characters like tabs and semicolons)
8	8		- there may or not be a header line that lists the field names
9	9		- field data containing delimiters may be prohibited or enclosed by quotes (most commonly the double quote character)
10	10		- line endings (#13 and/or \#10) may or may not be allowed in field data
11	11
12	12		[RFC4180](http://www.rfc-editor.org/rfc/rfc4180.txt) tries to codify and standardize existing practice; it makes sense to conform to this standard when writing CSV data (and accept all RFC4180 data when reading).
13	13
14	14		A different, Creativyst/Excel, CSV pseudo-format specification can be found [here](http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#FileFormat).
15	15
16	16		A sample CSV snippet:
17	17
18		-	~~```~~
	18	+	```text
19	19		FirstName,Surname,DOB,Remarks
20	20		Jim,Weston,19560818,"Also known as ""The Butcher"""
21	21		Alice,Cooper,19760312,""
22	22		```
23	23
24	24		Apparently a header line is used here, as is quoting using double quotes.
25	25
26	26		Spreadsheet packages such as Microsoft Excel and OpenOffice/LibreOffice Calc are able to export to and import from this format. However, as Microsoft Excel may interpret some fields such as date fields differently depending on a user's OS locale, it may pay to find alternative ways of transferring data (e.g. using the [[FPSpreadsheet]] code).
27	27
28	28		## CSV and SDF
29	29
30	30		Delphi (and Free Pascal) have a very similar (but not identical) format, SDF. See [[SDF]] for more details.
31	31
32	32		## Implementations
33	33
34	34		### CsvDocument
35	35
36	36		[[CsvDocument]] is a robust implementation of both the RFC 4180 CSV format and the alternative Creativyst/Excel CSV format. It offers both line-based and document-based access. Recommended for use with FPC/Lazarus. See [[CsvDocument]].
37	37
38	38		### DelimitedText
39	39
40	40		TStringList offers the DelimitedText property. This parses a line of text out into separate fields. Note, however, that DelimitedText is supposed to be in SDF, a Delphi-specific format that is very much like CSV, but does not conform with RFC4180 completely.
41	41
42	42		Tips: when reading CSV data, set the StrictDelimiter property to true.
43	43
44	44		When writing out CSV data, set StrictDelimiter to false and output the DelimitedText property. One oddity is that e.g. tab characters are removed when writing out data using StrictDelimiter:=false
45	45
46	46		#### SDF format
47	47
48	48		See [[SDF\|SDF format]]
49	49
50	50		### SDFDataset
51	51
52	52		Free Pascal offers the [[TSdfDataSet]], which stores data in SDF format. Note: FPC 2.6.x and earlier store sdfdataset data in a format that is not completely CSV or completely SDF compatible.
53	53
54	54		As indicated, [[SDF]] differs from [[CSV]]. Depending on the flavour of CSV, this format may be close enough to what a reading application expects to function. If you need an RFC4180 compliant CSV format, use [[TCSVDataSet]] instead.
55	55
56	56		#### TSdfDataset and TFixedDataset sample files
57	57
58	58		Sample SDF file
59	59
60	60
61	61		Below is a sample database for TSdfDataset. Note that the first line has the names of the fields and that we are using commas as separators:
62	62
63		-	~~```~~
	63	+	```text
64	64		ID,NAMEEN,NAMEPT,HEIGHT,WIDTH,PINS,DRAWINGCODE
65	65		1,resistor,resistor,1,1,1,LINE
66	66		2,capacitor,capacitor,1,1,1,LINE
67	67		3,transistor npn,transistor npn
68	68		```
69	69
70	70		- Sample TFixedDataset file
71	71
72	72
73	73		Each record occupies a fixed amount of space, and if the field is smaller, spaces should be used to fill the remaining size.
74	74
75		-	~~```~~
	75	+	```text
76	76		Name = 15 chars; Surname = 15 chars; e_mail = 20 chars;
77	77		```
78	78
79	79		`Piet Pompies piet@pompies.net`
80	80
81	81		#### Using the datasets: example code
82	82
83	83		Sometimes it is useful to create the dataset and work with it completely in code, and the following code will do exactly this. Note some peculiarities of TSdfDataset/TFixedDataset:
84	84
85	85		- The lines in the database can have a maximum size of about 300. A fix is being researched.
86	86		- It is necessary to add the field definitions. Some datasets are able to fill this information alone from the database file.
87	87		- One should set FirstLineAsSchema to true, to indicate that the first line includes the field names and positions.
88	88		- The Delimiter property holds the separator for the fields. It will not be possible to use this char in strings in the database. Similarly it will currently not be possible to have line endings in the database because they mark the change between records. It's possible to overcome this by substituting the needed comma or line ending with another rarely used char, like \#. When showing the data on screen all \# chars could be converted to line endings and the inverse when storing data back to the database. The ReplaceString routine is useful here.
89	89
90	90		``` pascal
91	91		uses sdfdata, db;
92	92
93	93		constructor TComponentsDatabase.Create;
94	94		var
95	95		FDataset: TSdfDataset;
96	96		begin
97	97		inherited Create;
98	98
99	99		FDataset := TSdfDataset.Create(nil);
100	100		FDataset.FileName := vConfigurations.ComponentsDBFile;
101	101		FDataset.FileMustExist := false; //don't require existing csv file
102	102
103	103		// Not necessary with TSdfDataset:
104	104		// FDataset.TableName := STR_DB_COMPONENTS_TABLE;
105	105		// FDataset.PrimaryKey := STR_DB_COMPONENTS_ID;
106	106
107	107		// Adds field definitions
108	108		FDataset.FieldDefs.Add('ID', ftString);
109	109		//schema addition is needed so that sdfdataset writes out the csv header correctly:
110	110		FDataset.Schema.Add('ID');
111	111		FDataset.FieldDefs.Add('NAMEEN', ftString);
112	112		FDataset.Schema.Add('NAMEEN');
113	113		FDataset.FieldDefs.Add('NAMEPT', ftString);
114	114		FDataset.Schema.Add('NAMEPT');
115	115		FDataset.FieldDefs.Add('HEIGHT', ftString);
116	116		FDataset.Schema.Add('HEIGHT');
117	117		FDataset.FieldDefs.Add('WIDTH', ftString);
118	118		FDataset.Schema.Add('WIDTH');
119	119		FDataset.FieldDefs.Add('PINS', ftString);
120	120		FDataset.Schema.Add('PINS');
121	121		FDataset.FieldDefs.Add('DRAWINGCODE', ftString);
122	122		FDataset.Schema.Add('DRAWINGCODE');
123	123
124	124		// Necessary for TSdfDataset
125	125		FDataset.Delimiter := ',';
126	126		FDataset.FirstLineAsSchema := True;
127	127
128	128		FDataset.Active := True;
129	129
130	130		// Sets the initial record
131	131		CurrentRecNo := 1;
132	132		FDataset.First;
133	133		end;
134	134		```
135	135
136	136		When using [[TSdfDataSet]] directly be aware that RecNo, although it is implemented, does not work as a way to move through the dataset whether reading or writing records. The standard navigation routines like First, Next, Prior and Last work as expected, so you need to use them rather than RecNo.
137	137
138	138		If you are used to using absolute record numbers to navigate around a database you can implement your own version of RecNo. Declare a global longint variable called CurrentRecNo which will hold the current RecNo value. Remember that this variable will have the same convention as RecNo, so the first record has number 1 (it is not zero-based). After activating the database initialize the database to the first record with TSdfDataset.First and set CurrentRecNo := 1.
139	139
140	140		Example code:
141	141
142	142		``` pascal
143	143		{@@
144	144		Moves to the desired record using TDataset.Next and TDataset.Prior
145	145		This avoids using TDataset.RecNo which doesn't navigate reliably in any dataset.
146	146
147	147		@param AID Indicates the record number. The first record has number 1
148	148		}
149	149		procedure TComponentsDatabase.GoToRec(AID: Integer);
150	150		begin
151	151		// We are before the desired record, move forward
152	152		if CurrentRecNo < AID then
153	153		begin
154	154		while (not FDataset.EOF) and (CurrentRecNo < AID) do
155	155		begin
156	156		FDataset.Next;
157	157		FDataset.CursorPosChanged;
158	158		Inc(CurrentRecNo);
159	159		end;
160	160		end
161	161		// We are after the desired record, move back
162	162		else if CurrentRecNo > AID then
163	163		begin
164	164		while (CurrentRecNo >= 1) and (CurrentRecNo > AID) do
165	165		begin
166	166		FDataset.Prior;
167	167		FDataset.CursorPosChanged;
168	168		Dec(CurrentRecNo);
169	169		end;
170	170		end;
171	171		end;
172	172		```
173	173
174	174		### Data Export
175	175
176	176		Free Pascal/Lazarus database export functionality (e.g. TCSVExporter on the Data Export tab - requires installation of the [[lazdbexport]] package) offers CSV export functionality for [[TDataSet\|datasets]].
177	177
178	178		### Jan's CSV components
179	179
180	180		See [[JCSV (Jans CSV Components)]].
181	181
182	182		### ZMSQL
183	183
184	184		[[ZMSQL]] stores data in semicolon-delimited files (using SDF?).
185	185
186	186		---
187	187		From the Free Pascal wiki, page "CSV", as it stood on 2020-07-18 after a change by FTurtle, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history