Convert Table field from Character to Date

[Larado]

I am trying to import a wk1 file and a date field comes in as Character. How do I convert the characters to a date?
Thanks

[MoGrace]

Re: Convert Table field from Character to Date

Have you tried to open it in Excel and change the field format and then import?

[Stan Mathews]

Re: Convert Table field from Character to Date

How do I convert the characters to a date?

You can leave the imported data as a character value and operate on/with the date type equivalent.

Alpha has a ctod() function that will change a character representation of a date into a date value.
Alpha has a convert_type() function that will do the same.

After importing you should be able to edit the structure of the table and change the character type date field to a date type date field.

[Al Buchholz]

Re: Convert Table field from Character to Date

I would import as a character field, then add a date field and run an update operation to put the date value in..

Then you can look at what converts and what doesn't.

Excel is a great tool, but is too forgiving in the data formatting department for most data base systems.

[Larado]

Re: Convert Table field from Character to Date

Thanks for all the good solutions. I will try them now. One problem is I have to import the wk1 file and it has a 4 line header. I tried skipping the top 4 lines but it does not work. This is the code I am trying to use when I import:

import.type = 5
import.names = "none"
import.file = ui_get_file("",".wk1")
import.db = "\\dc1\users\lcarr\alpha five v10\alphasports\manhourimport.dbf"
import.skip_lines = 0
import.skip_bytes = 0
import.field_sep = ""
import.record_sep = ""
import.text_qualifier = ""
import.record_len = 0
import.rem_blanks = .F.
import.escape_newlines = .F.
import.fields = 11
import.field1 = "field_1,c,0,9,0"
import.field2 = "field_2,c,1,13,0"
import.field3 = "field_3,d,2,11,0"
import.field4 = "field_4,c,3,8,0"
import.field5 = "field_5,c,4,8,0"
import.field6 = "field_6,c,5,13,0"
import.field7 = "field_7,n,6,13,6"
import.field8 = "field_8,c,7,13,0"
import.field9 = "field_9,n,8,13,6"
import.field10 = "field_10,c,9,13,0"
import.field11 = "field_11,c,10,13,0"
import()

Field 3 is the date and it comes in blank.

[Stan Mathews]

Re: Convert Table field from Character to Date

import.type = 5

5 is for Lotus 1-2-3, Version 1.0/1A extension .WKS

6 is for Lotus 1-2-3, Version 2.0 extension .WK1

import.skip_lines = 0
should be
import.skip_lines = 4

[Larado]

Re: Convert Table field from Character to Date

Thanks Stan. Unfortunately the dates are coming in wrong. It's converting =DATE(110,11,2) into 2/1/2010. Is there a way to fix this? If I am importing a wk1 file wouldn't it know the format for dates? Thanks for all the help.

[Larado]

Re: Convert Table field from Character to Date

I found this in the help menu:
How to Import a Julian Date

A Julian date is a number cannot be imported directly into a date field. To import a julian date:
Import the Julian date data into a numeric field (e.g. numeric_field ).
1. Select the Operation tab of the Control Panel.
2. On the Create New Operation dialog box, select "Update Records" from the Select Operation list.
3. Select your table from the Select Table/Set list.
4. Click Create Using Genie.
5. Click the General tab.
6. Select "Assign a Calculated Value to a Field" and click Next >.
7. Select the date field (e.g. date_field ) of your table in the Which field would you like to update? list.
8. Enter the expression jtodate(numeric_field) in the Enter the expression for the calculated value field.
9. Click Next >, Next >, Next >, and Finish.
10. Enter a name for the operation and click OK, OK, and OK.

But I am getting this error:

Variable "numeric_field" not found

What am I doing wrong?
By the way I am importing the wk1 date info as a number then updating it to a date with this method. I am so close I can taste it.

[Stan Mathews]

Re: Convert Table field from Character to Date

jtodate(numeric_field)

substitute your numeric field's name for numeric_field above

[Larado]

Re: Convert Table field from Character to Date

I figured out what I was doing wrong. I needed a new column that was a date field. Ounce I fixed that it worked but the records ended up blank. I checked one of the numbers to see if they were accurate. They are not. So I am back to the drawing board. Some how I need to import a record that is this "=Date(110,11,2)" and turn it into a date of November 2, 2010. Help!

[Stan Mathews]

Re: Convert Table field from Character to Date

What does the 110 represent in "=Date(110,11,2)"

[Al Buchholz]

Re: Convert Table field from Character to Date

I would import as a character field, then add a date field and run an update operation to put the date value in..

Then you can look at what converts and what doesn't.

I figured out what I was doing wrong. I needed a new column that was a date field. Ounce I fixed that it worked but the records ended up blank. I checked one of the numbers to see if they were accurate. They are not. So I am back to the drawing board. Some how I need to import a record that is this "=Date(110,11,2)" and turn it into a date of November 2, 2010. Help!

and Stan's question too....

[Larado]

Re: Convert Table field from Character to Date

Thanks Al. Yea sorry for not giving you props for adding the date column. As far as the date in wk1 I assume it is the year indicator for 2010. This has been quite the nightmare. Lets recap where I am.
1. I have to find a way to import wk1 Lotus 123 files.
2. I have opened one in lotus 123 and the date column has this equation format @DATE(111,8,14)
3. The equation is this @DATE(111,8,14) The shown format is 14-Aug-2011.
4. I even tryed changing the format to (dd/mm/yyyy) and it converted correctly
5. 110 is shown as 2010
6. 111 is shown as 2011
7. When I import the wk1 into Alpha 5 the dates are brought in as numbers (40484)

I am lossing faith in Alpha 5 ease of use. This seems like it would be automatic. When I open the file in Lotus and save it as an excel file it imports great. Maybe a odbc connection?
If anyone has any ideas let me know.

[Al Buchholz]

Re: Convert Table field from Character to Date

Thanks Al. Yea sorry for not giving you props for adding the date column. As far as the date in wk1 I assume it is the year indicator for 2010. This has been quite the nightmare. Lets recap where I am.
1. I have to find a way to import wk1 Lotus 123 files.
2. I have opened one in lotus 123 and the date column has this equation format @DATE(111,8,14)
3. The equation is this @DATE(111,8,14) The shown format is 14-Aug-2011.
4. I even tryed changing the format to (dd/mm/yyyy) and it converted correctly
5. 110 is shown as 2010
6. 111 is shown as 2011
7. When I import the wk1 into Alpha 5 the dates are brought in as numbers (40484)

I am lossing faith in Alpha 5 ease of use. This seems like it would be automatic. When I open the file in Lotus and save it as an excel file it imports great. Maybe a odbc connection?
If anyone has any ideas let me know.

importing is one of the most difficult things to do.
1. because it seems it should be so easy.
2. because it's not so easy.
3. because you're trying to make 2 ( or more ) things that are not designed to work together - to work together.
4. because it's always the target systems limitations that are the problem - since it's already so neatly done in the source system.
5. because of all of the above is so true... --- not....

Thanks for the props - but not needed..

[Ted Giles]

Re: Convert Table field from Character to Date

Larado, I haven't got Lotus, so could you post a copy of the workpage with a date formatting issue for us to try things out on please?
Alpha does have a great deal of ese of use capability. Mostly intuitive, but to cope with the vagueries of so many possibilities, sometime a bit of digging is necessary.
I'm not that surprised that Alpha is having trouble importing another apps calculated display.