CSV: what changed

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