Re: how to link to a csv file with a date field in it?
- From: "gllincoln" <gllincoln@xxxxxxxx>
- Date: Thu, 21 Feb 2008 22:32:05 -0800
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
"Howard" <nospam@xxxxxxxxxxxxxxxxx> wrote in message news:ndSdneJahtkFuSDanZ2dnUVZ8u6dnZ2d@xxxxxxxxxxxx
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 wrote: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" <nospam@xxxxxxxxxxxxxxxxx <mailto:nospam@xxxxxxxxxxxxxxxxx>> wrote in message news:Q_Kdneh2VotoGSHaRVnyjQA@xxxxxxxxxxxx
>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
.
- References:
- how to link to a csv file with a date field in it?
- From: Howard
- Re: how to link to a csv file with a date field in it?
- From: Howard
- how to link to a csv file with a date field in it?
- Prev by Date: Re: how to create an html template for access export
- Next by Date: Re: Percent-encoded data in import
- Previous by thread: Re: how to link to a csv file with a date field in it?
- Next by thread: Re: Exporting Reports to Word
- Index(es):
Relevant Pages
|
