how to stop empty date field being filled in?

[Shepherd Jim]

Hello All:

I maintain a number of databases in Alpha Five (ver 8) on my WinXP PC. I usually move those files to my Palm Centro (HDB 4.1.6) by exporting text .csv files and then, in the HDB Desktop, importing those files into existing HDB .pdb databases.

I have one database with a date field which, in almost all records, is empty = shows "NO DATE". I'm having a problem in that after I import the file the empty date fields are all populated with the date "01/01/2000". All the records which DO have a date in that field are fine -- that date remains uneffected.

This does not happen with all databases. Others manage to export/import the "NO DATE" empty date field without trouble. I've even checked and they're all set to "Date Record Added".

What am I missing?

Jim

[dhaupert]

Hi Jim!

Did you import via the CSV import, or using the Data Exchange tool? Is there a chance you could send me the database and an import file so I can debug this problem on my end?

Thanks in advance!

[Shepherd Jim]

....

Did you import via the CSV import, or using the Data Exchange tool? Is there a chance you could send me the database and an import file so I can debug this problem on my end?

...<cut>...

Hey Dave,

Okay, I couldn't see how it would be possible to attach files to an internal "PM" so I'm emailing you separately the two files: plants.csv, the "comma-delimited" text file exported from Alpha Five (8) and the Plants.PDB file that results from importing the csv.

As I said, the pdb was created by importing. I opened the Plants.PDB on the HanDBase Desktop, deleted all the "old" records in it and then imported all plants.csv records -- having chose to import all records into an "existing database replacing any existing".

It's the field "Date_Remov" that's exhibiting the problem. A date in that field indicates that a plant has been removed from the collection. I have a "standing" filter set up to only show plants that have NOT been deleted -- if there's a date in the "Date_Remov" field I don't see the record. Of course, with that field being filled in with "01/01/00" for ALL records that do not already have a removal date, I'm not seeing ANY records in the database. I have to turn off the filter and fill all those records with "NO DATE".

I'm going to re-fresh my mind re the "Data Exchange tool" -- maybe another HDB ability I haven't explored?

Thanks! Jim

[dhaupert]

Thanks for sending the file. I took a look and discovered why that field imports as 1/1/1904 instead of No Date. The field is not really empty, it has a blank space in it. The parsing code checks to see if there is no length to the field and sets to no date, otherwise, tries to parse it. It comes up with 2000 because we assume any date not starting with 19xx or 20xx in the year is a 2000 so we add 2000 to the value (which is 0 in this case, since blank). The solution here is:

1. Make sure blank dates are truly blank (ie, no space)
2. Try using Data Exchange which may yield a more direct conversion.

I can definitely change the behavior of the import routine for dates, but honestly I'd be a bit scared to change something so time tested at this point. It's never posed a problem before, and I'd be afraid that this change could break many other assumed behaviors for other users. I am definitely 'open to change' on this though, if there is enough demand for the change!

[Shepherd Jim]

Thanks Dave!

As always, you've proved yourself to be an expert diagnostician.

I've posted the following over on the Alpha Five (Ver. 8) Forum:

Dave Haupert of DDH Software discovered that when Alpha Five is exporting a .csv text file the empty date fields are NOT empty; there's a single " " blank space in each date field that does not have an actual date in it.

Unfortunately, that blank is tripping up HanDBase which checks the field length of incoming date fields. Only if the length is 0 will HanDBase assign a "No Date" value to the field.

I've been messing around with "Operations" -- changing different settings re the "Export Character ASCII" options: deleting leading and/or trailing characters. The default "end of record" appears to be a <CR> and a <LF> -- I've tried using one or the other without getting rid of the blank -- BTW: running the export WITHOUT either a <CR> or <LF> didn't work well at all.

I have been experimenting and find that A5 inserts the blank " " when exporting "empty" date, numeric AND logical fields. OTH, exported character fields are actually empty

.
Alpha Five seems to have a way to do just about anything so I'll be surprised if there's not an easy solution to this problem. I'll keep all the HDB people apprised.

Jim

[dhaupert]

Thanks Dave!

As always, you've proved yourself to be an expert diagnostician.

First time I've ever been given that title. I think I'll adjust my signature to say:

Dave Haupert
Expert Diagnostician



In all seriousness, how is this for a workaround:
In your database, create a view called 'needs fixin' (or whatever you want). Set a filter on the date field being 1/1/2000.
Import as you've done before.
Select the view to see all records that have the 1/1/2000 date
Use Actions, Set Value to and choose the field in question.
Set to No Date and proceed.

This should work unless there is a chance that there are any legit records set to 1/1/2000! Plus it's an extra step, but probably just a few clicks. If this is something you're doing often, I'd suggest Data Exchange instead- since it's an ODBC transfer of information and probably won't have this issue.

Sincerely,
Dave Haupert
Expert Diagnostician


Set a