Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2008
    Posts
    16

    Unanswered: Importting multiple csv files automatically

    I am trying to automatically import csv files into an existing table in access. i need it to import every 20 mins or so.

    the csv files are fomatted like this

    Code:
    name,address,dob,height
    Im not really sure how to go about starting this so any ideas are greatly appreciate.



    cheers

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well, there are many ways to do this. I'd go with the VBA "Open File for Input" method since you can set up error logging and error handling better than other methods and I find that very useful for automated imports/exports.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    16
    Hi StarTrekker and thanks for the reply. Im using this

    Code:
    Sub Import_multiple_csv_files() 
         
        Const strPath As String = "C:\Addresspoint\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
         
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv") 
        While strFile <> "" 
             'add files to the list
            intFile = intFile + 1 
            Redim Preserve strFileList(1 To intFile) 
            strFileList(intFile) = strFile 
            strFile = Dir() 
        Wend 
         'see if any files were found
        If intFile = 0 Then 
             MsgBox "No files found" 
            Exit Sub 
        End If 
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList) 
            DoCmd.TransferText acImportDelimi, , _ 
            "Test", strPath & strFileList(intFile) 
    
        Next 
        MsgBox UBound(strFileList) & " Files were Imported" 
    End Sub
    to import the csv's but am now trying to figure out how to automate this. Ill also need to move the csv files to a different folder when processed.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Excellent, you can use FileSystemObject to move the files and you can call your code from a Form's On Timer event procedure.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2008
    Posts
    16
    StarTrekker heres my "fixed code" for a form button.

    Code:
    Private Sub Command3_Click()
         
        Const strPath As String = "C:\test\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
         
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv")
        While strFile <> ""
             'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
         'see if any files were found
        If intFile = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)
            
        Next
        MsgBox UBound(strFileList) & " Files were Imported"
    End Sub

    I will work on "FileSystemObject" and the "On Timer" event procedure.

    thanks for the advice. ill come back when im further.

  6. #6
    Join Date
    Jan 2008
    Posts
    16
    Hey star im using access 97 (dont ask) im trying to set the time interval for the form but theres nothing under properties???? theres not (on timer event anywhere)am i missing something ? any ideas?
    Last edited by lazynewt; 05-21-08 at 12:06.

  7. #7
    Join Date
    Jan 2008
    Posts
    16
    ok im trying without the on timer event as i just cant find the vb clock....

    heres what im trying and i realise i will need to remove the popup notice but im getting an error

    Code:
    Private Sub Command3_Click()
    'The interval for the timer (in seconds).
    Const INTERVAL = 10
    'Stores the next time to fire.
    Dim fireTime As Single
    
    'The terminator for the loop, set this to true to exit.
    Dim exitLoop As Boolean
    
    Do
     'If we have hit or passed the fireTime
     If Timer >= fireTime Then
      'Set the next time to fire by adding the interval
      fireTime = Timer + INTERVAL
      
      
        Const strPath As String = "C:\test\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
         
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv")
        While strFile <> ""
             'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
         'see if any files were found
        If intFile = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)
            
        Next
        MsgBox UBound(strFileList) & " Files were Imported"
        Else
      'Do Nothing, good time to yield to the OS.
      DoEvents
     End If
    
    Loop Until exitLoop = True
    End Sub
    its bugging out on

    Code:
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)

  8. #8
    Join Date
    Jan 2008
    Posts
    16
    Ok got ontimer event (duh) im pretty sure i can atleast get it auto processing so ill retire and finish tomorrow.

    any tips still appreciated.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Strange the the code would "bug out" on that line, since it was working for you before...

    What error message are you getting?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2008
    Posts
    16
    Hi Star trekker i had a play this morning and i managed to get it working. heres the code incase anyone else needs it.

    Code:
    Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval = 10000
    End Sub
    
    Private Sub Form_Timer()
         
        Const strPath As String = "C:\test\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
         
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv")
        While strFile <> ""
             'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
         'see if any files were found
        If intFile = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)
            
        Next
        MsgBox UBound(strFileList) & " Files were Imported"
    End Sub
    Next step is to move the files....... brb

    p.s not sure why it threw up an error i may look into that later so i can understand why it did it.

  11. #11
    Join Date
    Jan 2008
    Posts
    16
    am i even close????

    Code:
    Private Sub Form_Timer()
         
        Const strPath As String = "C:\test\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
        Dim sDestinationfolder
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv")
        While strFile <> ""
             'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
         'see if any files were found
        If intFile = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)
            
        Next
         sDestinationfolder = "C:\processed"
         
        FileSystemObject .MoveFile strFile, sDestinationfolder
    
        ' Clean Up
        Set oFSO = Nothing
    
    
    End Sub

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You need to Dim an object variable for the FSO and then use its methods:

    Dim objFSO as Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")


    Then something like:

    objFSO.MoveFile <source>, <target>
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Jan 2008
    Posts
    16
    ahh i didnt dim.

    Code:
    Private Sub Form_Timer()
         
        Const strPath As String = "C:\test\" 'Directory Path
        Const strPathout As String = "C:\processed\" 'Directory Path
        Dim strFile As String 'Filename
        Dim strFileList() As String 'File   Array
        Dim intFile As Integer 'File Number
        Dim sDestinationfolder
        Dim oFSO As Object
         ' Loop through the folder & build file list
        strFile = Dir(strPath & "*.csv")
        While strFile <> ""
             'add files to the list
            intFile = intFile + 1
            ReDim Preserve strFileList(1 To intFile)
            strFileList(intFile) = strFile
            strFile = Dir()
        Wend
         'see if any files were found
        If intFile = 0 Then
             MsgBox "No files found"
            Exit Sub
        End If
         'cycle through the list of files &   import to Access
         'creating a new table called MyTable
        For intFile = 1 To UBound(strFileList)
            DoCmd.TransferText acImportDelim, , _
            "custapps", strPath & strFileList(intFile)
            
        Next
         sDestinationfolder = "C:\processed"
        Set oFSO = CreateObject("Scripting.FileSystemObject")
         
        oFSO.MoveFile strFile, strPathout
    
        ' Clean Up
        Set oFSO = Nothing
    
    
    End Sub
    is this code a valid format

    Code:
    oFSO.MoveFile strFile, strPathout
    or should it be more like

    Code:
    oFSO.MoveFile "C:\test\", "C:\processed\"
    ??

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Both are valid assuming the variables being passed are of string data type.
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2008
    Posts
    16
    Thanks george ive gone with
    Code:
    oFSO.MoveFile "C:\test\*.*", "C:\processed\"
    which seems to be working. is this a good way to do this or is there a better one?

    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •