Issue 69965 - date values from text/csv files improperly read in OOo Base
Summary: date values from text/csv files improperly read in OOo Base
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.4
Hardware: Other Windows XP
: P3 Trivial with 6 votes (vote)
Target Milestone: OOo 2.4
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
: 67227 69825 71572 72264 74790 76645 80466 (view as issue list)
Depends on:
Blocks:
 
Reported: 2006-09-29 07:29 UTC by pmoegenb
Modified: 2008-03-12 09:51 UTC (History)
5 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
document to reproduce the bug case (47 bytes, application/csv)
2006-10-04 11:34 UTC, Frank Schönheit
no flags Details
document to reproduce the bug case (2.73 KB, application/vnd.sun.xml.base)
2006-10-04 11:35 UTC, Frank Schönheit
no flags Details
ZIP containing the two csv files, the odb file and two screenshots (196.00 KB, application/x-compressed)
2007-01-30 16:28 UTC, rkollien
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description pmoegenb 2006-09-29 07:29:13 UTC
Sample:


Textfiles as Sources

Fieldnames
Spendennummer;Adressnummer;Spendenart;WEINHEIT;PRIMWERT;BetraginBuchstaben;SEKWERTTXT;Spendendatum;Rechnungsjahr;Bestaetigungsdatum;Einnahmebelegvom;Negativbescheidvom;Notizen;EHST;DTA;UserID;BDatum;Aufwandsspende;Aufwandszweck;FHLNr;FHLID;Abteilung;Abtweiterleitung;AHST;Ausgabebelegvom;ZWGKZ;Aufwandstext;Verwendung;Rechtsgrundlage;Sachspendentext;Duplikat;Sachspendenanlage;Adressnummer_1;Personenart;Anrede;Briefanrede;Titel;AkademischGrad;Vorname;Nachname;Zusatzbezeichnung;Ortsteil;Strasse;Hausnummer;Land;PLZ;Ort;Geburtsdatum;Notizen_1;Ersteller;UserID_1;BDatum_1;Adressnummer_2;BDatum_2;UserID_2;Adresse1;Adresse2;Adresse3;Adresse4;Adresse5;Adresse6;Ersteller_1

Data
"20060913104254393";"20060913104306145";"Geldzuwendung";"€";100,00
€;"eins-null-null";"";12.09.2006;"2006";;;;"";"2200";Nein;"PMOEGENB";13.09.2006;Nein;"";;"";"";Nein;"8600";;"100";"Es
handelt sich nicht um den Verzicht auf die Erstattung von Aufwendungen.";"Es
wird bestätigt, dass es sich nicht um Mitgliedsbeiträge, sonstige
Mitgliedsumlagen oder Aufnahmegebühren handelt und die Zuwendung nur zur
Förderung kultureller Zwecke (im Sinne der Anlage 1 - zu § 48 Abs.2 EStDV -
Abschnitt B Nr. 2) verwendet wird.";"Wir sind wegen Förderung kultureller Zwecke
nach dem letzten uns zugegangenen Freistellungsbescheid des Finanzamtes
Böblingen, StNr. 56002/01663 vom 20.04.2006 für das/die Jahr/e 2005 nach § 5
Abs.1 Nr. 9 des Körperschaftsteuergesetzes von der Körperschaftsteuer
befreit.";"";"";"";"20060913104306145";"Natürliche Person";"Frau";"Sehr geehrte
Damen und Herren
Schneider,";"";"";"Ursula";"Mustermann";"";"";"Musterstr.";"61";"";"71134";"Aidlingen";;"";"PMOEGENB";"PMOEGENB";13.09.2006;"20060913104306145";13.09.2006;"PMOEGENB";"Frau";"Ursula
Mustermann";"Musterstr . 61";"";"";"71134 Aidlingen";"PMOEGENB"

Result in OOo Base
Textfields OK, Numberfields OK, Datefields  faultily (12.09.2006 = 02.01.12,
13.09.2006 = 02.01.13)
Comment 1 Frank Schönheit 2006-10-04 11:34:38 UTC
Created attachment 39554 [details]
document to reproduce the bug case
Comment 2 Frank Schönheit 2006-10-04 11:35:00 UTC
Created attachment 39555 [details]
document to reproduce the bug case
Comment 3 Frank Schönheit 2006-10-04 11:38:44 UTC
attached is a stripped version of the CSV data pasted above, and a database
document accessing it (You need to adjust the path to the CSV in
Edit/Database/Properties).

In fact, the date value, which in the CSV appears as 12.09.2006, is read as
02.01.12.

confirming, targeting, assigning.
Comment 4 Frank Schönheit 2006-10-04 11:43:22 UTC
Note that 2.0.2 reads the date as 09.12.2006, which is better, but not correct,
too. 2.0.3 already reads a wrong date.
Comment 5 Regina Henschel 2006-10-24 23:08:59 UTC
Seems to be the same problem as in issue 69825.
Comment 6 ocke.janssen 2006-10-25 07:46:27 UTC
*** Issue 69825 has been marked as a duplicate of this issue. ***
Comment 7 rkollien 2007-01-30 16:19:42 UTC
The error of reading date values not only affects the date fields, also neighbor
fields are corrupted. In my test case i send you a csv file, where text fields
(inside ") are interpreted as numbers and set to 0. Date fields are recognized
sometimes correct, depending which field lead or follows.

Attached zip contains 2 csv files with similar content, but the field order
varies . In the first one (accounting1) the text field "note" is interpreted as
number, but the date fields are correct. In file two (accounting2) the note
field is moved to the end of the data row. Not the note is correct, but one of
the date fields (date3) is always the same and wrong value.

And - last but not least - decimal value of 10,509.00 is interpreted as 10.00.
In the database "definition" (attached as account.odb) decimal = "." and
thousend delimiter is ",". Omitting the "," in the value so it reads "10509.00"
doesn't solve the problem. It seems to me, that using values > 999 with decimal
point is wrong interpreted. Solved the problem by exporting the value as 1050900
and divide this by 100 in calc/writer.

The workaround for dates and textfields is: arrange the text field as long as
you get the right value. In most cases the date and text problem is solved by
inserting text "fillers". I now export the file with some dummy fields named all
"X" and containing only "X" (with the "). Inserted this on the right place and
order i got my file working. But you have to test and unfortunately it is not
100% sure, as sometimes depending on the field value this doesn't work. 

I know, this are not only related to "ate values from text/csv files improperly
read in OOo Base" but there are some other issues around regarding more or less
all of this and i didn't want to create a duplicate issue named "csv text files
in base completely broken".

BTW: In calc i can open and use this csv file WITHOUT any problem.
Comment 8 rkollien 2007-01-30 16:19:57 UTC
The error of reading date values not only affects the date fields, also neighbor
fields are corrupted. In my test case i send you a csv file, where text fields
(inside ") are interpreted as numbers and set to 0. Date fields are recognized
sometimes correct, depending which field lead or follows.

Attached zip contains 2 csv files with similar content, but the field order
varies . In the first one (accounting1) the text field "note" is interpreted as
number, but the date fields are correct. In file two (accounting2) the note
field is moved to the end of the data row. Not the note is correct, but one of
the date fields (date3) is always the same and wrong value.

And - last but not least - decimal value of 10,509.00 is interpreted as 10.00.
In the database "definition" (attached as account.odb) decimal = "." and
thousend delimiter is ",". Omitting the "," in the value so it reads "10509.00"
doesn't solve the problem. It seems to me, that using values > 999 with decimal
point is wrong interpreted. Solved the problem by exporting the value as 1050900
and divide this by 100 in calc/writer.

The workaround for dates and textfields is: arrange the text field as long as
you get the right value. In most cases the date and text problem is solved by
inserting text "fillers". I now export the file with some dummy fields named all
"X" and containing only "X" (with the "). Inserted this on the right place and
order i got my file working. But you have to test and unfortunately it is not
100% sure, as sometimes depending on the field value this doesn't work. 

I know, this are not only related to "ate values from text/csv files improperly
read in OOo Base" but there are some other issues around regarding more or less
all of this and i didn't want to create a duplicate issue named "csv text files
in base completely broken".

BTW: In calc i can open and use this csv file WITHOUT any problem.
Comment 9 rkollien 2007-01-30 16:20:44 UTC
The error of reading date values not only affects the date fields, also neighbor
fields are corrupted. In my test case i send you a csv file, where text fields
(inside "") are interpreted as numbers and set to 0. Date fields are recognized
sometimes correct, depending which field lead or follows.

Attached zip contains 2 csv files with similar content, but the field order
varies . In the first one (accounting1) the text field "note" is interpreted as
number, but the date fields are correct. In file two (accounting2) the note
field is moved to the end of the data row. Not the note is correct, but one of
the date fields (date3) is always the same and wrong value.

And - last but not least - decimal value of 10,509.00 is interpreted as 10.00.
In the database "definition" (attached as account.odb) decimal = "." and
thousend delimiter is ",". Omitting the "," in the value so it reads "10509.00"
doesn't solve the problem. It seems to me, that using values > 999 with decimal
point is wrong interpreted. Solved the problem by exporting the value as 1050900
and divide this by 100 in calc/writer.

The workaround for dates and textfields is: arrange the text field as long as
you get the right value. In most cases the date and text problem is solved by
inserting text "fillers". I now export the file with some dummy fields named all
"X" and containing only "X" (with the ""). Inserted this on the right place and
order i got my file working. But you have to test and unfortunately it is not
100% sure, as sometimes depending on the field value this doesn't work. 

I know, this are not only related to "date values from text/csv files improperly
read in OOo Base" but there are some other issues around regarding more or less
all of this and i didn't want to create a duplicate issue named "csv text files
in base completely broken".

BTW: In calc i can open and use this csv file WITHOUT any problem.
Comment 10 rkollien 2007-01-30 16:22:42 UTC
The error of reading date values not only affects the date fields, also neighbor
fields are corrupted. In my test case i send you a csv file, where text fields
(inside doublequotes) are interpreted as numbers and set to 0. Date fields are
recognized sometimes correct, depending which field lead or follows.

Attached zip contains 2 csv files with similar content, but the field order
varies . In the first one (accounting1) the text field "note" is interpreted as
number, but the date fields are correct. In file two (accounting2) the note
field is moved to the end of the data row. Not the note is correct, but one of
the date fields (date3) is always the same and wrong value.

And - last but not least - decimal value of 10,509.00 is interpreted as 10.00.
In the database "definition" (attached as account.odb) decimal = . and thousend
delimiter is ,. Omitting the "," in the value, so it reads "10509.00", doesn't
solve the problem. It seems to me, that using values greater 999 with decimal
point are wrong interpreted. Solved the problem by exporting the value as
1050900 and divide this by 100 in calc or writer.

The workaround for dates and text fields is: arrange the text field as long as
you get the right value. In most cases the date and text problem is solved by
inserting text "fillers". I now export the file with some dummy fields named all
"X" and containing only "X" (with the doubleqoutes). Inserted this on the right
place and order i got my file working. But you have to test and unfortunately it
is not 100% sure, as sometimes depending on the field value this doesn't work. 

I know, this are not only related to "date values from text/csv files improperly
read in OOo Base" but there are some other issues around regarding more or less
all of this and i didn't want to create a duplicate issue named "csv text files
in base completely broken".

BTW: In calc i can open and use this csv file WITHOUT any problem.
Comment 11 rkollien 2007-01-30 16:26:41 UTC
Sorry for the mass of comments :-( Always got error 

Software error:

Can't use an undefined value as an ARRAY reference at IzCrmBridge.pm line 412

Plz be so kind an deleted the unnecessary..
Comment 12 rkollien 2007-01-30 16:28:03 UTC
Created attachment 42575 [details]
ZIP containing the two csv files, the odb file and two screenshots
Comment 13 rkollien 2007-02-01 08:38:57 UTC
Found that base seems to want to be smart and try to guess the field content
regarding the type (text or number). Didn't recognize that the field "number"
was incorrect. Don't know why, but base subtracts the value "2" of the content.
In the csv file, the value is "2007002" but base says "2007000". Had a value of
"2007001" and base means "2006999". I enclosed the field with doublequotes to
read "2007001" but base still subtracts. Only the "filler" workaround did the
job. Using "X";"2007001";"X" brings the correct value. Funny to see, that the
"X" is either interpreted as blank or as zero. 

I think, naming the issue "csv import in base is totally broken" would better
reflect the problem.
Comment 14 ocke.janssen 2007-08-02 07:40:16 UTC
*** Issue 72264 has been marked as a duplicate of this issue. ***
Comment 15 ocke.janssen 2007-08-02 07:47:16 UTC
Fixed in cws dba24a
Comment 16 ocke.janssen 2007-08-03 08:38:41 UTC
*** Issue 76645 has been marked as a duplicate of this issue. ***
Comment 17 ocke.janssen 2007-08-03 13:50:25 UTC
*** Issue 71572 has been marked as a duplicate of this issue. ***
Comment 18 ocke.janssen 2007-08-09 11:19:07 UTC
*** Issue 74790 has been marked as a duplicate of this issue. ***
Comment 19 ocke.janssen 2007-08-09 12:45:26 UTC
*** Issue 67227 has been marked as a duplicate of this issue. ***
Comment 20 Frank Schönheit 2007-08-14 22:29:13 UTC
*** Issue 80466 has been marked as a duplicate of this issue. ***
Comment 21 Frank Schönheit 2007-09-03 11:18:04 UTC
targeting to 2.4, since the fix is part of a CWS aiming for this release
Comment 22 ocke.janssen 2007-09-05 12:26:34 UTC
Please verify. Thanks.
Comment 23 christoph.lukasiak 2007-09-10 14:26:27 UTC
verified in cws
Comment 24 drewjensen.inbox 2008-03-12 09:51:13 UTC
tested w/ OOo 2.4 m_10, Kubuntu 7.1

closing