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

H

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top