Bug 147809 - csv file column formatting of numbers during read
Summary: csv file column formatting of numbers during read
Status: CLOSED FIXED
Alias: None
Product: KEXI
Classification: Applications
Component: Migration/Import/Export (show other bugs)
Version: 1.1.3 (KOffice 1.6.3)
Platform: Ubuntu Linux
: NOR normal
Target Milestone: ---
Assignee: Jarosław Staniek
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-07-12 14:13 UTC by r.c.drew
Modified: 2012-08-11 12:05 UTC (History)
0 users

See Also:
Latest Commit:
Version Fixed In:
Sentry Crash Report:


Attachments
See my description above (14.00 KB, application/octet-stream)
2008-01-10 16:52 UTC, r.c.drew
Details
1st test file of 6 (14.00 KB, application/octet-stream)
2008-01-10 16:55 UTC, r.c.drew
Details
2nd test file of 6 (14.00 KB, application/octet-stream)
2008-01-10 16:56 UTC, r.c.drew
Details
3rd test file of 6 (493 bytes, text/plain)
2008-01-10 16:57 UTC, r.c.drew
Details
4th test file of 6 (182.40 KB, text/plain)
2008-01-10 17:01 UTC, r.c.drew
Details
5th test file of 6 (474 bytes, text/plain)
2008-01-10 17:01 UTC, r.c.drew
Details
6th test file of 6 (181.75 KB, application/octet-stream)
2008-01-10 17:02 UTC, r.c.drew
Details

Note You need to log in before you can comment on or make changes to this bug.
Description r.c.drew 2007-07-12 14:13:14 UTC
Version:           1.1.3 (KOffice 1.6.3) (using KDE KDE 3.5.6)
Installed from:    Ubuntu Packages
OS:                Linux

When reading in a csv file, Kexi presently lists the format options as Text, Number, Date, Time, Date/Time.  Therefore one cannot assign "Real" (Floating-Point) and "Integer" number formats.  On reading in the spreadsheet, Kexi sometimes wrongly assigns a number column to be "Real" when it should be "Integer" and vice versa.  This can even occur when, across line 1 of the csv file, one assigns dummy variables all of which which reflect the required format.

The reading in of csv files is an extremely attractive feature of Kexi, and resolution of this issue would greatly enhance it.
Comment 1 Jarosław Staniek 2007-12-17 12:56:47 UTC
Coud you attach a small example csv file (or more of them) that causes the problem?
Comment 2 r.c.drew 2008-01-10 16:52:01 UTC
Created attachment 22935 [details]
See my description above
Comment 3 r.c.drew 2008-01-10 16:53:10 UTC
Here are the files I've prepared.
First, I created a spreadsheet using Kspread.
I then saved the file as a .csv and removed all the commas (using 
the "replace" facility in Kate) because I find that .csv data are read in 
better that way.
Finally I imported the .csv file into Kexi 1.1.3 and created a simple form to 
show the data.
All my comments about Kexi refer to v.1.1.3

There are several observations:

TEST1
1) The dates can only be shown YYYY-MM-DD, whereas I'd prefer to display them 
in European format DD-MM-YYYY
2) For some reason the dates on the 3rd, 5th, 7th and 8th lines have not been 
read in.  This has led to other data being read into wrong columns, or zeros 
resulting instead of numbers.  I know that one or two of the numbers are 
purposely large, and may cause a problem, but I have seen similar things 
happening without unusually large numbers.
3) In the Real Number column, there seems to be no way inside Kexi of setting 
the display to a certain no of significant figures.  So if the real number 
has no figures after the decimal point, Kexi displays it as an integer, 
rather than allowing a prescribed number of zeros (significant figures) to be 
displayed after it.
4) During import, there's no way to specify/force real numbers, so if you have 
a column of integers that you'd still like to set as real you can't.  (See 
the last column).  In Kexi, if you try and modify one of these numbers to be 
real, you can't.
However, in the Real Number column (second column) you'll see I added some 
integers at the end, and these have correctly been read in as real, and can 
be edited as real inside Kexi.

TEST2
I did this to see if the very large numbers were haveing an adverse effect - 
but there are still some numbers being read in wrongly.

Clearly, Kexi does have some good automatic data format setting capability, 
but I think it's a bit short on manual over-rides!
I don't understand why some of the numbers have been read into the wrong 
columns, missed out, or read in as zeros.

I will try to send some attachments alongside.
Comment 4 r.c.drew 2008-01-10 16:55:02 UTC
Created attachment 22936 [details]
1st test file of 6
Comment 5 r.c.drew 2008-01-10 16:56:11 UTC
Created attachment 22937 [details]
2nd test file of 6
Comment 6 r.c.drew 2008-01-10 16:57:05 UTC
Created attachment 22938 [details]
3rd test file of 6
Comment 7 r.c.drew 2008-01-10 17:01:02 UTC
Created attachment 22939 [details]
4th test file of 6
Comment 8 r.c.drew 2008-01-10 17:01:41 UTC
Created attachment 22940 [details]
5th test file of 6
Comment 9 r.c.drew 2008-01-10 17:02:27 UTC
Created attachment 22941 [details]
6th test file of 6
Comment 10 Jarosław Staniek 2008-05-09 10:44:04 UTC
BTW, similar report: http://bugs.kde.org/show_bug.cgi?id=151478
Comment 11 Jarosław Staniek 2008-05-09 16:30:37 UTC
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
Comment 12 Jarosław Staniek 2008-05-09 23:13:39 UTC
BTW, please read comment http://bugs.kde.org/show_bug.cgi?id=151478#c5 and below on the importance of being able to compile Kexi. Distros do not keep maintenance if we're not announcing "official" releases post-1.1.3.
Comment 13 Jarosław Staniek 2008-05-10 02:16:36 UTC
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