How to connect a CSV file as a DataSet in Delphi

Probably many have encountered the problem of using a CSV file as a DataSet in Delphi.

Let's have a look at  one of the solutions to this problem. We will connect the CSV file via TADOQuery and build a report from this data in FastReport VCL.

Create a project and add the following components.

1
2
3
4
5
6
7
8

OHLC_Query: TADOQuery;
OHLC_Source: TDataSource;
OHLC_DB: TfrxDBDataset;
frxReport1: TfrxReport;
frxDesigner1: TfrxDesigner; 
frxChartObject1: TfrxChartObject;
ButtonShowReport: TButton;
ButtonDesignReport: TButton;

To connect to the CSV file, we will use the OHLC_Query: TADOQuery component.

Set it up by setting the ConnectionString property:

1

OHLC_Query.ConnectionString :=’Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\;Extended Properties="text;";Persist Security Info=False’

Next, you will also need to write your Schema.ini file in accordance with the documentation on the Microsoft website:

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15

And add it to the project folder.

Let's analyze the example of our demo CSV file (EURUSD_200201_200410.csv), it has the following structure:

Data is separated using the “;”

The data is presented in a more visual form, they have 9 columns:

Let's create a text file and save it as schema.ini

This file should have the following structure according to the documentation on the Microsoft website:

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

[EURUSD_200201_200410.csv]
ColNameHeader=True 
Format=Delimited(;) 
DecimalSymbol=.
TextDelimiter='
CharacterSet=ANSI 
DateTimeFormat=yyyymmdd
Col1=TICKER char
Col2=PER integer 
Col3=DATE date 
Col4=TIME char
Col5=OPEN float
Col6=HIGH float 
Col7=LOW float
Col8=CLOSE float
Col9=VOL integer

Set the necessary settings for OHLC_Source and OHLC_DB:

1
2
3

OHLC_Source.DataSet := OHLC_Query;
OHLC_DB.DataSource := OHLC_Source;
OHLC_DB.UserName := 'OHLC';

Create a template and save it as DemoCSV.fr3

And connect the DataSet to the template

Next, add the ButtonDesignReportClick, ButtonShowReportClick, and FormCreate events:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

procedure TFormDemoCSV.ButtonDesignReportClick(Sender: TObject);
begin
 frxReport1.DesignReport;
end;
 
procedure TFormDemoCSV.ButtonShowReportClick(Sender: TObject);
begin
 frxReport1.ShowReport();
end;
 
procedure TFormDemoCSV.FormCreate(Sender: TObject);
begin
 frxReport1.LoadFromFile('./DemoCSV.fr3')
end;

Launch applications

When you click on the ButtonShowReport button, a report is built.

Congratulations, you included the CSV file as a DataSet in Delphi and built a report from this data in FastReport VCL!

Download link: DemoCSV.zip