Help: Tricky Data Transformation (Paradox) 
Author Message
 Help: Tricky Data Transformation (Paradox)

][

I've undertaken a massive dbms project and I've got my first sticky
situation:  I need to transform an 8 character alpha (A8) field
(YYYYMMDD) to a Paradox date, and place the result back into the
table, overwriting the old A8 format.  A loop would be preferable,
since I've got something like 58,000 records...

Someone can do this in their sleep, right?  :-)

Thanks for any help!

--
michael regoli


..{att,rutgers}!moose!cica!mr



Sun, 28 Jan 1996 04:06:16 GMT
 Help: Tricky Data Transformation (Paradox)


Quote:
>I need to transform an 8 character alpha (A8) field
>(YYYYMMDD) to a Paradox date, and place the result back into the
>table, overwriting the old A8 format.

While you are in the table's date field and in CoEdit mode...and assuming
that every date field will be filled in the same format as given;
SCAN
   temp = SubStr([], 1, 4) + "/" + SubStr([], 5, 2) + "/" + SubStr([], 7, 2)
   [] = DateVal(temp)
ENDSCAN

hope it helps,
--nasir



Sun, 28 Jan 1996 19:43:58 GMT
 Help: Tricky Data Transformation (Paradox)
Quote:

>][

>I've undertaken a massive dbms project and I've got my first sticky
>situation:  I need to transform an 8 character alpha (A8) field
>(YYYYMMDD) to a Paradox date, and place the result back into the
>table, overwriting the old A8 format.  A loop would be preferable,
>since I've got something like 58,000 records...

I would think that you would write the results out to a NEW table,
and once you were satisfied with the results, rename the old to an
archive and rename the NEW table to have the old table name.
What exactly is the problem?  
The new table would have a D field whereas the old one is A8?
Are you trying to use Paradox itself, or PAL, or an engine app???
*********************************************************************
* Ed Dunkle                                  Texas Instruments Inc. *

* CompuServe: 76376,2215                     Plano, TX 75086        *
*                                                214-575-4791       *
*                                            FAX 214-575-2524       *
*********************************************************************


Mon, 29 Jan 1996 06:50:49 GMT
 Help: Tricky Data Transformation (Paradox)

Quote:
>>I've undertaken a massive dbms project and I've got my first sticky
>>situation:  I need to transform an 8 character alpha (A8) field
>>(YYYYMMDD) to a Paradox date, and place the result back into the
>>table, overwriting the old A8 format.  A loop would be preferable,
>>since I've got something like 58,000 records...

Why don't you just write a 'lil PAL script to read in the alpha numeric
date, pull it apart (substr(...) function comes to mind) and place it in
a new collumn .... then, if you like the results ... delete the old collumn
and have the data all point to the new date field.

-Bob-

Bob Angell                               | Data Integration (multi-platform)
Principal                                | AWK, C/C++, RDBMS langs, Paradox
Management Systems Engineering           | Health Systems Engineering
Applied Information & Management Systems | Database design/development
1238 Fenway Avenue - SLC, UT 84102-3212  | Simulation/Modeling/Neural Nets

IBMLINK:DEV4534, TEAMOS/2                | OS/2 2.x Application Developer

      [Disclaimer:  I don't speak for IBM or the University of Utah!]



Mon, 29 Jan 1996 09:40:17 GMT
 Help: Tricky Data Transformation (Paradox)

writes:

Quote:
> situation:  I need to transform an 8 character alpha (A8) field
> (YYYYMMDD) to a Paradox date, and place the result back into the
> table, overwriting the old A8 format.  A loop would be preferable,
> since I've got something like 58,000 records...

Not quite in my sleep but here are two methods:

;ctod.sc
; old table has a date field of "A8" in the format YYYYMMDD
; new  table has a date field of "D" format
; this procedure requires two databases, identical except for the type of
; the date field.
; Although this procedure requires two tables (and twice the space) it
; precludes mangling your original data base
; remember to back up your data before you do this: the data you save will be
; your own.

VIEW "new"
EDIT "old"

WHILE NOT EOT()
   MOVETO "new"
   ; transfer all other fields here
   [new->name] = [old->name]
   ;
   yr = SUBSTR ([old->datestr], 1, 4)     ; get the year part
   mo = SUBSTR ([old->datestr], 5, 2)     ; get the month part
   dy = SUBSTR ([old->datestr], 7, 8)     ; get the day part
   [new->Date date] = DATEVAL (mo +"/"+dy+"/"+yr)  ;convert str-> date
   DOWN         ; open blank record in new
   MOVETO "old"
   SKIP 1       ; move down 1 record in old
ENDWHILE

DO_IT!

If you are truly intent on working with only a single table, you can do the
following:

EDIT "original"

WHILE NOT EOT()
   ;
   yr = SUBSTR ([original->datestr], 1, 4)     ; get the year part
   mo = SUBSTR ([original->datestr], 5, 2)     ; get the month part
   dy = SUBSTR ([original->datestr], 7, 8)     ; get the day part
   [original->datestr] = mo +"/"+dy+"/"+yr     ; convert string format
   SKIP 1       ; advance to next 1 record in original
ENDWHILE
DO_IT!

At this point you still have a character field.  All you have to do now is
choose Modify/Restructure from the Paradox's Main Menu, and convert the type of
the date field to "D" from "A8".  Paradox will do the conversion automatically.
All you have to do is sit tight and bite your nails.  

I don't recommend the second method because I get the willys working with
original data.  I much prefer working on a copy or creating a new database as
in the first example.

Remember step 1:  Back it up first.




Tue, 30 Jan 1996 05:32:10 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Help: DTS Wizard Error - license does not permit data transformation

2. HELP? with VB code for SQL data transformation

3. Data Transformation Help!

4. About DTS and other Data Movement / Data Transformation tools

5. Btrieve to SQL Server using Data Transformation Services and Data Direct ODBC Driver

6. Tricky Paradox query

7. Paradox 4.0 PAL questions - tricky

8. Tricky question....how to internally copy TEXT data??

9. sql data transformation

10. Data Transformation Services

11. Remotely setting up Data transformations

12. Data Transformation Properties


 
Powered by phpBB® Forum Software