Linked CSV file, date format error, #Num! ... (1 Viewer)

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
I need some help with a linked table in Access.

I have a linked CSV file, in which some columns are date fields. I import it as a date/time and when I try to open the table (or run queries) it returns #Num! .

The text field in the CSV file is like this (opened in Notepad): 21/07/2009 12:00:00 PM

Is there any way I can link this text file through WITHOUT importing it as a text field? As I have the date field linked to another date table in a query.
 

Attachments

  • Extract.zip
    1.7 KB · Views: 376
Last edited:

DCrake

Remembered
Local time
Today, 11:22
Joined
Jun 8, 2005
Messages
8,632
I would check the full column that contains the date field. It may be that certain records have invalid data in them and that is what is causing the error.

If not that post a sample to look at.
David
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
I would check the full column that contains the date field. It may be that certain records have invalid data in them and that is what is causing the error.

Thanks David, I don't think the file contains a footer at that range, I know it contains one earlier on. However even if it does, the values with a valid date should still show up, right? That's the weird part.

I'll get a sample of the CSV and see if it makes more sense that way.
 

sportsguy

Finance wiz, Access hack
Local time
Today, 07:22
Joined
Dec 28, 2004
Messages
358
if you check the #num records in a text editor, are the date fields null?

access has a hard time with null date fields.

sportsguy
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
if you check the #num records in a text editor, are the date fields null?

access has a hard time with null date fields.

sportsguy
No, they're the ones with dates. The null ones are fine, blank.
 

Kiwiman

Registered User
Local time
Today, 11:22
Joined
Apr 27, 2008
Messages
799
Howzit

When you linked the table, did you check that the data type of the date field is set to Date\Time. Access will take a guess at the data type based on the contents on the first row (few rows??) and may well get it wrong.

I have just linked a csv sheet, and selected datatype double for a date field and the vlaues in the table come up with #NUM. Changing the datatype to date\time imports links them correctly.
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
Definitely did have date/time as the field data type :) My current workaround is linking it as a text field, it's cumbersome but it'll do for now :(
 

Kiwiman

Registered User
Local time
Today, 11:22
Joined
Apr 27, 2008
Messages
799
Howzit

Can you post a copy of your csv file?
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
Yep, will do it tomorrow at work. I hope I remember. :p
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:22
Joined
Aug 11, 2003
Messages
11,696
this probably has to do with the US vs Euro format of dates...

DD/MM vs MM/DD, where your date is Euro and access is using US :D
Gotta love dates :)
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region :)
 

Kiwiman

Registered User
Local time
Today, 11:22
Joined
Apr 27, 2008
Messages
799
Howzit

How about setting the text qualifier to " or ' when you are linking the csv file. If actual text values have a comma in the string it will effectively move everthing over into incorrect fields when linking.

E.g. A column with "coming, and going" without the text qualifier set will effectivley split this into two distinct fields. One field will have "Coming" and the another will have "and going"
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
How about setting the text qualifier to " or ' when you are linking the csv file. If actual text values have a comma in the string it will effectively move everthing over into incorrect fields when linking.

Done that too :) I learnt from previous CSVs which had ""s.
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:22
Joined
Aug 11, 2003
Messages
11,696
Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region :)

Bull hockey !

Yes nice and all, but Access using (semi hidden to you) MM/DD not DD/MM

Try searching any table using a query for the date #08-05-2009#
You will find it returns NOT 8 may, but Aug 5 !
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
I've added an extract of the CSV file to the first post. It's taken me a bit longer to remember than I hoped!
 

namliam

The Mailman - AWF VIP
Local time
Today, 12:22
Joined
Aug 11, 2003
Messages
11,696
Very strange, the dates seem to be valid, but still return #num!??

Taking away the PM part fixes some but not all, is there some unreadable character or something that is messing this up??

Looks very wierd... I would go with import as text and use a Dateserial + Left/right/mid functions to convert it into a real date and be over with it.
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
haha see? told you so :D
I've linked it as a text field now, is it possible to link that field to another table's date field without doing anything else? It give sme a data type mismatch at the moment, but if there's a way of doing it, that'd be great...
 

Kiwiman

Registered User
Local time
Today, 11:22
Joined
Apr 27, 2008
Messages
799
Howzit

You may want to see this link from http://support.microsoft.com/kb/89618

Imports will only be successful where the date\time format will be in a 24 hour format. They fail if you have the 1:00 PM format. Admitedly news to me.

This link also has some possible solutions.
 

tangcla

Registered User.
Local time
Today, 22:22
Joined
Sep 8, 2008
Messages
35
Thanks Kiwiman, sadly those solutions don't look as if it would work if I had a linked table and no import involved at all (i.e. query run on top of that linked table directly)
 

themurph2000

Fat, drunk, and stupid
Local time
Today, 06:22
Joined
Sep 24, 2007
Messages
181
Howzit

You may want to see this link from http://support.microsoft.com/kb/89618

Imports will only be successful where the date\time format will be in a 24 hour format. They fail if you have the 1:00 PM format. Admitedly news to me.

This link also has some possible solutions.

I had this same problem with a database program designed for Windows Mobile (since there's no Access for Mobile). I converted the field to record time into a text field. In Access, that same field is formatted as a Date/Time field (and I specify the format). Thankfully, it works.

This might be the way to go if using a military time format won't work for you.

Thanks for the suggestions! :D
 

Users who are viewing this thread

Top Bottom