Importing with the right format conversion

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hello,

I have a problem regarding the way I want data from a
text file to appear in the database table.

For example, in a text file, I have fields with a record
that reads '20828'; this is a date, and corresponds to
the 28th of August, 2002.
Another example would be '990203': 3rd of Feb, 1999.

I plan to export tables to Excel and from there I need to
be able to calculate the number of days between different
dates really fast (this kind of operation would be
performed 10 000 times more or less).
I know how to do that if the dates are in the dd/mm/yy
format.

So, I am trying to convert the data I import to Access
from the text file in the format dd/mm/yy, but I have no
clue on how to tacle this.

Any insight would be helpful.

Thanks everyone!
Steve
 
Hmmm. It sounds as if the person who is creating this text file for you
may be exporting these dates as numbers, which would account for why August
28, 2002 appears as '20828' as opposed to '020828'. My first suggestion
would be: if you have any control over how this data comes to you, ask the
person creating this file for you to export the dates as text values and to
use yyyymmdd format so that you do not have to write code which first will
have to deal with inconsistent field widths and second will have to
interpret the century.

If the above is not possible, you will probably have to import these "date"
fields into an Access Text field and then do an update query to insert the
leading zero where the length of the field value is 5. You would then need
to do another update query to parse the value in your Text date field and
insert it into your true Date field. You can use the following expression
in the Update to: row of a query to update your true Date field with the
value in your Text date field (020828 becomes 08/28/02):

Mid([TextDateField],3,2) & "/" & Right([TextDateField],2) & "/" &
Left([TextDateField],2)


hth,
 
Thank you very much Cheryl, you solved my problem!!

Before importing the file, I made sure the type of the
date fields is 'Text', so I got the right 6 character
format. I then used your code to create a query for each
field that needed changes. It works great! Thanks so much.

One question though: I have 17 date fields, and I had to
create one query per field, and used a macro to have them
run one after the other; as this is the first week I work
on Access, I imagine the method is clumsy; do you know of
a better way to have done it?

Take care,
Steve
-----Original Message-----
Hmmm. It sounds as if the person who is creating this text file for you
may be exporting these dates as numbers, which would account for why August
28, 2002 appears as '20828' as opposed to '020828'. My first suggestion
would be: if you have any control over how this data comes to you, ask the
person creating this file for you to export the dates as text values and to
use yyyymmdd format so that you do not have to write code which first will
have to deal with inconsistent field widths and second will have to
interpret the century.

If the above is not possible, you will probably have to import these "date"
fields into an Access Text field and then do an update query to insert the
leading zero where the length of the field value is 5. You would then need
to do another update query to parse the value in your Text date field and
insert it into your true Date field. You can use the following expression
in the Update to: row of a query to update your true Date field with the
value in your Text date field (020828 becomes 08/28/02):

Mid([TextDateField],3,2) & "/" & Right ([TextDateField],2) & "/" &
Left([TextDateField],2)


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hello,

I have a problem regarding the way I want data from a
text file to appear in the database table.

For example, in a text file, I have fields with a record
that reads '20828'; this is a date, and corresponds to
the 28th of August, 2002.
Another example would be '990203': 3rd of Feb, 1999.

I plan to export tables to Excel and from there I need to
be able to calculate the number of days between different
dates really fast (this kind of operation would be
performed 10 000 times more or less).
I know how to do that if the dates are in the dd/mm/yy
format.

So, I am trying to convert the data I import to Access
from the text file in the format dd/mm/yy, but I have no
clue on how to tacle this.

Any insight would be helpful.

Thanks everyone!
Steve
Click to expand...


.
Click to expand...
 
You can update more than one field in an Update Query.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Thank you very much Cheryl, you solved my problem!!

Before importing the file, I made sure the type of the
date fields is 'Text', so I got the right 6 character
format. I then used your code to create a query for each
field that needed changes. It works great! Thanks so much.

One question though: I have 17 date fields, and I had to
create one query per field, and used a macro to have them
run one after the other; as this is the first week I work
on Access, I imagine the method is clumsy; do you know of
a better way to have done it?

Take care,
Steve
-----Original Message-----
Hmmm. It sounds as if the person who is creating this text file for you
may be exporting these dates as numbers, which would account for why August
28, 2002 appears as '20828' as opposed to '020828'. My first suggestion
would be: if you have any control over how this data comes to you, ask the
person creating this file for you to export the dates as text values and to
use yyyymmdd format so that you do not have to write code which first will
have to deal with inconsistent field widths and second will have to
interpret the century.

If the above is not possible, you will probably have to import these "date"
fields into an Access Text field and then do an update query to insert the
leading zero where the length of the field value is 5. You would then need
to do another update query to parse the value in your Text date field and
insert it into your true Date field. You can use the following expression
in the Update to: row of a query to update your true Date field with the
value in your Text date field (020828 becomes 08/28/02):

Mid([TextDateField],3,2) & "/" & Right ([TextDateField],2) & "/" &
Left([TextDateField],2)


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Hello,

I have a problem regarding the way I want data from a
text file to appear in the database table.

For example, in a text file, I have fields with a record
that reads '20828'; this is a date, and corresponds to
the 28th of August, 2002.
Another example would be '990203': 3rd of Feb, 1999.

I plan to export tables to Excel and from there I need to
be able to calculate the number of days between different
dates really fast (this kind of operation would be
performed 10 000 times more or less).
I know how to do that if the dates are in the dd/mm/yy
format.

So, I am trying to convert the data I import to Access
from the text file in the format dd/mm/yy, but I have no
clue on how to tacle this.

Any insight would be helpful.

Thanks everyone!
Steve
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top