CSV: what changed

Revision 2 to 3. 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		-	~~`FirstName,Surname,DOB,Remarks`\~~
19		-	~~`Jim,Weston,19560818,"Also known as ""The Butcher"""`\~~
20		-	~~`Alice,Cooper,19760312,""`~~
	18	+	```
	19	+	FirstName,Surname,DOB,Remarks
	20	+	Jim,Weston,19560818,"Also known as ""The Butcher"""
	21	+	Alice,Cooper,19760312,""
	22	+	```
21	23
22	24		Apparently a header line is used here, as is quoting using double quotes.
23	25
24	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).
25	27
26	28		## CSV and SDF
27	29
28	30		Delphi (and Free Pascal) have a very similar (but not identical) format, SDF. See [[SDF]] for more details.
29	31
30	32		## Implementations
31	33
32	34		### CsvDocument
33	35
34	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]].
35	37
36	38		### DelimitedText
37	39
38	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.
39	41
40	42		Tips: when reading CSV data, set the StrictDelimiter property to true.
41	43
42	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
43	45
44	46		#### SDF format
45	47
46	48		See [[SDF\|SDF format]]
47	49
48	50		### SDFDataset
49	51
50	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.
51	53
52	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.
53	55
54	56		#### TSdfDataset and TFixedDataset sample files
55	57
56	58		Sample SDF file
57	59
58	60
59	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:
60	62
61		-	~~`ID,NAMEEN,NAMEPT,HEIGHT,WIDTH,PINS,DRAWINGCODE`\~~
62		-	~~`1,resistor,resistor,1,1,1,LINE`\~~
63		-	~~`2,capacitor,capacitor,1,1,1,LINE`\~~
64		-	~~`3,transistor npn,transistor npn`~~
	63	+	```
	64	+	ID,NAMEEN,NAMEPT,HEIGHT,WIDTH,PINS,DRAWINGCODE
	65	+	1,resistor,resistor,1,1,1,LINE
	66	+	2,capacitor,capacitor,1,1,1,LINE
	67	+	3,transistor npn,transistor npn
	68	+	```
65	69
66	70		- Sample TFixedDataset file
67	71
68	72
69	73		Each record occupies a fixed amount of space, and if the field is smaller, spaces should be used to fill the remaining size.
70	74
71		-	~~`Name = 15 chars; Surname = 15 chars; e_mail = 20 chars;`\~~
	75	+	```
	76	+	Name = 15 chars; Surname = 15 chars; e_mail = 20 chars;
	77	+	```
	78	+
72	79		`Piet Pompies piet@pompies.net`
73	80
74	81		#### Using the datasets: example code
75	82
76	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:
77	84
78	85		- The lines in the database can have a maximum size of about 300. A fix is being researched.
79	86		- It is necessary to add the field definitions. Some datasets are able to fill this information alone from the database file.
80	87		- One should set FirstLineAsSchema to true, to indicate that the first line includes the field names and positions.
81	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.
82	89
83	90		``` pascal
84	91		uses sdfdata, db;
85	92
86	93		constructor TComponentsDatabase.Create;
87	94		var
88	95		FDataset: TSdfDataset;
89	96		begin
90	97		inherited Create;
91	98
92	99		FDataset := TSdfDataset.Create(nil);
93	100		FDataset.FileName := vConfigurations.ComponentsDBFile;
94	101		FDataset.FileMustExist := false; //don't require existing csv file
95	102
96	103		// Not necessary with TSdfDataset:
97	104		// FDataset.TableName := STR_DB_COMPONENTS_TABLE;
98	105		// FDataset.PrimaryKey := STR_DB_COMPONENTS_ID;
99	106
100	107		// Adds field definitions
101	108		FDataset.FieldDefs.Add('ID', ftString);
102	109		//schema addition is needed so that sdfdataset writes out the csv header correctly:
103	110		FDataset.Schema.Add('ID');
104	111		FDataset.FieldDefs.Add('NAMEEN', ftString);
105	112		FDataset.Schema.Add('NAMEEN');
106	113		FDataset.FieldDefs.Add('NAMEPT', ftString);
107	114		FDataset.Schema.Add('NAMEPT');
108	115		FDataset.FieldDefs.Add('HEIGHT', ftString);
109	116		FDataset.Schema.Add('HEIGHT');
110	117		FDataset.FieldDefs.Add('WIDTH', ftString);
111	118		FDataset.Schema.Add('WIDTH');
112	119		FDataset.FieldDefs.Add('PINS', ftString);
113	120		FDataset.Schema.Add('PINS');
114	121		FDataset.FieldDefs.Add('DRAWINGCODE', ftString);
115	122		FDataset.Schema.Add('DRAWINGCODE');
116	123
117	124		// Necessary for TSdfDataset
118	125		FDataset.Delimiter := ',';
119	126		FDataset.FirstLineAsSchema := True;
120	127
121	128		FDataset.Active := True;
122	129
123	130		// Sets the initial record
124	131		CurrentRecNo := 1;
125	132		FDataset.First;
126	133		end;
127	134		```
128	135
129	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.
130	137
131	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.
132	139
133	140		Example code:
134	141
135	142		``` pascal
136	143		{@@
137	144		Moves to the desired record using TDataset.Next and TDataset.Prior
138	145		This avoids using TDataset.RecNo which doesn't navigate reliably in any dataset.
139	146
140	147		@param AID Indicates the record number. The first record has number 1
141	148		}
142	149		procedure TComponentsDatabase.GoToRec(AID: Integer);
143	150		begin
144	151		// We are before the desired record, move forward
145	152		if CurrentRecNo < AID then
146	153		begin
147	154		while (not FDataset.EOF) and (CurrentRecNo < AID) do
148	155		begin
149	156		FDataset.Next;
150	157		FDataset.CursorPosChanged;
151	158		Inc(CurrentRecNo);
152	159		end;
153	160		end
154	161		// We are after the desired record, move back
155	162		else if CurrentRecNo > AID then
156	163		begin
157	164		while (CurrentRecNo >= 1) and (CurrentRecNo > AID) do
158	165		begin
159	166		FDataset.Prior;
160	167		FDataset.CursorPosChanged;
161	168		Dec(CurrentRecNo);
162	169		end;
163	170		end;
164	171		end;
165	172		```
166	173
167	174		### Data Export
168	175
169	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]].
170	177
171	178		### Jan's CSV components
172	179
173	180		See [[JCSV (Jans CSV Components)]].
174	181
175	182		### ZMSQL
176	183
177	184		[[ZMSQL]] stores data in semicolon-delimited files (using SDF?).
178	185
179	186		---
180	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