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…
Great article. Helps me a lot. Thank you!
Interesting, thanks. I have one problem though – could you help?
The Batchmove has incorrectly guessed one of my columns as Date, when in fact it’s a string column. So I’m getting “Bad Text Value […] format for mapping item. “2014/2015 is not a valid date”.
Where exactly can I tell it to expect string, not date. I’ve looked at the BatchMove.Mappings – and there’s no data type there. Nor are there any actual fields in the Dataset to alter.
Thanks in advance,
Stuart
Stuart,
if you know of what type your data is, do not guess. My answer might sound oversimplified, but you can use Guess to get started during design time and have it auto-generate the mappings. Then edit those mappings and execute accordingly. I mentioned “guess” in this post to get started “quick and dirty” and if you do not know what kind your data is.
In order to make sure that the date types are recognized correctly, you need to look at the Reader component. That component has a property named “DataDef” that allows you to set up all the things properly. As I live in Germany, I also have to make changes to the decimal point and thousand seperator e.g. if the data comes from the US.
I hope this will get you started.
Thank you Holger – that’s exactly what I was looking for, problem solved.
Thanks for a very useful article,
Regards
Thank you for this articel. Unfortunately it is not working in my RAD Studio 7 IDE, although I have checked three times all the steps you describe. The Error message is [FireDAC][Comp][DS]-206. It says that it is impossible to open the Table, and that TFDMemTable would need a DataTable or DataView Object. In your example, as far as I understand it, TFDMemTable is not changed, it is just selected in the properties of TDataSource and TFDBatchMoveSetWriter. Can you tell from the description what might have gone wrong? Thanks a lot, Volker
Volker, that is correct as the table has no field information. Execute the batch, add the fields to the memory table and then activate it. It is important that the reader actually already “pumped” something so that the memory table has some data. TClientDataset behaves the same way. Just consider that the table needs a structure. The structure is delivered by the execution of the batch process and not before that. Adding the fields during design-time when you executed the batch can help you with that. However, if you want to be independent of a structure you need to open the table at the right time during run-time as well.
Dear Holger, thanks for the quick response. I actually tried to execute the batch, but it is not working. I am getting the error message quoted in my first post above. I have connected Batch to Reader and Writer, Writer to Table, Reader to CSV-File, Data Source to Table and DBGrid to Data Source.
Disconnect the datasource. It might be that the datasource tries to open the table. Furthermore, start with reader, writer and batch … build step-by-step to determine the cause. There might be version differences in play as I used Delphi 10.1 Berlin Update 1.
Hi,
As well as what Holger said, did you try the “Guess” option first?
Regards
Yes, I have tried the Guess option first. And I have disconnected the datasource. Still the same error message… And the table editor is empty. And I have built up the vcl application in exact the same order as described in your post. I’ll ask the Embarcadero Key Account if Version XE7 might have a bug in the FireDAC components… Would it be possible to share a working sample project, so I can see if this is running on my machine?
Please use the contact form. Be sure to include your email address. I will provide my sample project. Download area might be something to look into this weekend…
Just got the reply from Embarcadero: It is a version problem, XE7 does not have the required components. Thanks for all your answers anyway.
I get the same error but I am on 10.1. Do you have any ideas why? -206. cannot open dataset.
I’m having trouble saving the configuration, I wonder if you would have a moment to help me? I am saving the TextReader component once I have it configured correctly. So I don’t want to use the Guess Format function
Example :
object TFDBatchMoveTextReader
FileName = ‘Test.csv’
DataDef.Fields =
DataDef.Delimiter = ‘”‘
DataDef.Separator = ‘,’
DataDef.RecordFormat = rfCustom
DataDef.WithFieldNames = False
DataDef.FormatSettings.ShortDateFormat = ‘dd-mmm-yyyy’
end
However when I try to use it everything works but I only get data in the first field, all the others fields are blank. The OnWriteValue shows that record is not being parsed into different fields, hence only the first field having data.
Are you saving the configuration? If so how are you doing it?
Many thanks
Steve
The actual DataDef fields from the example above are not showing, I suspect because of the greater than and less than characters.
item
FieldName = ‘Name’
DataType = atString
FieldSize = 5
end
item
FieldName = ‘Address 1’
DataType = atString
FieldSize = 6
end
item
FieldName = ‘Address 2’
DataType = atString
FieldSize = 6
end
item
FieldName = ‘Date’
DataType = atDate
FieldSize = 10
end
Found the problem, streaming the component and back again this way doesn’t restore chars properly. So where we save DataDef.Separator = ‘,’ it doesn’t restore it properly when you just stream it back. The default separator is ‘;’ so it didn’t chop up my CSV file. In the end I used the JEDI jvFormStorage component and manually converted chars to integers and back again. Nothing to do with FireDac but I thought I’d note what I found here in case any one else has a similar problem.
Tested this in Delphi Berlin, and works like a charm in design mode, populating all columns and rows in a TDBgrid. However, in runtime, when doing Batch.execute, only the header row and first column of the DBgrid is filled with data. All other fields are blank. what am I missing?
Seems like I found the problem. In runtime, batch.Guessformat should be called before Batch.execute
Yes, in order to execute the import, you need to guess the format to use before that.
Hi Holger,
Hallo van ‘n sonnige Suid-Afrika.
I am using Delphi’s RAD XE7 Architect.
Your csv info is excellent.
At age 72 I am battling a bit (stupid old man).
Please let me have your sample project.
Kind regards and thanks.
Jan Fourie
This topic seems to be one of the rare examples where Lazarus proves to be superior to Delphi in that its TStringGrid class provides an additional LoadFromCsvFile method!