date conversion from csv flat file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get a weekly csv flat file from UPS with data about shipments. In this
file the date of shipment is a string that is, for example, 9242005 for this
week. The last 4 fields are always the year, the 5th and 6th from the right
are always the date and the 7th (and 8th for 2 digit months) from the right
is the month. Additional examples of the string:

July 9, 2005 -- 7092005
August 20, 2005 -- 8202005
October 8, 2005 -- 10082005

What is the preferred function(s) to convert these to dates that can be used
to generate reports for date ranges, etc?

I think I can check the length of string and then use the mid function to
pluck the right numbers. How do I check the length of the string and then
how would I convert the 3 sets of numbers into mm/dd/yyyy?

Thanks is advance for the help.
 
Marc said:
I get a weekly csv flat file from UPS with data about shipments. In this
file the date of shipment is a string that is, for example, 9242005 for this
week. The last 4 fields are always the year, the 5th and 6th from the right
are always the date and the 7th (and 8th for 2 digit months) from the right
is the month. Additional examples of the string:

July 9, 2005 -- 7092005
August 20, 2005 -- 8202005
October 8, 2005 -- 10082005

What is the preferred function(s) to convert these to dates that can be used
to generate reports for date ranges, etc?

I think I can check the length of string and then use the mid function to
pluck the right numbers. How do I check the length of the string and then
how would I convert the 3 sets of numbers into mm/dd/yyyy?

Thanks is advance for the help.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the DateSerial() function:

DateSerial(Right(Format(date_col,"00000000"),4),
Mid(Format(date_col,"00000000"),3,2),
Left(Format(date_col,"00000000"),2)) As CorrectDate

The Format(date_col,"00000000") will correctly format the string w/ 8
characters, no matter it's length. For an explanation of DateSerial()
see the Access VBA Help article.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzwYrYechKqOuFEgEQL/IwCg2sHFQjk5dTNgNN1YAgq1/yJ3xugAnjhd
lvzhkd748KPWyE/GwbMGIKg/
=9bZH
-----END PGP SIGNATURE-----
 
This may help you.

IIf(Len([upsdata])=7,Mid([upsdata],1,1) & '/' & Mid([upsdata],2,2) & '/' &
Mid([upsdata],4,4),Mid([upsdata],1,2) & '/' & Mid([upsdata],3,2) & '/' &
Mid([upsdata],5,4)) AS recDateStamp

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I get a weekly csv flat file from UPS with data about shipments. In this
| file the date of shipment is a string that is, for example, 9242005 for
this
| week. The last 4 fields are always the year, the 5th and 6th from the
right
| are always the date and the 7th (and 8th for 2 digit months) from the
right
| is the month. Additional examples of the string:
|
| July 9, 2005 -- 7092005
| August 20, 2005 -- 8202005
| October 8, 2005 -- 10082005
|
| What is the preferred function(s) to convert these to dates that can be
used
| to generate reports for date ranges, etc?
|
| I think I can check the length of string and then use the mid function to
| pluck the right numbers. How do I check the length of the string and then
| how would I convert the 3 sets of numbers into mm/dd/yyyy?
|
| Thanks is advance for the help.
 
Back
Top