Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#2746 closed defect (fixed)

.dbf files; inconsistencies with date formats

Reported by: j03lar50n Owned by: ashrafroni
Priority: normal Milestone: 1.6.1
Component: OGR_SRS Version: 1.6.0
Severity: normal Keywords: .dbf shape date
Cc: warmerdam, Markus Neteler

Description (last modified by warmerdam)

Inconsistencies in date formats within .dbf's of shapefiles have been noticed when loading a particular .shp in QGIS, OpenJump?, MapWindow? and OGR.

In particular the sample .dbf file has a installe_1 date field for which the first record is reported by OGR as 0000/00/04 while the actual text in the file (as reported by shapelib's dbfdump) is "04/25/1989".

Attachments (3)

water_main_dist.zip (42.2 KB) - added by j03lar50n 13 years ago.
.dbf file for Ticket #2746
shape2ogr.cpp (37.6 KB) - added by ashrafroni 13 years ago.
dateDiffPatch.patch (1.9 KB) - added by ashrafroni 13 years ago.

Download all attachments as: .zip

Change History (16)

Changed 13 years ago by j03lar50n

Attachment: water_main_dist.zip added

.dbf file for Ticket #2746

comment:1 Changed 13 years ago by warmerdam

Cc: warmerdam added
Component: defaultOGR_SRS
Description: modified (diff)
Keywords: shape added
Owner: changed from warmerdam to ashrafroni
Version: unspecified1.6.0

Roni,

Please treat this as your top priority bug report to work on. You will need to review the literature on dbf file date formatting to figure out if there is some systematic way to know date formats in .dbf formats. The OGR Shapefile driver uses shapelib for dbf access (http://shapelib.maptools.org) (also maintained by me), but the date support is quite new. http://www.clicketyclick.dk/databases/xbase/format/ may be a useful resource on dbf formats.

We must take care not to break other dbf files with dates while fixing this ticket. Note that this file is properly supported by some other applications (mapwindow, and excel) which presumably use some microsoft supplied dbf parser.

comment:2 Changed 13 years ago by ashrafroni

Status: newassigned

I used jump-src-1.1.2 and modified the source to load only water_main_dist.dbf and i got unparseable date. I went through the source code.

And got the date format in water_main_dist.dbf is Month.date.Year but the standard format jump expect is Year.Month.Date. In Excel shows it as String value only.So it can show the right format. In the dbf file the date is in text(String) format. I also want to know which version of open jump is used.

I will check the OGR and let you know details.

comment:3 in reply to:  2 Changed 13 years ago by j03lar50n

Replying to ashrafroni:

I also want to know which version of open jump is used.

I cannot get the shapefile to load in OpenJUMP version 1.2F .

Output Window msg:

1 Problem Loading water_main_dist. See View / Log For Stack Traces For input string: "'" (Number Format Exception)

Shapefile loads and date field attributes are readable in OpenEV.

Changed 13 years ago by ashrafroni

Attachment: shape2ogr.cpp added

comment:4 Changed 13 years ago by ashrafroni

Milestone: 1.6.1
Resolution: fixed
Status: assignedclosed

The ogrinfo expected the water_main_dist.dbf date field(installe_1) should be in YYYYMMDD fromat. For reading the date information from the database it used the following line of code:

int nFullDate = DBFReadIntegerAttribute( hDBF, iShape, iField );

But in the database file it is in MM/DD/YYYY format, so when it executes the upper line it showed wrong value. Now both YYYYMMDD and MM/DD/YYYY are being supported.

Please check the attachment.

comment:5 Changed 13 years ago by warmerdam

Resolution: fixed
Status: closedreopened

Re-open pending patch review and application.

comment:6 Changed 13 years ago by warmerdam

Roni,

Since you started working on this (in fact just in the last couple days) the shape2ogr.cpp file was modified for other issues. Please "svn update" to get those changes and then attach a patch generated with "svn diff shape2ogr.cpp".

Thanks,

comment:7 Changed 13 years ago by Even Rouault

Roni,

instead of using strcpn and strtok, you could use CSLTokenizeString()/CSLCount()/CSLDestroy() from the GDAL port library (/port subdirectory). See http://gdal.org/cpl__string_8h.html

comment:8 Changed 13 years ago by ashrafroni

Thanks rouault, Thats a good idea. I will change this immediately and send the svn diff patch soon.

Regards Roni

comment:9 Changed 13 years ago by Markus Neteler

Cc: Markus Neteler added

Changed 13 years ago by ashrafroni

Attachment: dateDiffPatch.patch added

comment:10 Changed 13 years ago by ashrafroni

Resolution: fixed
Status: reopenedclosed

I changed the string function using cpl_string.

comment:11 Changed 13 years ago by warmerdam

Resolution: fixed
Status: closedreopened

Reopening pending application of the patch(es).

comment:12 Changed 13 years ago by warmerdam

Keywords: date added
Resolution: fixed
Status: reopenedclosed

I have added a slight variation of the patch that more carefully confirms things are in MM/DD/YYYY format by checking at which offsets the slash is found.

Fix in trunk (r16200), and 1.6 branch (r16201) with a new test suite addition (r16199).

Thanks Roni!

comment:13 Changed 13 years ago by Even Rouault

Just a minor note : if someone updates date fields in such a .dbf, we would end up mixing 2 different date formats.

Note: See TracTickets for help on using tickets.