Importing DBF to Paradox - Dates problem

[rogerte]

We routinely take large DBF files, which contain dates in character fields in the format "dd/mm/yyyy", and import them into Paradox tables using Paradox 9. The Paradox files have identical structure, except that the date fields are dates.

Using the Tools\Utilities\Add menu we read the data from the DBF to the DB. Nearly every time there is a message about trimming the data for the date fields, but usually the data is imported OK. However sometimes all the data for all fields from the date field onwards is blank. Doing some tests it appears that in such cases Paradox is assuming that the data is in US (mm/dd/yyy) format and doesn't like dates such as "30/10/2001"

The BDE Admin program (5.01) is set up with date formats as 4digityear and mode as 1.

Does anyone know why it sometimes fails?

<btw> the reason we use character fields for dates in the DBF is that some dates fall outside of the DBF date range, but are valid in Paradox.

Does anyone know why

 

[footpad]

rogerte,

Well, first and foremost, why not simply work with the .DBF files natively? Paradox for Windows can work with multiple data types as natively as it handles its own, including dBASE Tables.

In other words, you may not need to transfer the DBF data into DB files, if you don't want to. Simply link your Paradox .DB files to your .DBF's using standard linking indexes and go from there.

Second, Paradox for Windows automatically formats date values according to your Windows Control Panel settings. If it's using dd/mm/yyyy by default, then that's probably the format of your Windows Short Date format.

You can control this by setting the default date format using ObjectPAL. For example:

   if formatExists( "mm/dd/yy" ) then
      formatSetDateDefault( "mm/dd/yy" )
   endIf

Mind you, you need to use an existing date format. You can see what's available by:

1. Creating a new, blank form.
2. Dropping an unbound field object.
3. Right-clicking the field object, choosing Properties from the shortcut menu, and then selecting the Format tab.

When you do this, you're presented with a list of formattable data types and the formats available for each one.

Note that Date is selected by default and that the Format list shows several. The name displayed in the list is what you pass to the formatSetDateDefault method.

You can create new date formats from this same dialog box, though be aware that custom date formats are stored in the Registry and must therefore be copied to every PC using the system. It's a pain, but that's how it works.

Also, when working with custom date formats, it's an excellent idea to ensure that all field objects bound to date fields have their Date Format properties specifically set to the desired format on all forms and reports using the tables in question. It takes time to set up but does reduce confusion in the long run.

Finally, you can always roll your own conversion code using TCursors. If you find the interactive processes difficult to manage, this may be the cheapest and most effective alternative.

In this approach, you open two TCursors, one of the source DBF file and one on the target .DB file. Then you use the scan statement to cycle through each source record, convert the string date to a valid datetime value assigned to a variable and then add or update a record in the target Tcursor with the results.

I know this seems like a lot of work, but it's necessary when you use data formatting that's different from what Windows itself uses. Paradox works very hard at letting the OS do what it's supposed to.

Hope this helps...

-- Lance

 

[rogerte]

Lance

Thanks for the reply. Unfortunately I have no control over the data formats - We have an old DOS program (with about 1200 users) that uses the DBF format and a Delphi program (also about 1200 users) which was written using the DB format.
I forgot to mention that all data import is done by Tools/Utilties

What is puzzling is that the PC used for data building has dd/mm/yyyy as the Windows date format, and that sometimes the import works OK, other times it doesn't!

Just had an idea though - like most organisations the new super PCs are given to the Directors and we humble IT people are left with the dross. The PC in question is an old Compaq 133MHz Pentium - I bet the BIOS isn't fully Y2K - wonder if that is anything to do with it?

Roger

 

[LeBlatt]

I'm quite sure the bios has nothing to do with the problem.

I had the same problem, except I was importing ascii files, and some dates werent handled properly (with a script, not the menu).
So I use importAsciiFix to import the ascii file into a db table (where all fields are strings), which I later scan with

scan tTempTable :
tMyTable.insertRecord ()
tMyTable.update (
field1, tTempTable.stringField,
field2, longInt (tTempTable.integerField),
field3, date (tTempTable.dateField.subStr (4, 2) + &quot;/&quot; + tTempTable.dateField.subStr (1, 2) + &quot;/&quot; + tTempTable.dateField.subStr (7, 4))
)
tMyTable.unLockRecord ()
endScan

for inserting a string, an integer and a mm/dd/yyyy date, for instance.
In my case it also allows to process stuff like replacing decimal separators, etc.