Date Import Format

[spain246]

I'm trying to import from a text file a list of dates. The format from the text file I am importing from is year, month, day. (Ex: 041225). I am trying to figure out how I can get it to convert to Month/Day/Year. (Ex:12/25/2004) Any thoughts to how I need to set the table format or change some coding to get this import to work consistantly?

Thanks in Advance.

Steve Hall

[Stan Mathews]

RE: Date Import Format

assuming the format you posted yymmdd

dstr = "041225"
? substr(dstr,3,2)
= "12"

? substr(dstr,5,2)
= "25"

? left(dstr,2)
= "04"

? ctod(substr(dstr,3,2)+chr(92)+substr(dstr,5,2)+chr(92)+left(dstr,2))
= {12/25/2004}

[spain246]

RE: Date Import Format

Thanks! But one quick question. I'm just now making the transition to learning Alpha 5 from access. Where would I insert the code so that when I import is show correctly?

Thanks!

Steve Hall

[Stan Mathews]

RE: Date Import Format

You'll probably have to have at least one intermediate step.

I would use the import genie to import the text file to a "junk" table. I would design the structure I wanted for the final result, create a new table with that structure, then apppend from the junk table to the "real" table. As you specify what fields to import to which fields in the real table, when you get to the field that will hold the date in question, specify the expression to be appended like


ctod(substr(@junktable-"datefield,3,2)+chr(92)+substr(@junktable-"datefield,5,2)+chr(92)+left(@junktable-"datefield,2))

where junktable is the name of the table you initially imported into and datefield is the fieldname holding the "041225" type data.

[Al Buchholz]

RE: Date Import Format

Steve

One way is to import as a character field and then convert into another date field once it's in a dbf.....

[spain246]

RE: Date Import Format

Is there a good way to automate it. Basically, i'm going to set up my autoexec to import and append files from a certain directory when I start the database. I have the import script set up exactly how I want it, but that is where i'm having the problem. Is there a way to automate this so that the imported fields (ex: 041225) are converted during the import to show up as (ex: 12/25/04). That's where i'm stuck. If I automate it, I know that it reduces the chance of someone in my office screwing up all of my data.

Everything else is importing like I want it, except that.

Thanks

Steve

[spain246]

RE: Date Import Format

Is there a good way to automate it. Basically, i'm going to set up my autoexec to import and append files from a certain directory when I start the database. I have the import script set up exactly how I want it, but that is where i'm having the problem. Is there a way to automate this so that the imported fields (ex: 041225) are converted during the import to show up as (ex: 12/25/04). That's where i'm stuck. If I automate it, I know that it reduces the chance of someone in my office screwing up all of my data.

Everything else is importing like I want it, except that.

Thanks

Steve