.CSV Import question (1 Viewer)

[HaRp3r]

Automated .CSV Import question

Hi all, am hoping you can help me out with this problem. I have a large number of .csv files in a folder that i need to import into a new db, with a new .csv file being added to this folder every 30 minutes. The filename's of each .csv are different so i need a macro to import everything that is already there and from that point to append the newly created files into the db as an automated job every 30 minutes.

I have tried using wildcards in the filename of a macro but the file cannot be found, am hoping there is a way to get around this in someway.

Am quite new to macro's/vb in Access so any help would be hugely appreciated.

Thanks,
HaRp3r

 

[Johnvandamme]

the heaviest but fastest way would be this :

import the csv each time name TBL_WT (worktable)
perform a unmatch query with the db data
append this data to the DB data
Empty the TBL_WT
Job done !

Good luck with it.

John

 

[gbshahaq]

you could try the following (adapted from a routine i use to import all Excel files from a given folder including subfolders)

Strictly speaking, you don't need a temp table, as import to an existing table does an append by default - but as a cautionary step it's a good idea.
Just add the code to clear the table first, run the code below to do the import and then run the append query from the VBA.

Sub ImportManyItems

Dim vaFileName As Variant, myDir As String

myDir = "\\server\files\folderx\"

With Application.FileSearch
    .NewSearch
    .LookIn = myDir
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    If .Execute > 0 Then
        For Each vaFileName In .FoundFiles
        DoCmd.TransferText acImportDelim, myspecname, mytbl, vaFileName
        Next
    End If
End With

End Sub

 

[HaRp3r]

Many thanks to you both, i will try this out as soon as i get the opportunity to work on the DB itself....lots of other work needs doing aswell

Will no doubt have more questions to ask still heh

 

[arstacey]

Newb here so be kind!

What does the line .FileType = msoFileTypeAllFiles mean? I added this code to a push button on a form and get this error:

Run-time error '5':

Invalid procedure call or argument

I am trying to import csv files so should I replace msoFileTypeAllFiles with something that says only to import csv's?

 

[gbshahaq]

Newb here so be kind!

What does the line .FileType = msoFileTypeAllFiles mean? I added this code to a push button on a form and get this error:

Run-time error '5':

Invalid procedure call or argument

I am trying to import csv files so should I replace msoFileTypeAllFiles with something that says only to import csv's?

The line tells the application which filetypes (extensions) to filter in the search box - there is no option for csv or text files - so msoFileTypeAllFiles means just that - it will display all file types.
An alternative you could try is to leave it out and substitute with:

.FileName = "*.csv"

which will then filter by the name, where filename ends with ".csv"

 

[HaRp3r]

Finally got chance to work on this some more

Shammy, that worked a treat and saved me many hours of manual imports so many thanks

Now for next little problem - getting it to work automatically with a timer.

Ideally this is how i want this to work, bearing in mind that every 30 minutes a new file is created with a slightly different filename which then needs to be imported aswell, once running the DB will not be shut down very often;

DB starts, main form opens and import code runs once, then repeats every 30 minutes

The main form is very simple, having only a few buttons to run queries & reports and minimal formatting, therefore is it possible to run the timer event/timer interval from this form, or is a 2nd form required?

Here is the slightly edited code provided by shammy so you can see exactly what i have so far;

Sub ImportManyItems()

Dim vaFileName As Variant, myDir As String

myDir = "\\myserver\Teleconnectdata\Reports\SIFT"

With Application.FileSearch
    .NewSearch
    .LookIn = myDir
    .FileName = "*.csv"
    .SearchSubFolders = False
        If .Execute > 0 Then
        For Each vaFileName In .FoundFiles
        DoCmd.SetWarnings False
        DoCmd.TransferText acImportDelim, "SIFT_2006_ Import Specification", "SIFT_2006_main", vaFileName
        DoCmd.SetWarnings True
        Next
    End If
End With

End Sub

Everytime i try to add a Form_Timer or specify TimerInterval in Form_Load i get various errors so currently they are set as event procedures in the form properties.

Any and all help/suggestions appreciated