Version: 1.1.3 (using KDE KDE 3.5.8) Installed from: Ubuntu Packages OS: Linux 1. take a csv file with '2007-10-28 20:55:13' -like date/time values in some columns 2. use import table from the file menu 3. import is successful 4. check the last colums: in rows where the date/time values are NULLs, fields got shifted to the left. Independent from csv separator character. Independent from quoting the fields in the csv. If developers contact me I send a sample csv file that triggers the bug - I don't intend to upload the file here.
Upon attempting to import a table of type .CSV as described in this bug, the table to be imported is correct as seen in Kexi's import preview view, but incorrect after the actual import is done. There are three errors I have noted on import, which are not present in Kexi's import preview view: 1. Each column's data type is correct in preview view, but all the columns of type "date" became converted to type "text" upon import. 2. Columns of type boulean (i.e. check box yes/no), get treated on import as being "floating point number". In the preview view as well, this is the only column which had a questionable data type. In the preview view, for data type there is no option for "boulean". The preview view had interpreted my boulean columns as being of type "number", because in the CSV file exported from OO Base, all the checkboxes in the boulean column had been converted to either "0" or "1". So I allowed the preview category "number" to remain, and on import it was seen to have been categorized as of type "floating point number". 3. I have the same experience reported in this bug, that null cells of type "date" get filled in by moving data from columns to the right, so that all the data ends up in the wrong cell and the wrong column. (But for some reason this does not carry over to succeeding rows. That is, the data starts correct on each row, but as soon as an empty cell is encountered it gets wrongly filled in by data to the right in the same row, thus making all the data in the rest of the row incorrect. (i.e. in the wrong column).
I've tried to reproduce anything you say. What version of Kexi do you have? The version in the svn repository (1.1.3-post) contains at least fix for your 2nd issue. Please update, or request an update from your OS vendor. Also I've been able to import the following file with no problems: 1,"2007-10-28 20:55:13","abc" 2,"2008-11-29 21:56:14","cde" The table created have then 3 columns: integer, date/time, text.
I've received report on similar issues: http://bugs.kde.org/show_bug.cgi?id=147809 and can reproduce problem with data of type date being moved from column to column (http://bugs.kde.org/attachment.cgi?id=22938&action=view). Looking at the problem.
SVN commit 805884 by staniek: CSV Import Dialog - fix setting data types for columns, previously text type was used instead - fix autodetecting and importing of floating-point values in "E scientificnotation" - output null values to fields if value coversion failed (e.g. text-to-date); without this subsequent values are written into the wrong columns - added "Date format" import option with possible values: auto, DMY, YMD, MDY BUG: 147809 BUG: 151478 M +36 -14 kexicsvimportdialog.cpp M +2 -2 kexicsvimportdialog.h M +76 -21 kexicsvimportoptionsdlg.cpp M +14 -3 kexicsvimportoptionsdlg.h WebSVN link: http://websvn.kde.org/?view=rev&revision=805884
Thank you for your efforts, I do deeply appreciate it. Unfortunately, the fixes have not appeared in the version which I have just downloaded today and tested. I have downloaded it from the Kexi official website at: http://kexi-project.org/wiki/wikiview/index.php@Packages.html#Ubuntu This is the package that is made for Ubuntu distributions. It is Kexi version 1.1.3 (KOffice 1.6.3) (Using KDE 3.5.9). I have just tried an import using it, and note the same problems as previously: everything looks fine in the import preview window (except the boulean column which shows as being of type "number"). But when I do the import, the five date columns in my .CSV--which showed up correctly as date columns in the import preview window--became changed into "text" columns. And any null values in those columns got filled in by migrating data over from columns to the right. And the "boulean" (yes/no checkbox) columns got converted to "floating point number" columns. So in sum, the three bugs which I reported in my first comment above, still remain in the version I have downloaded. Was I perhaps meant to download the new version from a different place? You mention "the version in the svn repository (1.1.3-post)". Is 1.1.3-post different from 1.1.3? If so, where do I get it? Please give the exact link.
Whenever I mention SVN version, I mean compiling Kexi. So first uninstall Kexi and its drivers, then compile it using these instructions: http://kexi-project.org/wiki/wikiview/index.php@UsingSubversion.html Unfortunately, Ubuntu is not Windows in terms of ease of deployment - I cannot provide pacakge that will work on every or most Linux computers. Alternatively please ask the Ubuntu team to deliver pacakge for you, if you cannot compile. But this will mean you cannot cooperate with us fully and get updates quickly. So better - learn ow to compile and only then you'll get the real power of Open Source.
I'll compile it, that's just fine. If there are instructions, I'm sure it will be clear enough to do. I'd like to cooperate with you fully and get updates quickly, as you have said. 1. For doing the uninstall of Kexi: I had installed it from the Kexi official site. For the uninstall, can I just do it through Synaptic Package Manager? I have gone there to Synaptic to look, and it is indeed showing kexi 1:1.6.3-4Ubuntu7 as installed. By uninstalling that listing from Synaptic, will I get this job done? 2. As for the compilation instructions, I tried going to the site http://kexi-project.org/wiki/wikiview/index.php, but it gives the message: Object not found! The requested URL was not found on this server. If you entered the URL manually please check your spelling and try again. If you think this is a server error, please contact the webmaster. Error 404 kexi-project.org Fri May 9 22:41:08 2008 Apache/2.0.55 (Ubuntu) DAV/2 PHP/5.1.2 mod_ssl/2.0.55 OpenSSL/0.9.8a Is there a different url by which I could get to the compilation instructions? And I guess the instructions will specific for my Ubuntu Hardy distribution i.e. the file download etc whatever I need to get, it will be for Ubuntu Hardy right.
Thanks for your interest. Go to http://kexi-project.org/wiki/wikiview/index.php@UsingSubversion.html not http://kexi-project.org/wiki/wikiview/index.php. We've recently migrated out of buggy php scripts. Have to live with the inconvenience until Mediawiki engine comes.
SVN commit 806092 by staniek: Ported r805884 to Kexi 2.0: CSV Import Dialog - fix setting data types for columns, previously text type was used instead - fix autodetecting and importing of floating-point values in "E scientific notation" - output null values to fields if value coversion failed (e.g. text-to-date); without this subsequent values are written into the wrong columns - added "Date format" import option with possible values: auto, DMY, YMD, MDY CCBUG:147809 CCBUG:151478 M +36 -14 kexicsvimportdialog.cpp M +2 -2 kexicsvimportdialog.h M +64 -19 kexicsvimportoptionsdlg.cpp M +14 -3 kexicsvimportoptionsdlg.h WebSVN link: http://websvn.kde.org/?view=rev&revision=806092
Please pardon my ignorance on this-- I could not locate the site you have given here: Go to http://kexi-project.org/wiki/wikiview/index.php at UsingSubversion.html not http://kexi-project.org/wiki/wikiview/index.php. Tried hard though. Does this part "at UsingSubversion.html" belong as part of the url perhaps? It didn't seem to work with that either. I tried googling the url +/- the suffix you have given ("at UsingSubversion.html"). But nothing worked. Please explain further how to get to the link you have given.
Replace "at" with "@" sign. This is part of url. see http://bugs.kde.org/show_bug.cgi?id=151478#c8
In other words, use http://kexi-project.org/wiki/wikiview/index.php%40UsingSubversion.html
I am going through the instructions. Two questions at this point: 1. Please give the exact link to the Kexi file which needs to be downloaded and compiled. I see links above to svn revision sites: http://websvn.kde.org/?view=rev&revision=805884 http://websvn.kde.org/?view=rev&revision=806092 And at each of these sites there are multiple pathways listed at the bottom of the page. Which is the actual file to be compiled? 2. In the instructions on this site, http://kexi-project.org/wiki/wikiview/index.php%40UsingSubversion.html, there are multiple options for how to move ahead. A) "Short Version" B) "Building from Trunk" C) "Building Kexi on Debian" (Is this a complete pathway unto itself, which does not require the above two pathways? Or is it a sort of addendum to be used by Debian users (like me), in addition to A &/or B? "However, if you want to build your own Debian packages from Subversion, then you will need at least: apt-get install debhelper cdbs" It says "at least". Will this be sufficient? Or is something more than this needed?
For installing "subversion", do I need to go through all the gyrations to install "subversion-1.4.6.tar.gz" with its separate but required "dependency package" tarball (which I have been unable to find), or can I just do the following: Debian GNU/Linux Just run 'apt-get install subversion'. This Debian subversion package is version 1.4.2dfsg1-2. The 1.4.6 is not yet labeled as a stable release in the package version. Is this package good enough, or do I need to use 1.4.6 and compile source code directly. (I hope the package is good enough.)
Seems you have real problems with compiling anything. So please visit #kexi and #koffice channel and request help for compilation. I am unable to help everyone, you know, and there are dozens of similar requests.
Use "Short Version" section. Trunk means 2.x - you dont want it yet.
Ok. But at least give me the link to the file I need to compile. You have updated the Kexi software, fixing the bugs. What is the url link to that file you have made? And if I install the Debian subversion package, that should be good enough for doing the compilation right? I'll try to contact #koffice channel or #kexi on IRC for questions after this.
Look, there is patch at http://kexi-project.org/download/patches/1.1.3/csv-import-improved-05-2008.patch to apply to the 1.1.3 source tarball, if you have one. But again, if you use subversion repository 1.6 branch, the patch is already applied _there_.
On the HowTo site you gave, before doing the install it says: 'Please uninstall any KOffice package if you have such installed independently (these not coming from this particular compilation). Otherwise you'll encounter hard to locate crashes or misbehaviour." The only KOffice components I have installed on my system now are "koffice-data" and "koffice-libs". Do these need to be removed before I move ahead, or are these alright (or even needed) to keep?
Yes you have to uninstall those koffice-* packages, because some files would be overwritten or conflict with kexi.
I have done the compile of Kexi and tested it, and the problems involving the date column are now repaired. After a .CSV import, the date columns retain their categorization type as "date", and null values in those date columns remain null as they should. The only problem I still see is that of the boulean column--the yes/no check box. It comes in as "floating point number", and all the values are zero or one. In the preview window of the kexi import utility, there is no boulean (yes/no box) option for column data types. There is only text, number, date, time, date/time. Shouldn't there also be an option for boulean (yes/no checkbox)? There is an option in the Kexi design view to change/make the column of type yes/no, but if you do that then kexi says it is going to delete all the data in the table.
Yes, there should be yes/no data type. For completness I am thinking about adding it - mostly because Kexi 2.0 won't be available soon. So, reopening the bug... In the meantime: perhaps it is possible to use integer data type with values of 0 and 1.
Working further with the import tool to bring in another .CSV database from OO Base, three further important points come to light: 1. The Kexi .CSV import tool only allows columns of type "number" to be set as primary key. In both MS Access and OO Base, columns of any data type can be set as primary key. For our e-mail mailing list for example, it is of critical importance to set the email address column as the primary key, so that no email address is allowed to be entered twice. 2. All imported dates year 2000 or later of format "01/09/08" are reassigned to the wrong century, i.e. "01/09/08" shows up in Kexi's final imported table as "1908-01-09". 3. In the import preview window, in one of two computers here doing Kexi .CSV imports, all the columns meant to be of type "date" are wrongly categorized as type "text". --But in either computer i.e. the one in which date columns are correctly identified by Kexi as being of type "date", and the other computer in which date columns are wrongly identified by Kexi as of type "text"-- in either computer, all dates after 2000 appear in the final Kexi table wrongly as being of the 20th century i.e. 1901 or 1902 instead of 2001 or 2002. Of the above three points, points #1 and #2 are both regarded as being very important, and of these, point #1 is regarded as critical. The imported table cannot be used until the email address column is permitted to be made the primary key.
Ad 1. In theory you just need 'unique' constraint, not primary key here. I'll look at other issues. BTW I have a patch for yes/no (and floating point types) mostly ready, will send it next week.
'Unique' constraint will work just fine, re point #1 of previous post. But 'unique' constraint is not seen to be available in the import preview window. And once the table is imported, one is not permitted to add the 'unique' parameter after the fact. If one tries to do so, then at the time of saving the change Kexi announces that upon saving, all the data will be deleted. So: if one needs to impose a 'unique' constraint on any colunn of a table to be imported as .csv, there needs to be a stage at which 'unique' constraint will be permitted to be imposed. Either at the import preview window, or after the import, in the finalized table's data view.
Created attachment 25075 [details] bool_and_fp_types.patch a patch for handling boolean and floating-point values in the CSV import dialog
Kindly give a brief instruction how the patch is to be implemented. Is it done from within the Kexi program, or does it involve compiling. What are the steps for implementing the patch. --Thank you.
1. In the command line, go to kexi source code directory (cd koffice/kexi/). 2. Enter: patch -p0 < bool_and_fp_types.patch 3. Compile Kexi by typing 'make' and then 'make install' in kexi directory. These are the same instructions as on http://kexi-project.org/wiki/wikiview/index.php@Kexi1.1.3_Patches.html
fixed url for konqueror: http://kexi-project.org/wiki/wikiview/index.php%40Kexi1.1.3_Patches.html
1. I downloaded the attachment you have given, but the attachment's name is not "bool_and_fp_types.patch", but is rather "attachment.cgi". I looked on the website you have given reference to, and all the patches for download there actually have the name "--------.patch". Whereas this attachment does not carry that name. So does one need to create a text file with the name "bool_and_fp_types.patch", and copy-paste all the code given in your attachment into that newly created file? 2. And then the newly created file "bool_and_fp_types.patch" should be placed in the directory "koffice/kexi/", and the terminal command then run as you have given?
re 1. Rename the file re 2. yes
Regarding the patch you have given, I renamed it as instructed and put it in koffice/kexi/. Then see the terminal code: ~$ cd /home/swarup/koffice/kexi ~/koffice/kexi$ patch -p0 < bool_and_fp_types.patch bash: bool_and_fp_types.patch: No such file or directory And there IS a file by that name in the kexi folder. Here it is: /koffice/kexi/bool_and_fp_types.patch What to do now?
if you are sure you have the bool_and_fp_types.patch file in kexi/ dir (type 'ls bool_and_fp_types.patch' to check), try to execute patch -p0 < ./bool_and_fp_types.patch (note the ./ added)
Here is the output: swarup@swarup-laptop:~$ ls bool_and_fp_types.patch ls: cannot access bool_and_fp_types.patch: No such file or directory swarup@swarup-laptop:~$ cd /home/swarup/koffice/kexi swarup@swarup-laptop:~/koffice/kexi$ patch -p0 < ./bool_and_fp_types.patch bash: ./bool_and_fp_types.patch: No such file or directory I don't understand why it is saying there is no such file or directory. The file IS there. Here I am right clicking on that very file, selecting "copy", and then pasting its path right here for you: /home/swarup/koffice/kexi/bool_and_fp_types.patch
Here is the ls from within that very kexi directory: swarup@swarup-laptop:~/koffice/kexi$ ls bool_and_fp_types.patch ls: cannot access bool_and_fp_types.patch: No such file or directory But plse see my above note in comment #34: the file IS there.
type ls inTthe kexi dir; what's the output? Excuse me irritation but you are not accustomed (yet!) with the shell. =)
swarup@swarup-laptop:~$ cd /home/swarup/koffice/kexi swarup@swarup-laptop:~/koffice/kexi$ ls 3rdparty formeditor kexi.la.o Makefile.in bool_and_fp_types.patch kexi kexiutils migration CHANGES kexidb kexi_version.h pics chartable.txt kexi.desktop libkdeinit_kexi.la plugins configure.in.in kexi_dummy.cpp main README core kexi_dummy.lo main.cpp tests data kexi_export.h main.lo tools debian kexi_global.h Makefile widget doc kexi.la Makefile.am examples kexi.la.cpp Makefile.global swarup@swarup-laptop:~/koffice/kexi$
type patch -p0 < bool and press tab - the name should be automatically completed; I guess you have space in the name or so.
1. When I typed "patch -p0 < bool" and hit "tab", it completed the exact file name which I have been using all along-- with one difference: it tacked a "/" onto the end of the file name. Like this: "bool_and_fp_types.patch/". Could that have made the difference? 2. Then I did "make". It worked for about an hour. Then I did "make install". But it just gave a few error lines and didn't do anything. So I tried "sudo make install". And it did the work. Is it ok that I did the first command--"make"--without sudo, and then followed it with "sudo make install"? Or will I need to go back and redo the first command as "sudo make"?
I just tested the .csv import, and it works perfectly with the yes/no (boulean). So you can ignore note #2 in my comment just above (#39). Great work! --By the way, any news about the century date issue? (See my comment #23, point #2)
Is it ok that you did the first command--"make"--without sudo, and then followed it with "sudo make install". This is how things have to be used. I'll look at the date issue too. BTW, so what was your filename? You probably created a directory "bool_and_fp_types.patch" and a file inside it.
Reply to your question: The filename was "bool_and_fp_types.patch". And it was not located in a directory of the same name. Rather, it is sitting directly inside the Kexi directory. Here is it's address: /home/swarup/koffice/kexi/bool_and_fp_types.patch It still remains a mystery why it refused to be recognized when I myself placed the address. Whereas if I typed only "bool" and then hit "tab", it would autocomplete with the exact same name and work fine. Wonders of nature.
Fixed problem with two-digit dates. Comment: The minimum year for the "100 year sliding date window": range of years that defines where any year expressed as two digits falls. Example: for date window from 1930 to 2029, two-digit years between 0 and 29 fall in the 2000s, and two-digit years between 30 and 99 fall in the 1900s. The default is 1930. The recipe: The patch is joined with the previous one, so to apply the new patch, you need first undo changes. 1. remove kexi/plugins/importexport/csv directory (I assume you do not have your important files in this dir, otherwise jsut move this dir elsewhere). 2. go to kexi/ directory in the command line 3. enter 'svn up' (this will revert changes made locally in your computer) 4. download the bool_and_fp_types_and_date.patch file into kexi/ directory 5. type in this directory: patch -p0 < bool_and_fp_types_and_date.patch 6. make, sudo make install
Created attachment 25288 [details] like bool_and_fp_types.patch but also contains date fix
I have carried out all the instructions given above and am confident that I have executed all the steps properly. But when I did the cvs import, the date century remained incorrect. Here is why I am confident that all was done properly by me: because I first followed your instructions for undoing the changes of the earlier patch (bool_and_fp_types.patch), so that in that undone state, the boolean fields would no longer import properly i.e. would import as true/false instead of as yes/no check boxes. But after installing the new patch and did the cvs import, the boolean fields *did* import properly as yes/no boxes. So this is proof that the new patch correctly reinstalled the boolean fix. But despite correct reinstallation of the boolean fix, the date century remained wrong on all the dates. "2006" imported as "1906", etc. And one further import defect has been detected: In the import preview window, there is a tab marked "options" where one can adjust the format in which the dates will appear i.e. "1998-10-22" vs "10-22-1998" etc. Despite selecting the format "10-22-1998", all the dates import as format "1998-10-22". So this options tab for selecting the date format does not work. I checked in the previous imports I have done to confirm that it has always been like this, and indeed it has. I just didn't notice it. Despite always selecting the format "10-22-1998", all the dates have been importing as format "1998-10-22".
Thanks for your tests Swarup. I have to look at the date options issue, but regarding your comment: "So this is proof that the new patch correctly reinstalled the boolean fix. " I am not sure the comment is true. Both patches contain boolean fix. The second patch contain the first one. So instead of guessing let's just execute every command step by step and _check the output for error messages_; preferably visit kexi IRC again for my assistance.
SVN commit 831164 by staniek: CSV Import Dialog - added support for boolean (1, 0, yes, no, true, false) and floating-point data types - fixed all dates after 2000 appearing after import wrongly as being of the 20th century i.e. 1901 or 1902 instead of 2001 or 2002; to implement this, added config value MinimumYearFor100YearSlidingWindow, by default 1930, range of years that defines where any year expressed as two digits falls. For date window from 1930 to 2029, two-digit years between 0 and 29 fall in the 2000s, and two-digit years between 30 and 99 fall in the 1900s. BUG:151478 M +87 -47 kexicsvimportdialog.cpp M +11 -0 kexicsvimportdialog.h M +1 -1 kexicsvimportoptionsdlg.cpp WebSVN link: http://websvn.kde.org/?view=rev&revision=831164
SVN commit 831237 by staniek: CSV Import Dialog (ported from 1.3, implemented on 2008-05-27) - added support for boolean (1, 0, yes, no, true, false) and floating-point data types - fixed all dates after 2000 appearing after import wrongly as being of the 20th century i.e. 1901 or 1902 instead of 2001 or 2002; to implement this, added config value MinimumYearFor100YearSlidingWindow, by default 1930, range of years that defines where any year expressed as two digits falls. For date window from 1930 to 2029, two-digit years between 0 and 29 fall in the 2000s, and two-digit years between 30 and 99 fall in the 1900s. CCBUG:151478 M +86 -48 kexicsvimportdialog.cpp M +11 -0 kexicsvimportdialog.h M +13 -9 kexicsvimportoptionsdlg.cpp WebSVN link: http://websvn.kde.org/?view=rev&revision=831237