5 Minute Snack: Accessing comma-separated files in Delphi
Reading comma-separated or tab-separated data from text files (also known as “flat files”) is a rather common task that developers have to deal with. For that reason most 3rd party frameworks offer methods to import data into their grids and databases.
I was really surprised how easy and flexible it was to read this kind of data with Delphi. You are even able to do all the work during design-time already! Furthermore, digging a little deeper, you can even edit the data on the fly for visualization and other calculations.
A few words of explanation to the title of the post. Blog posts marked “5 minute snack” will offer neat tid-bits about programming that you need only 5 minutes to dive into, but they will help you a great deal and save you hours of work. German readers will certainly get the title as we have a very popular “5 minute meals” in Germany. Just add hot water, let it sit for 5 minutes and you have a meal.
The problem with Delphi is sometimes that you do not realize that these features are right in the box. Googling for it definitely will not help. In .NET on the other hand you will find plenty of CSV-frameworks or even a Linq for CSV.
Let’s focus on what we need: We need a means to load a csv file and browse the data. Maybe transform the data for additional calculations or aggregations.
To get you started: FireDAC is where you need to look. The component we need is called TFDBatchMoveTextReader . It offers not only a means to read any kind of text data, it also offers methods to analyse the data for you and derive the field values automatically.
In my example I have an Excel export of data that was recorded of a solar energy plant. For every day the energy was recorded. I never even looked at the structure of the file. Furthermore, we need a dataset that will store the data from the file. Thus, drop a TDataset-descendant of some sort on the form. I used TFDMemTable . You will also need one more component that transfers or writes the data from the text file to the dataset, called TFDBatchMoveDataSetWriter . Finally, the component that actually offers the methods to move the data.
You really simply need to drop these components on the form and link them to each other, i.e. you need to connect the writer to the dataset, the reader to the text file and the move component to both the writer and the reader.
The TFDBatchMoveTextReader does allow you to set up certain things if you want to, but it is not necessary. FireDAC does a pretty good job at guessing the actual structure of the data. In my case, I needed to adjust the ShortDateFormat and several seperators as FireDAC was unable to guess the German date specification on a Windows system with English localization enabled. Thus, I needed to change “yyyy/MM/dd” to “dd.MM.yyyy”, for example.
Here you see the connection of TFDBatchMoveDataSetWriter to the Table.
The TFDBatchMove offers lots of properties to map data, to configure the automatic analysis of the data and so on. We just connect Reader and Writer and leave all the defaults.
Finally, so that you can “see” something, add a TDatasource and a TDBGrid to the form (and connect them to the data accordingly).
The most surprising thing for me was that you can already use the components during design-time. This allows you to set up the grid with actual data like you are used to in Delphi. Do not get me wrong, I do know that there are lots of 3rd party components out there, but the key point I want to make here is the fact that this is all included “in the box”.
FireDAC will also require a WaitCursor-component that you simply need to drop as well.
When you open the context menu for the batch move component, you have the means to execute or guess the text format. In case Execute does not work with the default values, then you might need to guess first.
As soon as the Execute action is successful, the values are already shown in the grid. Even more fascinating, field types are recognized. Thus, setting the index field to “Datum” (date) will sort all the data according to their date.
Just like that.
Zero code. No frameworks to learn, no complicated queries. If you have an existing database application in place, you can easily use this approach to offer a comfortable import for this kind of data to your customers. Of course, FireDAC is not restricted to text data and can offer the same kind of “service” for other databases, but from experience I know, that a lot of data is still exchanged using “flat files”.
Making the transition to code is also very easy as the methods are called “Execute” and “GuessFormat”.
The next step will be to make changes to the data using queries or other means. Even that can be done very easily as FireDAC offers LocalSQL. But that will be a topic for another snack…