Export to dbf
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
SeregaKR · 2018-06-22 05:09

Hello.
I would like to know if it is possible to save table in dbf format
(maybe using sfUser FileFormat).

As far as I can see now the way to save data from Excel table is to
parse through cells and copy data using TFpDbExport component. But I'm
not sure if it's the most efficient way to do this.

Other way it seems is described in section dataset export in the wiki,
but it is really vague.

Can you give any pointers in the right direction?

To clarify: I need to save excel file like the one attached to the
topic as dbf. Current solution used is office automation but it's not
ideal and it's done in other language.

All fields are known beforehand.

----------------------------------------------------------------------
kjteng · 2018-06-22 09:02

As far as I can remember TFpDbExport only export dbf to Spreadsheet
but not the other way round.

----------------------------------------------------------------------
SeregaKR · 2018-06-22 09:17

Quote from: kjteng on June 22, 2018, 11:02:59 amAs far as I can
remember TFpDbExport only export dbf to Spreadsheet but not the other
way round.
I see it now. Thank you. But the question still stands. What is a
correct way to save worksheet to dbf? Parse the cells and write
directly to dbf?
In wiki there is an example that reads from dbf and writes to Excel
using virtual mode but not the other way around.

----------------------------------------------------------------------
wp · 2018-06-22 09:19

Don't think of format sfUser - I requires you do know exactly the file
structure of dbf files.

I am attaching a (tested) example which creates the dbf file and its
fields on the fly, iterates through the cells of the worksheet and
exports to dbf.

----------------------------------------------------------------------
SeregaKR · 2018-06-22 09:24

Quote from: wp on June 22, 2018, 11:19:24 amDon't think of format
sfUser - I requires you do know exactly the file structure of dbf
files.

I am attaching a (tested) example which creates the dbf file and its
fields on the fly, iterates through the cells of the worksheet and
exports to dbf.

There is a small problem with encoding: it seems that it saves with
utf8, but I''ll fix it. Thank you!

----------------------------------------------------------------------
wp · 2018-06-22 09:44

Quote from: SeregaKR on June 22, 2018, 11:24:45 am[...] it saves with
utf8, but I''ll fix it. Thank you!
And you should also adjust the string lengths in the FieldDefs lines
of the string fields - I see that some strings are truncated in my
demo.

----------------------------------------------------------------------
SeregaKR · 2018-06-22 10:56

Corrected length of the character fields like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Dbf1.FieldDefs.Add('ot', ftString, 45);DBF works incorrectly with UTF8
so I added the line to set LanguageID to Russian:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Dbf1.LanguageID := $C9;And changed case like this (also added LazUTF8
in uses):
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i case
cell^.ContentType of          cctUTF8String: f.AsString :=
UTF8ToWinCP(cell^.UTF8StringValue);          cctNumber: f.AsFloat :=
cell^.NumberValue;          cctDateTime: f.AsDateTime :=
cell^.DateTimeValue;          else f.AsString :=
UTF8ToWinCP(AWorksheet.ReadAsText(cell));end;As a result DBGrid show
database incorrectly but now I can open the file in LibreOffice,
FoxPro and other dbf viewers.

----------------------------------------------------------------------
wp · 2018-06-22 11:53

Try this (just guessing...)
- Add "lconvencoding" and "dbf_dbffile" to "uses"
- Instead of UTF8ToWinCP call UTF8ToCP1251 in the cell-to-field
assignments
- Add this to the beginning of the ExportToDBF procedure:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
DbfGlobals.DefaultCreateCodePage := 1251; 
DbfGlobals.DefaultOpenCodePage := 1251;

----------------------------------------------------------------------
SeregaKR · 2018-06-22 12:01

Result is the same. DBGrid (the bottom one) displays text incorrectly
but file is good. But for me it;s good enough.

----------------------------------------------------------------------
wp · 2018-06-22 12:25

This works for me (i'm on CP 1252):
Add "lconvencoding" to "uses"In cell-to-field assignments use
UTF8ToCP1251 instead of UTF8ToWinCP (BUT: see note at end)Add this
methodCode: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.DbfGetTextHandler(Sender: TField; var AText: string; 
DisplayText: Boolean);begin  if DisplayText then    AText :=
CP1251ToUTF8(Sender.AsString);end;In "ExportToDBF", after "Dbf1.Open"
add this:Code: Pascal  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   for f in
Dbf1.Fields do    f.OnGetText := @DbfGetTextHandler;  NOTE:
You seem to be on CP1251. Therefore it should work also if you use
UTF8ToWinCP/WinCPToUTF8 instead of UTF8ToCP1251/CP1251ToUTF8

----------------------------------------------------------------------
SeregaKR · 2018-06-25 07:07

It seems that this line is needed in the end of ExportToDBF procedure.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Dbf1.Close;
After adding the code you suggested (procedure DbfGetTextHandler, and
"FOR cycle" after the line Dbf1.Open; )
DBGrid stopped showing any text whatsever (attached screenshot).

Maybe this type of code should be added to examples?

----------------------------------------------------------------------
wp · 2018-06-25 07:45

Here, the Dbf1 dataset must remain open after the conversion because
its records are still being displayed by the DBGrid.

Just for clarity, I am attaching the demo project again

----------------------------------------------------------------------
SeregaKR · 2018-06-25 07:51

my mistake. Thank you.
Works perfectly.

Moved Dbf1.Close to Form OnClose event

----------------------------------------------------------------------
wp · 2018-06-25 08:00

Quote from: SeregaKR on June 25, 2018, 09:51:12 amMoved Dbf1.Close to
Form OnClose event

It is not harmful, but even this is not necessary because the
destructor of any TDataset will close automatically. From (fpc
sources)/packages/fcl-db/src/base/dataset.inc:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i destructor
TDataSet.Destroy;var  i: Integer;begin  Active:=False;      // <---
This closes the dataset  FFieldDefs.Free;  FFieldList.Free;    ...