Results 1 to 6 of 6
  1. #1
    kgfyre is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    3

    Question Import Co-located Text Files to Access Database as Tables VBA

    I recently inherited three monthly recurring tasks involving 200 to 900 CSV files each. The number of files varies monthly, depending on activity on different lines of accounting. The naming conventions are static, but each task uses different naming conventions. The files must be placed in a new location each month, and the database should be co-located for the convenience of other users. I have already created/saved import specs for each task, I just don't want to do it manually. (The big one takes days...)



    I'd like to be able to bulk import my CSV files into Access (using 2010) by way of a macro, with each file landing in its own table. (Then I'll need to do a couple of other things to them, but I think I can do that via queries.)

    I've found the macro at this link: http://www.eraserve.com/tutorials/MS..._All_Files.asp
    It sounds like it should do what I'm looking for, but I'm running into an error when I try to debug it. (A variable not defined.) It also doesn't appear to be supported for 2010.

    I have some coding experience in a variety of languages, but VBA is new for me. This is the first time I've attempted macros or VBA of any kind in Access. (I'm an Access neophyte...) My VBA experience is limited to Excel up to now.

    I've found a variety of related threads, but I just don't understand what's going on.

    I think I need something that:
    1. Counts the number of .csv files in a folder where the database is to establish a looping protocol.
    2. Imports those .csv files as tables in the database, using a defined import spec.

    Any help/suggestions would be appreciated. If you provide script that includes items I should change the name/text of, would you please indicate them somehow? (One of the problems I'm running into is that I can't tell what variable to change!)

    Thanks so much!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Here is a partial code block that I use to loop through a temp folder. There are different ways to do it. This is one example. It grabs all of the CSV files. One by one it will import the file and then DELETE it. You will need to declare and initialize strFileName and strPath


    Code:
            strFileName = Dir(strPath + "*.csv", vbHidden) 'gets first csv file in path specified
            
                'I use a different validation. This validation is something I just typed out.
                If strFileName = "" Then 'validation requires strFileName to be initialized as empty string 
                MsgBox "No file found. Import Failed!", vbCritical, "Try Again"
                Exit Sub
                Else
                    Do While strFileName <> ""   'will cause to loop through all csv files in path
                    
                    DoCmd.TransferText acImportDelim, "FasTrakSpec", _
                    "tblFasTrakHistory", strPath & strFileName, 0
                    Kill strPath & strFileName  'Delete the first csv file after importing
    
                    strFileName = Dir(strPath + "*.csv", vbHidden) 'see if there is another csv file
                    Loop    'Rinse and Repeat

  3. #3
    kgfyre is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    3
    Thank you! Does this loop require the already-used file to be deleted to loop to the next one? How do I install this in Access? I tried pasting it in, but I seem to be missing parts. (And adding the sub/end sub stuff from Excel doesn't help...)

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It does require deleting the file. This is why you will want to import from a temp folder. You can create a temp folder by copying the directory to a temp folder.

    You will need to create a sub procedure or a function to place your VBA code into. You will also need to declare a couple of string variables.

    Maybe a click event could get you started. In a form, create a control button and use the Event tab in the property sheet to create the event handler.

    There is an elipses(...) next to On Click. Click the elipses and select "Code Builder". This will create a sub procedure for you.

    here is an illustration where focus is on the After Update event within the property sheet.
    .
    Click image for larger version. 

Name:	AfterUpdate.jpg 
Views:	13 
Size:	34.6 KB 
ID:	15341

  5. #5
    kgfyre is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    3
    Ok - I think I'm nearly there. I got the form set up with relatively little trouble and added a button. I've pasted in your code with just a couple of switches to my spec name/table name, but I'm getting an error about BlockIf without EndIf (see illustration) What am I still missing?
    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	32.0 KB 
ID:	15342

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Like I mentioned, this is only part of the code I use in my application. You will need to adjust it to suit your app. Also, you can post your code examples here using tags. The tags will preserve the formatting and make it easier for others to look at, copy, edit, etc.

    Go Advanced then use the hashtag/pound symbol to insert tags. Then paste your code within the "Code" tags.

    The End If exception is because you have code that starts an If Then statement but does not close/end it.

    Code:
                If strFileName = "" Then 'validation requires strFileName to be initialized as empty string 
                MsgBox "No file found. Import Failed!", vbCritical, "Try Again"
                Exit Sub
                End If
    This example replaces Else with End If. The idea is to exit the procedure is a file does not exist in the folder.

    Beyond that you are going to need some string variables

    strFileName
    and
    strPath

    Need to be declared and you need to set them to equal something. This is critical because you do not want to Kill the wrong file on your computer.

    Dim strPath as string
    strPath = "C:\TestFolder\"
    Dim strFileName as string
    strFileName = ""

    Make sure you know what you are doing before you Kill strPath.

    Perhaps commenting out the Kill and Loop until after you test a single file import.

    Code:
                    'Do While strFileName <> ""   'will cause to loop through all csv files in path
                    
                    DoCmd.TransferText acImportDelim, "FasTrakSpec", _
                    "tblFasTrakHistory", strPath & strFileName, 0
                    'Kill strPath & strFileName  'Delete the first csv file after importing
    
                    'strFileName = Dir(strPath + "*.csv", vbHidden) 'see if there is another csv file
                    'Loop    'Rinse and Repeat
    This code uses my previous example and shows how to comment out the loop/iteration and the kill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-31-2013, 01:03 PM
  2. Replies: 19
    Last Post: 10-08-2012, 07:47 AM
  3. Replies: 3
    Last Post: 08-29-2011, 03:11 PM
  4. Import Text Files without access
    By 95DSM in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 04:36 PM
  5. Field returns error when I import Text Files to Access
    By geng in forum Import/Export Data
    Replies: 3
    Last Post: 06-01-2010, 02:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums