Linked CSV file, date format error, #Num! ...

T

tangcla

Registered User.
Local time
Tomorrow, 00:37
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

Last edited:
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
 
DCrake said:
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.
Click to expand...

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.
 
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
 
sportsguy said:
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
Click to expand...
No, they're the ones with dates. The null ones are fine, blank.
 
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.
 
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 :(
 
Howzit

Can you post a copy of your csv file?
 
Yep, will do it tomorrow at work. I hope I remember. :p
 
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 :)
 
Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region :)
 
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"
 
Kiwiman said:
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.
Click to expand...

Done that too :) I learnt from previous CSVs which had ""s.
 
tangcla said:
Nah, not dd/mm vs mm/dd. Both date sources are normal (dd/mm) for my region :)
Click to expand...

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 !
 
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!
 
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.
 
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...
 
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.
 
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)
 
Kiwiman said:
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.
Click to expand...

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
 
You must log in or register to reply here.

Similar threads

L
VBA - How to save in csv one of multiple worksheets in excel file.
Replies
5
Views
482
CJ_London
C
L
Some Idea using tables in sharepoint
Replies
13
Views
458
LeoM
L
S
The text file specification <name> does not exist. You cannot import, export, or link using the specification. (Error 3625)
Replies
12
Views
949
The_Doc_Man
The_Doc_Man
T
Solved Import Filepath Batch
Replies
12
Views
456
Tophan
T
D
Date and Time Format
Replies
12
Views
597
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom