how to link to a csv file with a date field in it?

[Howard]

A MIS I am using exports tabular data as a csv file each night with
every field delimited with double quotes.

I need to link a table in my access database to this file to give an up
to date snapshot.
Problem is one of the fields represents a date (in long date UK format
e.g. 20 February 2008).

I have set the import specification under 'advanced' to say this field
is date/time but when I look at the table that field is filled with #Num!

I need it as a date for subsequent processing. How can I make it read it
as a date.

(I did get it to work by linking it all as text and then running a
Cdate() query on it but this is not the way I want to go)

Howard

 

[gllincoln]

Hi Howard,

I think you have the right idea - link to the csv, maybe run an append query to process the contents into your main table, in that query you can fix things such as the date.

The next alternative I can think of would be to write your own import routine. I've been known to do that sort of thing. You have more control, it's certainly a good learning experience, but it is time-consuming.

You would need to tell your function the table name, the filename, and whether the table contained a header or not. Then open the file and start peeling away at it.

Here is a chunk of code to get you started - (the code uses the older style DOS open file for input as #1, fh contains the returned value from the freefile() function. s is the complete row of data as a string, flag is a boolean that is initialized to be False. If you can follow the logic of the code snippet below then you can probably write the rest of the function yourself. x is an integer that marks the ordinal position of the col in the fldArray and is initialized as 0. We can create a recordset object and set it to a specific table - we add the new row one field at a time, testing each field for type using a select case, add the # for dates or ' for text or nothing for numeric field types to the fldArrray(i) element contents and the set rs.fields(i) = the resulting expression.

NOTE! This will only work where the output has double-quotes on each and every column as you stated - otherwise it's going to break with a loud CRACKING sound! The full code I snipped this from (and adapted it a little) contains so much checking for quotes and # hashes and internal commas and internal single quotes replacing certain chars, that it's difficult for me to follow and I wrote it.

fh = freefile()
open myFile for input as #fh
do while not.eof(fh)

Line Input #fh, s
For i = 1 To Len(s)
If Mid(s, i, 1) = Chr$(34) Then
flag = Not flag
i = i + 1
If flag = True Then x = x + 1
End If
If flag = True Then fldArray(x) = fldArray(x) & Mid(s, i, 1)
Next i

'rs.Addnew, apply the fldArray
'to the recordset rs.fields(i) = fldArray(i)
'at end of the count of elements rs.Update

Loop
Close #fh
set rs = nothing

 

[Howard]

Thank you, It looks like that's what I'll have to do. I can follow your
code although using code requires access to automatically run it every
day somehow rather than the data just being there in the morning as it
would with a link.

Odd that Access can't do it. No point in an import spec if it's ignored.
(I did find out the MIS will export to excel but not save an xls, just
open excel with the data in it. Shame this is happening at 2am !)

Howard

 

[gllincoln]

Hi Howard,

You could set up a small front end MDB that had your import code function in
it, set to automatically run upon opening with an autoexec macro, then after
the import is completed, have the code close the application. This could be
setup to run via Windows Task Scheduler to run at 3 AM.

Regarding the scheduler it's in XP and above - if you aren't familiar with
using the Windows Task Scheduler, here is a link to a fairly clean, simple,
short tutorial.
http://www.iopus.com/guides/winscheduler.htm

Can't beat WTS for little chores like the one we've been discussing. The
biggest gotcha is that you have to be sure that your system's power
management and green settings don't prevent the event from firing at the
appointed time.

Hope this helps...
Gordon