[SOLVED] DBF and rare characters lazarus / 16-Databases ---------------------------------------------------------------------- Raul_ES · 2020-04-19 10:24 Hello, I have just started to program databases with Pascal. For a project I'm working on I decided to use dBase format (but probably will switch to sqllite or similar, I am open to hear your suggestions). I have an XLS file and I need to convert it to dbf. This can be achieved with the command (if you use LibreOffice): 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 soffice --invisible --convert-to dbf C:\mydata\example.xls or an online service like https://onlineconvertfree.com/convert- format/xls-to-dbf/ But I have the following problem when opening the result DBF with any other program. I get rare characters in strings where spanish characters are expected (ñ, accents...). Any clue of what I am doing wrong? When opening the result DBF the program (LibreOffice i.e) asks me to specify the character set codification, but none of the ones I try solves this issue. I attach the xls file that can be opened without problem. (I am not able to find its codification). thanks ---------------------------------------------------------------------- Raul_ES · 2020-04-19 10:25 The xls was to big to be attached. I paste the link: https://www.ine.es/daco/daco42/codmun/codmun11/11codmun.xls ---------------------------------------------------------------------- nouzi · 2020-04-19 10:53 hi Raul_ES see this https://stackoverflow.com/questions/22210834/convert-xls-to-dbf-with- libre-office-using-the-command-line ---------------------------------------------------------------------- wp · 2020-04-19 12:18 It is not needed to use any external programs to convert an xls file to dbf. You can use fpspreadsheet (available through Online-Package- Manager) and you can do the conversion in plain Pascal code - there is sample code in the fpspreadsheet installation. ---------------------------------------------------------------------- eljo · 2020-04-19 12:35 dbf is a previous of the last century technology so no unicode support. I haven't downloaded the xml file but I'll bet the character encoding is utf8 16 or something like that. You need to convert it to a code page that supports your characters during the conversion from xml. ---------------------------------------------------------------------- BrunoK · 2020-04-19 13:28 Quote from: eljo on April 19, 2020, 02:35:38 pm.. but I'll bet the character encoding is utf8 16 or something like that. You need to convert it to a code page that supports your characters during the conversion from xml. libreoffice calc suggests CP 850 : 'DOS Latin 1 Western Europe' for 11codmun.xls ---------------------------------------------------------------------- wp · 2020-04-19 14:09 Raul_ES, since you are new to Pascal I thought it is maybe more instructive to you if you have a working example to get started. There is an xls-to-dbf conversion example in the fpspreadsheet wiki, but it is maybe too complex for a beginner. Therefore I wrote the attached demo. It reads your excel file (if zipped it still fits into the forum upload limit), extracts fields from it and writes the cells to a dbf file. The dbf file is then displayed in a DBGrid. FPSpreadsheet converts strings to utf8, and I saw in this example that your Spanish characters appear correctly in the DBGrid. Therefore, I assume that the DBF-component works with UTF8 too, by default. I checked this with Laz trunk and Laz 2.0.8 / fpc 3.0.4 in Windows and Linux Min. No extra code page conversions seem to be needed suprisingly. Please note that you need to have the laz_fpspreadsheet package to compile this demo. Go to Online-Package-Manager (in menu "Package"), scroll down to FPSpreadsheet, open the node and check the line "laz_fpspreadsheet.lpk", click "Install". (Of course you can check the parent node "FPSpreadsheet instead, but you do not need the other packages for this demo). ---------------------------------------------------------------------- Thaddy · 2020-04-19 15:23 dBASE itself (tested dBASE 4 for DOS, which I have a license for) does not support that except for codepage, as explained above. It is questionable if support for such a legacy platform is really needed. It is simply a wrong choice of database engine unless working with legacy data. ---------------------------------------------------------------------- BrunoK · 2020-04-19 15:47 Quote from: wp on April 19, 2020, 04:09:23 pmNo extra code page conversions seem to be needed suprisingly. Code page is in fact CP 1252 (as reported by notepad++) and that is the default for TDBF. ---------------------------------------------------------------------- wp · 2020-04-19 17:20 I don't know what you want to say. When I load the dbf file generated by my demo above into LibreOffice Calc I get correct Spanisch characters when I select UTF8 in the import dialog. When I load the dbf file into Notepad++ and I switch to encoding UTF8 the detectable text patches have correct Spanisch charadters. When I load the dbf file into my hex editor I see two bytes at places where a spanish character is expected -> the created dbf file is UTF-8 encoded. However, when I import the dbf file into LibreOffice Base as dBase file the Spanish characters are displayed as two bytes. So, a "real" database program is confused by this file. My program transfers strings to the dbf file byte-by-byte, and for displaying it the file is read back without conversions again, so everything looks correct within the Lazarus infrastructure. When I query the property dbf.CodePage I get CP850. Therefore, when I replace the line 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             dbf.Fields[c].AsString := cell^.UTF8StringValue;   // (1) by 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             dbf.Fields[c].AsString := UTF8ToCP850(cell^.UTF8StringValue);   // (2) the strings are converted to CP850 before writing to the file. And this results in a valid dbase file, i.e. when I open it in LibreOffice Base the spanish characters are correct. However, when I load the file in Lazarus into a DBGrid they are not - because the grid expects strings to be utf8. So, another conversion from CP850 back to UTF8 is needed for usage of the file in Lazarus. ---------------------------------------------------------------------- lucamar · 2020-04-19 17:53 It has been quite some time since I used dBase tables so I might be mis-remembering but IIRC you have to set either Dbf.LanguageID or Dbf.DbfFile.UseCodePage (or both?) to get the data automagically translated from the file's encoding (which in this case is probably WIN1252, IBM437 or IBM850) to the encoding your app uses (most probably UTF-8). Can't help much more; it has really been a long time and FCL's TDbf is almost undocumented. ETA You  might also find this wiki page useful: Guide for usage of Tdbf component/ru. It's a little old and in Russian but the code is mostly self-explanatory and since it deals with Russian databases it shows a way to convert encodings. ---------------------------------------------------------------------- wp · 2020-04-19 22:18 Thanks for this information. I now modified my example from the previous post such that it writes a valid dbf file (regarding encoding) which is displayed correctly in the DBGrid. P.S. I added a short note to the wiki dbf tutorial: https://wiki.freepascal .org/Lazarus_Tdbf_Tutorial#Code_pages_and_string_encoding_issues ---------------------------------------------------------------------- Raul_ES · 2020-04-20 16:15 Thank you very much for your comments. regards ---------------------------------------------------------------------- Raul_ES · 2020-04-20 16:18 Thanks wp for your code. It has been very helpful. Quote from: wp on April 20, 2020, 12:18:07 amThanks for this information. I now modified my example from the previous post such that it writes a valid dbf file (regarding encoding) which is displayed correctly in the DBGrid. P.S. I added a short note to the wiki dbf tutorial: https://wiki.freepascal .org/Lazarus_Tdbf_Tutorial#Code_pages_and_string_encoding_issues ---------------------------------------------------------------------- Raul_ES · 2020-04-20 17:35 When I run wp's example and I click the conversion button I get the following error related to the dataset. I have converted  the project to CodeTyphon and I currently have the fpspreadsheet component installed. ---------------------------------------------------------------------- wp · 2020-04-20 20:23 I can reproduce this when I test the demo in Manjaro Linux. It seems to me that setting the Dbf.LanguageID is too Windows-specific. Near the top of the implementation section of the main unit, there is a constant DBF_LANGUAGEID = DbfLangID_ESP_850. Set it to 0, and the demo will run. In this case, the codepage defaults to 1252, Western Europe, which is maybe correct for you. ---------------------------------------------------------------------- Raul_ES · 2020-04-20 23:32 Thanks! now it works, I am using OpenSuse Tumbleweed. I'll check later under MS Windows. Spanish characters seem to be printed correctly. But I see that the city name field appears truncated. ---------------------------------------------------------------------- Raul_ES · 2020-04-20 23:44 For example, "Baños de Ebro/Mañueta" will appear as "Baños de Ebro/Mañ" "Iruña Oka/Iruña de Oca" as "Iruña Oka/Iruña d" The attachment is from the xls file. ---------------------------------------------------------------------- Raul_ES · 2020-04-20 23:56 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  begin        ftype := ftString;              // Iterate through all rows in this column and determine the              // longest string for the field size.           fsize := Length(cell^.UTF8StringValue);         for cc := FirstDataRowIndex + 1 to lastRow do                fsize := max(fsize, Length(cell^.UTF8StringValue));end; maybe there's something wrong here? 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 dbf.TableLevel := 4;I think I read that dbase 4 level had a limit stablished to 10 character per string field. But switching to level 7 and increasing the filed size limit to 32 does not solve the problem. ---------------------------------------------------------------------- wp · 2020-04-21 00:05 I thought it would become wider than required, so I did not care... Try to use UTF8Length() instead of Length() in both occurances. I think it is in unit LazUtf8. ---------------------------------------------------------------------- wp · 2020-04-21 00:14 No - this makes it shorter, as expected. The problem is that "cell" is not updated as the iteration runs down the column. Stupid mistake... Use 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 var  cpStr: String;...    // Create temporary dbf component    dbf := TDbf.Create(nil);    try      // set filename parameters in dbf file    dbf.FilePathFull := ExtractFilePath(DbfFileName);      dbf.TableName := ExtractFileName(DbfFileName);      dbf.TableLevel := 4;      dbf.LanguageID := DbfLanguageID;       cp := LangID_To_CodePage[DbfLanguageID];      if cp = 0 then cp := DbfGlobals.DefaultCreateCodePage;      cpStr := 'cp' + IntToStr(cp);       // Now we must create the fields of the dbf component.      // We assume that the field names are in the cells specified by HeaderRowIndex      for c := 0 to lastCol do      begin        // Read field name from header (title row)        fname := worksheet.ReadAsText(HeaderRowIndex, c);        // If this string contains non-ASCII characters the string encoding        // must be converted. Unfortunately dbf.CodePage does not return        // a valid result at this moment, so, we must determine it outselves:      fname := ConvertEncoding(fname, 'utf8', cpStr);         // Read field data type from row specified by FirstDataRowIndex        cell := worksheet.FindCell(FirstDataRowIndex, c);        if cell = nil then        raise Exception.Create('Empty cells not allowed.');        fsize := 0;  // field size for non-string fields        case cell^.ContentType of          cctUTF8String:            begin            ftype := ftString;              // Iterate through all cells of this column and determine the              // longest string (--> field size).              fsize := Length(cell^.UTF8StringValue);              // or UTF8Length(..) to be exact, but a tiny bit slower            for r := FirstDataRowIndex + 1 to lastRow do              begin                cell := worksheet.FindCell(r, c);                fsize := max(fsize, Length(cell^.UTF8StringValue));  // or here UTF8Length(...), too              end;            end;          cctNumber:            ftype := ftFloat;          cctDateTime:          ftype := ftDateTime;          cctBool:            ftype := ftBoolean;          else            raise Exception.Create('Field type not supported.');        end;        // Create the field def according to the information read from the xls        dbf.FieldDefs.Add(fname, ftype, fsize);      end; ---------------------------------------------------------------------- Raul_ES · 2020-04-21 13:26 Thank you very much wp, runs smoothly. I am playing with your code to see all what I can do. I'll try to adapt it to open ODF files and XML. Another exercise I will try later is to open a large XML database and save it into a dbf. one thing I don't undersand is the usage of cell^ , why a pointer? I must admit that pointers are painfull and scary to me. regards, ---------------------------------------------------------------------- wp · 2020-04-21 13:57 Hmmm... Probably because one of the first authors of fpspreadsheet decided to use a balanced tree to store the cells, and this data structure only can work with pointers internally. Of course it would have been possible to change this to classes, but I think some things would be easier with them, but others would be more difficult. And now it is too late to change anything because it would break the code of many users. Most of the time you do not need the "cell" in FPSpreadsheet, because access to it is encapsulated in numerous methods of the worksheet. I only used it here because having the cell pointer it the easiest way to determine which kind of content (string, number, date, etc). is stored in a cell. You may want to look at the fpspreadsheet documentation to learn more about it: https://wiki.lazarus.freepascal.org/FPSpreadsheet You can convert OpenDocument files with the same program. You only must add the ODS reader unit (fpsOpenDocument) to the uses clause, and use the parameter "sfOpenDocument" in the ReadFromFile call (instead of sfExcel8). ---------------------------------------------------------------------- valdir.marcos · 2020-04-22 04:08 Quote from: Raul_ES on April 20, 2020, 07:35:12 pmI have converted  the project to CodeTyphon and I currently have the fpspreadsheet component installed.Be careful: https://jonlennartaasenden.wordpress.com/2015/01/03/free-sparta-and- codetyphoon-violates-gpl/ ---------------------------------------------------------------------- Raul_ES · 2020-04-22 12:18 Quote from: wp on April 21, 2020, 03:57:44 pmYou may want to look at the fpspreadsheet documentation to learn more about it: https://wiki.lazarus.freepascal.org/FPSpreadsheet You can convert OpenDocument files with the same program. You only must add the ODS reader unit (fpsOpenDocument) to the uses clause, and use the parameter "sfOpenDocument" in the ReadFromFile call (instead of sfExcel8). Thanks again, fpspreadsheet is very poweful. I've read that it supports a wide range of formats, even csv, that I will also going to need. ---------------------------------------------------------------------- Raul_ES · 2020-04-22 12:20 Quote from: valdir.marcos on April 22, 2020, 06:08:46 amQuote from: Raul_ES on April 20, 2020, 07:35:12 pmI have converted  the project to CodeTyphon and I currently have the fpspreadsheet component installed.Be careful: https://jonlennartaasenden.wordpress.com/2015/01/03/free-sparta-and- codetyphoon-violates-gpl/ oooouch... that article is from 2015. I am wondering about the actual situation. Thanks for the advice, I'll do some research. ---------------------------------------------------------------------- Raul_ES · 2020-04-22 12:36 please check this. Is an xls without column header, so I added the line for the column names. (I added two lines to make it work with your code). The fixed row does not process correctly the symbols. Another question! how can I make the fixed columd to show the record number? I remember this was easy to achieve with an Tstringgrid, but I'm not able to find the right property of dbgrid. I attach a reduced version of the xls file that is beneath the 250kb limit. regards ---------------------------------------------------------------------- wp · 2020-04-22 13:42 Quote from: Raul_ES on April 22, 2020, 02:36:12 pmThe fixed row does not process correctly the symbols.I could imagine that this is dbase specification - field names can only be ASCII (< #128), in the same way as only ASCII is allowed for Pascal variable and function names. Quote from: Raul_ES on April 22, 2020, 02:36:12 pmAnother question! how can I make the fixed columd to show the record number? I remember this was easy to achieve with an Tstringgrid, but I'm not able to find the right property of dbgrid. The left most column, the "indicator column", is not intended to contain user data; it only shows the indicator triange of the selected record. I don't know of an easy way to display the record number. Maybe add a dummy column and owner-draw it. Or add a field to the dbf file with the sole purpose to contain the "record number". Or maybe: Don't use a DBGrid at all, read the entire table and write its contents into a TStringGrid; here you have more freedom for adding olumns. P.S. You should be aware that the "record number" is not a universal concept. There are database engines which doe not support something like a record number. And even with the DBGrid it is not clear whether the record number would be correct because in case of a large databae, the grid reads only a limited count of records. So, the problem is: does the record number refer to the entire full dataset (which maybe is not known by the grid), or only to the buffered data? Maybe the old-fashioned dbf files are less of a problem, but it is maybe a good idea to not get used to the record number concept. ---------------------------------------------------------------------- Raul_ES · 2020-04-27 16:52 Thanks wp, I see your point. Rather than "record number" maybe I should have just said "row number". A row number relative to the data loaded into the grid instead to the database "as a whole". regards