Recent

Author Topic: Export to dbf  (Read 3469 times)

SeregaKR

  • New member
  • *
  • Posts: 35
Export to dbf
« on: June 22, 2018, 07:09:21 am »
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.

« Last Edit: June 22, 2018, 09:22:16 am by SeregaKR »

kjteng

  • Full Member
  • ***
  • Posts: 157
Re: Export to dbf
« Reply #1 on: June 22, 2018, 11:02:59 am »
As far as I can remember TFpDbExport only export dbf to Spreadsheet but not the other way round.

SeregaKR

  • New member
  • *
  • Posts: 35
Re: Export to dbf
« Reply #2 on: June 22, 2018, 11:17:43 am »
As 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.
« Last Edit: June 22, 2018, 11:24:08 am by SeregaKR »

wp

  • Hero Member
  • *****
  • Posts: 5992
Re: Export to dbf
« Reply #3 on: June 22, 2018, 11:19:24 am »
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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 35
Re: Export to dbf
« Reply #4 on: June 22, 2018, 11:24:45 am »
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.

There is a small problem with encoding: it seems that it saves with utf8, but I''ll fix it. Thank you!
« Last Edit: June 22, 2018, 11:29:53 am by SeregaKR »

wp

  • Hero Member
  • *****
  • Posts: 5992
Re: Export to dbf
« Reply #5 on: June 22, 2018, 11:44:22 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 35
Re: Export to dbf
« Reply #6 on: June 22, 2018, 12:56:44 pm »
Corrected length of the character fields like this:
Code: Pascal  [Select]
  1. Dbf1.FieldDefs.Add('ot', ftString, 45);
DBF works incorrectly with UTF8 so I added the line to set LanguageID to Russian:
Code: Pascal  [Select]
  1. Dbf1.LanguageID := $C9;
And changed case like this (also added LazUTF8 in uses):
Code: Pascal  [Select]
  1. case cell^.ContentType of
  2.           cctUTF8String: f.AsString := UTF8ToWinCP(cell^.UTF8StringValue);
  3.           cctNumber: f.AsFloat := cell^.NumberValue;
  4.           cctDateTime: f.AsDateTime := cell^.DateTimeValue;
  5.           else f.AsString := UTF8ToWinCP(AWorksheet.ReadAsText(cell));
  6. end;
As a result DBGrid show database incorrectly but now I can open the file in LibreOffice, FoxPro and other dbf viewers.

wp

  • Hero Member
  • *****
  • Posts: 5992
Re: Export to dbf
« Reply #7 on: June 22, 2018, 01:53:24 pm »
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]
  1.   DbfGlobals.DefaultCreateCodePage := 1251;
  2.   DbfGlobals.DefaultOpenCodePage := 1251;

Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 35
Re: Export to dbf
« Reply #8 on: June 22, 2018, 02:01:35 pm »
Result is the same. DBGrid (the bottom one) displays text incorrectly but file is good. But for me it;s good enough.

wp

  • Hero Member
  • *****
  • Posts: 5992
Re: Export to dbf
« Reply #9 on: June 22, 2018, 02:25:12 pm »
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 method
Code: Pascal  [Select]
  1. procedure TForm1.DbfGetTextHandler(Sender: TField; var AText: string;
  2.   DisplayText: Boolean);
  3. begin
  4.   if DisplayText then
  5.     AText := CP1251ToUTF8(Sender.AsString);
  6. end;
  • In "ExportToDBF", after "Dbf1.Open" add this:
Code: Pascal  [Select]
  1.   for f in Dbf1.Fields do
  2.     f.OnGetText := @DbfGetTextHandler;  
    NOTE:
    You seem to be on CP1251. Therefore it should work also if you use UTF8ToWinCP/WinCPToUTF8 instead of UTF8ToCP1251/CP1251ToUTF8
    Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

    SeregaKR

    • New member
    • *
    • Posts: 35
    Re: Export to dbf
    « Reply #10 on: June 25, 2018, 09:07:29 am »
    It seems that this line is needed in the end of ExportToDBF procedure.
    Code: Pascal  [Select]
    1. 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

    • Hero Member
    • *****
    • Posts: 5992
    Re: Export to dbf
    « Reply #11 on: June 25, 2018, 09:45:22 am »
    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
    Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

    SeregaKR

    • New member
    • *
    • Posts: 35
    Re: Export to dbf
    « Reply #12 on: June 25, 2018, 09:51:12 am »
    my mistake. Thank you.
    Works perfectly.

    Moved Dbf1.Close to Form OnClose event

    wp

    • Hero Member
    • *****
    • Posts: 5992
    Re: Export to dbf
    « Reply #13 on: June 25, 2018, 10:00:44 am »
    Moved 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]
    1. destructor TDataSet.Destroy;
    2. var
    3.   i: Integer;
    4. begin
    5.   Active:=False;      // <--- This closes the dataset
    6.   FFieldDefs.Free;
    7.   FFieldList.Free;  
    8.   ...
    Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10