Importing repeat files with new data ....continued

[steve6501]

Hi

I have used access for a short while and having trouble with writing a module/macro.

I have clients who send in files (20 a day) with data. The files are all csv format and always the same headers. I import these one by one adding to my database but would like to be able to place the new files in a folder and let a macro import the files and then move the files to an archive folder.

Is this easily done, please? some of the files may not contain data (ie nil returns for bank holidays etc)

I have Access2003

Thanks

Steve

 

[SydneyGeek]

Steve, try the following:

1. Create a file import specification, which helps you deal with datatypes and skip unneeded fields, etc.
a. File > Get External Data > Import
b. Browse to a .csv file that you want to import, and step through the Wizard.
c. Once you have selected the fields and indicated whether the first row has column headings, click the Advanced button (bottom left of dialog). Here you can fine-tune the datatypes and which fields to include / skip. You can also save the import spec for later use. Take the default name or enter your own, then write it down, you will need it later.
d. Complete the import process. If it goes smoothly, you're good to go to the next step.

Place this code in a new module (Alt + F11, Insert > Module, Paste, then Save the module as basImport):

Function ImportCSVFiles() 
    Dim FilesToProcess As Integer 
    Dim i As Integer 
    Const TOP_FOLDER = "C:\Vmlogs" 'adjust folder name to suit 
    Const ARCHIVE_FOLDER = "C:\Vmlogs\Imported" 'adjust folder name to suit 
    Const DEST_TABLE = "Your table name" 'change to suit
    Const IMPORT_SPEC = "Your import spec name" 'change to suit

    
    With Application.FileSearch 
        .NewSearch 
        .LookIn = TOP_FOLDER 
        .SearchSubFolders = False 'we only want to search the top folder
        .Filename = "*.csv" 
        .Execute 
        FilesToProcess = .foundfiles.Count 
        
        For i = 1 To FilesToProcess 
          'import each file 
          DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, .foundfiles(i), True 
        Next i 
    End With 
End Function

You will need to change:
The TOP_FOLDER and ARCHIVE_FOLDER values (make sure you have created the Imported folder...)
The DEST_TABLE and IMPORT_FILESPEC constants

To run from the module, place your cursor anywhere in the code and press F5.

To run from a button, do this:

1. Create a button by dragging a Command button onto a form in Design view, wizards OFF.
2. Right-click the button, select Properties, select the Events tab.
3. Double-click the blank line next to On Click, so you see Event Procedure. Now click the Builder (...) button at the end of the row, to go to the code window.
4. In the blank line between Private Sub... and End Sub, paste this:

    basImport.ImportCSVFiles

Save the form, give it a name, and check it out.

Note: at this stage, the code does not archive the files. Working on that...

Denis

 

[SydneyGeek]

Steve, complete version of the code.

1. Place all of this (including the StrRev function) in the basImport module, replacing whatever is already there.

2-3. Make changes to constants as noted in previous post.
4. Create button as described previously.

The code now archives imported files to the Imported folder. Their names are timestamped with today's date. If you want to turn off archiving, set bArchiveFiles = False.

Function ImportCSVFiles()
    Dim FilesToProcess As Integer
    Dim i As Integer
    Dim bArchiveFiles As Boolean
    Dim sFileName As String
    Dim sOutFile As String
    Const TOP_FOLDER = "H:\Test" 'adjust folder name to suit
    Const ARCHIVE_FOLDER = "H:\Test\Imported" 'adjust folder name to suit
    Const DEST_TABLE = "tblUsers" 'change to suit
    Const IMPORT_SPEC = "CSV_Import_Spec" 'change to suit
    Const PATH_DELIM = "\"

    bArchiveFiles = True 'set to False if you DON'T want to move imported files to new folder
    
    With Application.FileSearch
        .NewSearch
        .LookIn = TOP_FOLDER
        .SearchSubFolders = False 'we only want to search the top folder
        .Filename = "*.csv"
        .Execute
        FilesToProcess = .FoundFiles.Count
        
        'check that files have been located
        If FilesToProcess = 0 Then
            MsgBox "No files found, nothing processed", vbExclamation
            Exit Function
        End If
        
        For i = 1 To FilesToProcess
          'import each file
          DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, .FoundFiles(i), True
          'archive the imported files
          If bArchiveFiles Then
            'code for archiving imported files...
            sFileName = StrRev(Left(.FoundFiles(i), Len(.FoundFiles(i)) - 4))
            sFileName = Left(sFileName, InStr(1, sFileName, PATH_DELIM) - 1)
            sFileName = StrRev(sFileName)
            sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " & Format(Date, "yyyymmdd") & ".csv"
            FileCopy .FoundFiles(i), sOutFile
            Kill .FoundFiles(i)
          End If
        Next i
    End With
End Function
    
Function StrRev(sData As String) As String
    Dim i As Integer
    Dim sOut As String
    sOut = ""
    For i = 1 To Len(sData)
        sOut = Mid(sData, i, 1) & sOut
    Next i
    StrRev = sOut
End Function

Denis

 

[steve6501]

SydneyGeek

Thankyou for the reply.

I will have a go at the weekend...been too busy with other things! Looks good to me so far.

Thanks again

Steve

 

[steve6501]

SydneyGeek

Wow...thanks...this worked first time trying

ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray: ray:


Steve

 

[Norie]

Denis

Instead of using FileCopy why not just use Name?

Also why not use Dir to extract only the filename?

sFileName = Dir(.FoundFiles(i))

sFileName = Left(sFileName, Len(sFileName)-4) & Format(Date, "yyyymmdd") & ".csv" 
sOutFile = ARCHIVE_FOLDER & "\" & sFileName 

Name .FoundFiles(i) As sOutFile

 

[steve6501]

All is now working fine with the database.

However some clients send in the datafile and then add data to a daily file (by running their output again). Obviously if I then imort this updated file I get duplicates of the original data rows as well as the new data rows.

Is there an easy way to make Access not import just the duplicated data rows but still import any 'changed' data rows. ie if the whole record exists do not import. This would be preferable to importing and then finding and deleting the duplicated rows.

Thanks

Steve

 

[SydneyGeek]

The simplest option is if one if the fields in the import table is unique, then all you need to do is set that field to not allow duplicates.

Otherwise, you can import into a temp table and run a query that updates the main table with new records only. If you need to do it this way, I can provide the required SQL for the query.

Denis

 

[naveen327]

Select File Dialog box

Hi,

I'm trying to combine Denis's Import Function with a file dialog box as seen in this article, which selects the file and puts it in a listbox http://support.microsoft.com/kb/279508, so the user selects the file but am getting errors when I try get the value from the list box. Any help would be much appreciated.

Thanks,
Naveen

 

[SydneyGeek]

Could you post your code so we can see where you are getting into trouble? It will make it easier to troubleshoot.

Denis