Thank you all for your support. Albert, I think that one of my problems is the date setting. In Holland commonly the setting dd-mm-yyyy is used. >From a long time ago I can remember that there was no issue converting text to date if dd/mm/yyyy is used.
Dennis, your solution is great. I did the same trick, but I needed much more code. As always in R:base, there are 100 solutions possible, of which 90 are good, 8 poor and 1 or 2 are brilliant. The trick is always to find the brilliant one. Tony -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Albert Berry Sent: donderdag 27 maart 2014 16:03 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet I just did a quick test, and Date can be text, provided that the date format is identical to the text column. R>show date DATE format MM/DD/YYYY DATE sequence MMDDYY Century threshold YEAR is 30 Default CENTURY is 19 R>SET VAR vDateText TEXT = '06/30/2014' R>SET VAR vDate DATE = .vDateText R>show var vdate% Variable = Value Type -------------------------------- ------------------------------ ------- vDateText = 06/30/2014 TEXT vDate = 06/30/2014 DATE On 3/27/2014 8:47 AM, Dennis McGrath wrote: > OOPS, This is better! > > --Convert data string to date > UPDATE ImpProcess SET CELLG = (SRPL(CELLG,'/',',',0)) > > UPDATE ImpProcess SET PaidDate = + > (RDATE(INT(SSUB(CELLG,1)),INT(SSUB(CELLG,2)),INT(SSUB(CELLG,3)))) + > WHERE CELLG LIKE '%,%,%' > > > Dennis McGrath > Software Developer > QMI Security Solutions > 1661 Glenlake Ave > Itasca IL 60143 > 630-980-8461 > [email protected] > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Dennis > McGrath > Sent: Thursday, March 27, 2014 9:46 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > This is what I came up with: > > --Convert data string to date > UPDATE ImpProcess SET CELLG = (SRPL(CELLG,'/',',',0)) > > UPDATE ImpProcess SET PaidDate = + > (RDATE(INT(SSUB(CELLG,1)),INT(SSUB(CELLG,2)),INT(SSUB(CELLG,3)))) + > WHERE AND CELLG LIKE '%,%,%' > > Dennis McGrath > Software Developer > QMI Security Solutions > 1661 Glenlake Ave > Itasca IL 60143 > 630-980-8461 > [email protected] > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Buddy > Walker > Sent: Thursday, March 27, 2014 9:16 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > Tony > If I remember correctly sometimes Excel adds time to it even though > it is set as date only. > > I would suggest importing into a temp table. In the temp table make > sure to have an extra column at the end that is date. If the imported > field looks like a date then just update the last column based on the imported date. > > Your temp table should look sometime like this > > TempTableName > Imp_Col_1 text 8 > Imp_Col_2 text 10 - your imported text date field > RLastCol DATE > > >From the R:> or code > > UPDATE TmpTableName SET RLastCol = Imp_Col_2 > > Buddy > > > > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Tony > IJntema > Sent: Thursday, March 27, 2014 9:12 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > Buddy, > > I have done that. > The setting was dd-mm-yyyy (European setting) I have imported the > spreadsheet after having defined a RGW-file, but the date values were > ignored in the import, because they were delivered in a different way. > The source of the problem is the poor date capabilities of Excel. > Maybe XLSX is working better, but I need to be sure that everybody can > use the spreadsheet, thats why I am using XLS. > > In the meantime I have implemented a little conversion program by myself. > I extract the day, month and year value from the text field and then I > convert it to a date value using Rdate It works fine and as far as I > can see now it is solid like a rock. > > Thank you for your suggestion. > > Tony > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Buddy > Walker > Sent: donderdag 27 maart 2014 13:49 > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > Tony > I think this starts with the Excel worksheet. Make sure the column > is set as date (mm/dd/yyyy) or whatever matches the database sequence. > Not sure how you are doing the import but during import you can select > how the date being imported is set. > > Buddy > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Tony > IJntema > Sent: Thursday, March 27, 2014 8:39 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > Jason, > > Normally this is what I am doing, but in this case the database is > distributed to a lot of people (everybody will have its own database). > So I am not in control of the situation. > I like to avoid complicated handling at the user site. > > I have found a solution using the Rdate function, but I was hoping > that I missed a conversion function. > > Tony > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of > Kramer, Jason J > Sent: donderdag 27 maart 2014 13:12 > To: RBASE-L Mailing List > Subject: [RBASE-L] - RE: Date problem while importing Excel sheet > > Can you dump to CSV and import that instead? > Jason > > Jason Kramer > University Archives and Records Management > 002 Pearson Hall > (302) 831 - 3127 (voice) > (302) 831 - 6903 (fax) > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Tony > IJntema > Sent: Thursday, March 27, 2014 8:08 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Date problem while importing Excel sheet > > Hi, > > In one way or another Excel (xls) is always causing problems when I > try to import a date value in a table. > To avoid this I can import the date values as a text and then I like > to convert the text value into a date value. > Does anyone know a solution for converting text values to date values. > I cannot find this in the help > > Tony > > >
