"Oups...I did it again...." YES, I found a (complex) way to import my table with correct dates and times.
1. I created the ODBC connection 2. Connected the access-database 3. SATTACHED the table 4. copied the table to a Rbase table 5. created a view on this table to correct the data 6. did a manual insert via an INSERT command in and RMD file to insert the rows in the correct RBASE table. This was a pretty intensive job.....but in the end, it worked and that's all that counts... Luc D. From: jan johansen Sent: Tuesday, November 09, 2010 9:54 PM To: RBASE-L Mailing List Subject: [RBASE-L] - re: importing table problem Sometimes cut and paste does some weird stuff -----Original Message----- From: Gary Wendike <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Tue, 9 Nov 2010 12:48:05 -0800 (PST) Subject: [RBASE-L] - re: importing table problem Yes...sloppy work on my part transferring the info. ------------------------------------------------------------------------------ From: jan johansen <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, November 9, 2010 10:36:57 AM Subject: [RBASE-L] - re: importing table problem Also I assume that IF SQLCODE < 0 should be IF SQLCODE <> 0. -----Original Message----- From: Gary Wendike <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Tue, 9 Nov 2010 08:31:03 -0800 (PST) Subject: [RBASE-L] - re: importing table problem Correction...the line that say I export Sttached table should be that I export the projected table. This is done after I drop some unneeded columns. You may not need to do this. Gary ---------------------------------------------------------------------------- From: Gary Wendike <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, November 9, 2010 8:21:09 AM Subject: [RBASE-L] - re: importing table problem Luc, I import files daily from Access based files. Here are the steps I use. I am sure there are better ways to complete the process and you may want to improve on the steps. I do a DSN-Less connection SATTACH the table Project the SATTACHed table to put it in RBASE "hands" Drop some columns I don't need Export STTACHed table to ASCII file type....this is done because of column names and formats. Set date format for loading the data Create temporary table in which to load the data Load the data as ASCII Reset the date format Below is a part of the code for loading the data. I have been using this method for over 2 years with no problems. SET DATE YYYY/MM/dd - This is the format in which the date is submitted *(Create Table and load data from txt file) DROP TABLE inventdc CREATE TABLE `InventDC` (`Item_No` INTEGER, + `DCNo` INTEGER, + `dcinventoh` INTEGER, + `dcinventturn` INTEGER, + `dcinventbuyer` INTEGER, + `dcinventoo` INTEGER, + `cwoflag` TEXT (2), + `cwodate` DATE) *(load data from ASCII files for Inventory DC ) LOAD inventdc FROM &voutputfile AS ASCII IF SQLCODE < 0 THEN OUTPUT dt_invdc.emg WRITE 'DC Inventory did NOT load. ' OUTPUT SCREEN GOTO startload ENDIF *(Resets date format to system format) SET DATE MM/DD/YYYY Gary ---------------------------------------------------------------------------- From: Luc Delcoigne <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, November 9, 2010 3:35:52 AM Subject: [RBASE-L] - re: importing table problem Same problem with the excell-conversion. DDMMYYYY is imported as MMDDYYYY. ;-) ----- Original Message ----- From: Luc Delcoigne To: RBASE-L Mailing List Sent: Tuesday, November 09, 2010 10:31 AM Subject: [RBASE-L] - re: importing table problem Thanks Tony, Right now I'm trying to convert via the Excell-sheet. Curious if that would work.... Luc D. ----- Original Message ----- From: A.G. IJntema To: RBASE-L Mailing List Sent: Tuesday, November 09, 2010 10:21 AM Subject: [RBASE-L] - re: importing table problem Luc, I am not familiar with all the details, but maybe it is an idea to attach all tables as is to the database (make a backup before!). Then you have to write some conversion procedures. You extract the date and time portion with functions like (DEXTRACT(datetime )) I am aware it does not help you for now, but an important lesson I have learned in the past is to start with the conversion when you have to rebuild a system. Not only for this kind of details but also you will find hidden know how, when you analyze the content of the old tables. I hope this helps a bit. Tony From: [email protected] [mailto:[email protected]] On Behalf Of Luc Delcoigne Sent: dinsdag 9 november 2010 9:51 To: RBASE-L Mailing List Subject: [RBASE-L] - re: importing table problem Razzak, I have tried the ODBC migration .......however I get the message " Cannot Attach a DATETIME column to existing Consultdate of Type DATE"... It seems that the date and time columns in Access are really a DATETIME field, which seems to be different from the Rbase Date And Time fields. So the import works but all the date and time fields are left blank in the Rbase table.... How can I solve this annoying problem ?? Luc D.
