C
Chip
Hey everyone, I have a question about how to atumate a process. My
raw data is coming from Scantron forms. They are student application
data with address, dob, etc etc. I have the scanning application
writing to a .csv file on a server. I then have my database pointing
to that file to import my data.
Scan tron forms are nice, when their properly filled out. One of the
drawbacks I have though is the way inwhich is represents dates (for
DOB's).
When I scan them in, they come accross as xxxxxx (mmddyy, no
slashes). In excel, when I take that number and try to simply convert
it to a date by arbitrarily inputing /'s, I get all manner of weird
numbers. I believe it takes the number first says, ok this is a
computer or digital date and then converts it to a julien date.
For example, May 25th, 1988 comes accross at 52588. It doesnt add a
leading 0 to the month. So the way I'm dealing with that is in excel,
I first evaluate the length of the number using the LEN function. It
will either come back at 5 or in the case of October, November and
December, 6. (December 11th, 1970 will come over as 121170). Then I
use a Concentenate function to inser my slashes. In excel, I have a
cell that concentenates as if the number was 5 digit date and another
cell that concentenates as f the number was a 6 digit date.
=CONCATENATE(LEFT(A2),"/",MID(A2,2,2),"/",RIGHT(A2,2))
=CONCATENATE(LEFT(A2, 2),"/",MID(A2,3,2),"/",RIGHT(A2,2))
Then in a third cell, I use an If statement that says
=IF(B2=5,C2,D2)
At whcih point I use a fourth cell to evluate the age of the provider
=(NOW()-E2)/365.25
Works well enough in excel, although I'm relatively sure there's a
more elegant solution. But what I want to do now is in access, using
a linked table to where this raw data is, I want to write a script,
that will scrub this data (accomplishing the above, and a few simpler
formating items) and then write (append) to my real table, and then
finally delete the data on the original linked table (or maybe fix it
so I dont renter or re import that data).
Not sure if I'm makign much sense. If someone can help me with the
above script to simply convert the date, I think I'll be in good
shape.
thanks..
chip
raw data is coming from Scantron forms. They are student application
data with address, dob, etc etc. I have the scanning application
writing to a .csv file on a server. I then have my database pointing
to that file to import my data.
Scan tron forms are nice, when their properly filled out. One of the
drawbacks I have though is the way inwhich is represents dates (for
DOB's).
When I scan them in, they come accross as xxxxxx (mmddyy, no
slashes). In excel, when I take that number and try to simply convert
it to a date by arbitrarily inputing /'s, I get all manner of weird
numbers. I believe it takes the number first says, ok this is a
computer or digital date and then converts it to a julien date.
For example, May 25th, 1988 comes accross at 52588. It doesnt add a
leading 0 to the month. So the way I'm dealing with that is in excel,
I first evaluate the length of the number using the LEN function. It
will either come back at 5 or in the case of October, November and
December, 6. (December 11th, 1970 will come over as 121170). Then I
use a Concentenate function to inser my slashes. In excel, I have a
cell that concentenates as if the number was 5 digit date and another
cell that concentenates as f the number was a 6 digit date.
=CONCATENATE(LEFT(A2),"/",MID(A2,2,2),"/",RIGHT(A2,2))
=CONCATENATE(LEFT(A2, 2),"/",MID(A2,3,2),"/",RIGHT(A2,2))
Then in a third cell, I use an If statement that says
=IF(B2=5,C2,D2)
At whcih point I use a fourth cell to evluate the age of the provider
=(NOW()-E2)/365.25
Works well enough in excel, although I'm relatively sure there's a
more elegant solution. But what I want to do now is in access, using
a linked table to where this raw data is, I want to write a script,
that will scrub this data (accomplishing the above, and a few simpler
formating items) and then write (append) to my real table, and then
finally delete the data on the original linked table (or maybe fix it
so I dont renter or re import that data).
Not sure if I'm makign much sense. If someone can help me with the
above script to simply convert the date, I think I'll be in good
shape.
thanks..
chip