Correctly Formating Date and Time Field 1

[lorn]

I'm trying to import a csv file that uses a date field as follows:

04JAN1993

and a time field as follows:

9:30:27

Excel opens it right up, but access doesn't seem to want to import the values correctly. Are these nonstandard formats for access? What would be the correct way to import them?

Thank you very much for any help you might give. Best Regards.

 

[TomCologne]

Easiest way is setting the Access table fields to "Text".

If that's not an option, import to a temp table, update the values to an Acc compatible format with a query and append the records to the destination table.


TomCologne

 

[lorn]

The problem with the "text" solution is that I need to change that format so it can be read by another program. Which means I definitely need to have it in date format.

The reason I thought access might be able to do the job, is because the files are huge... csv's on the order of 100-400mb with millions of rows. I'm not even attempting to import (actually I did, didn't work)... just linking and running a query. So editing the files and changing the date format first in a spreadsheet is out.

After further testing I noticed that with certain date formatting the date imports ok. I hadn't noticed before because it was only visible lower in the table... for the rest I get #Num! errors. There is no incongruency in the actual .csv data to account for this. Could this possibly be because of the file size?

If it is a problem with the size. Are there any programs that can manipulate large csv's like this. My basic needs are to query the primary field by date and then export these to smaller, seperate files.

Any suggestions would be greatly appreciated.

 

[willir]

I tried working with the data you provided and the import wizard.

One problem is that you do not have a date deliminater...
04JAN1993
vs
04/Jan/1993
or
04/01/1993

(Hint: Look at the "Advanced" tab when running the import wizard for more control of the import.)

04Jan1993 -> 11/4/993
20Jan2005 -> 12/20/2005
01Nov2003 -> error

So, I went on to Plan B.
I imported dates and times as text fields

The CDate(YourTime) worked perfectly in converting the time as a text to time as a date/time data type.

The date was more of a challenge.

I created an function to convert the date...

Function ConvertDate(MyDate As String) As Date
Dim intMonth As Integer, strMonth As String

strMonth = Mid(MyDate, 3, 3)
intMonth = (InStr(1, "JanFebMarAprMayJunJulAugSepOctNovDec", strMonth) / 3) + 1

ConvertDate = DateSerial(Right(MyDate, 4), intMonth, Left(MyDate, 2))

End Function

I did not include error checking, but the following SQL statement worked perfectly with the Text import file...

[tt]
SELECT T.MyID, T.MyDate, ConvertDate([MyDate]) AS My_Date, T.MyTime, CDate([MyTime]) AS My_Time, T.MyText
FROM TestTxt as T;
[/tt]

[tt]
MyID MyDate My_Date MyTime My_Time MyText
As Text As Date As Text As Date
1 04Jan1993 1/4/1993 9:30:30 9:30:30 AM Test1
2 05Jan1993 1/5/1993 9:45:31 9:45:31 AM Test2
3 06Jan1993 1/6/1993 10:00:32 10:00:32 AM Test3
4 07Jan1993 1/7/1993 10:15:33 10:15:33 AM Test4
5 08Jan1993 1/8/1993 10:30:34 10:30:34 AM Test5
6 01Feb1994 2/1/1994 8:00:00 8:00:00 AM Test6
7 01Mar1995 3/1/1995 9:00:00 9:00:00 AM Test7
8 01Apr1996 4/1/1996 10:00:00 10:00:00 AM test8
9 01May1997 5/1/1997 11:00:00 11:00:00 AM test9
10 01Jun1998 6/1/1998 12:00:00 12:00:00 PM test10
11 01Jul1999 7/1/1999 13:00:00 1:00:00 PM test11
12 01Aug2000 8/1/2000 14:00:00 2:00:00 PM test12
13 01Sep2001 9/1/2001 15:00:00 3:00:00 PM test13
14 01Oct2002 10/1/2002 16:00:00 4:00:00 PM test14
15 01Nov2003 11/1/2003 17:00:00 5:00:00 PM test15
16 01Dec2004 12/1/2004 18:00:00 6:00:00 PM test16
17 20Jan2005 1/20/2005 19:00:00 7:00:00 PM test17
18 25Feb2004 2/25/2004 19:00:00 7:00:00 PM test18
19 30Apr2003 4/30/2003 20:00:00 8:00:00 PM test19
20 15May2002 5/15/2002 21:00:00 9:00:00 PM test20
21 16Jun2001 6/16/2001 22:00:00 10:00:00 PM test21
22 22Jul2000 7/22/2000 23:00:00 11:00:00 PM test22
23 23Aug1999 8/23/1999 24:00:00 test23
24 24Sep1998 9/24/1998 01:00:00 1:00:00 AM test24
25 25Oct1997 10/25/1997 02:00:00 2:00:00 AM test25
26 26Nov1996 11/26/1996 03:00:00 3:00:00 AM test26
27 27Dec1995 12/27/1995 04:30:31 4:30:31 AM test27
28 12Apr1994 4/12/1994 05:45:00 5:45:00 AM test28
29 09May1995 5/9/1995 06:33:00 6:33:00 AM test29
30 21Sep1996 9/21/1996 07:55:21 7:55:21 AM test30
[/tt]

I included a wide range of samples to make sure it worked. The only issue is that CDate did not like 24:00:00.

To summerize...
- import as a text fields
- once the data is in Access, you can use CDate to convert the time
- use a function to convert the date.

After you are satisfied with the date and time conversion, you can change the SQL statement into an Append query to migrate the data into your final table.

Richard

 

[lorn]

Richard Thank you!. This solves the data conversion problem perfectly. It took me a beat to work with functions, as I never have before, but once I got it working it worked like a charm. Very much appreciate your help on this, you are very kind... karma points for you.

My only issue right now is some memory issues when appending the file. It completely fills up my temp folder when running the append... and I have about 3GB of free space on there. I think I'm going to solve this by setting the global temp variable to another much larger drive... this hopefully will solve that. The other option is to sort it into smaller files... which I can do now because I can search by date!

Thanks again, it's people like you that make these boards great!

Best regards,
Lorn

 

[willir]

Glad it worked.

and yea, many people dont realize how the temp files fill up when you run reprots and queries. Sounds like you have some major work here.

Thanks for the star.

Richard

 

[lorn]

Arrrgggh! Exporting the appended fields gives me extra fields during export. For instance, the appended fields date and time respectively are:

1/4/1993, 9:30:30 //which the function converted perfectly... again thank you richard

which unfortunately export as:

1/4/1993 0:00:00, 12/30/1899 9:30:30

I've been racking my brain on how to solve this and finally went back to the intended program where the data will be going to and found out it will accept integer date/time formats as well. Richard or anyone, how would I go about modifying the function to change the format to an integer like 19930104? I was acutally able to export as integers by deleting the delimiter fields through the advanced window... but I still get the extra time and date fields. I figure if it's in the integer format before I export then I shouldn't get any extra fields.

Anyhow, many thanks for the help on this challenging project.

Kind regards,
Lorn

ps.

No prob for the star, Richard, you definitely deserved it.

 

[willir]

Function ConverToNumber(MyDate As Variant) As String

ConvertToNumber = Year(MyDate) & Format(Month(MyDate), "00") _
& Format(Day(MyDate), "00")

End Function