Results 1 to 8 of 8

Import files, partially automated, unknown filenames but with keywords.

  1. #1
    rdougherty is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    4

    Import files, partially automated, unknown filenames but with keywords.

    Hello,



    I have a fair amount of Access experience, with a little VBA mixed in (but not much). Here is what I am hoping to do with imports, so that users will be required to perform minimal clicks to get .csv files processed.

    • Form opens upon opening database (I know how to do this)


    either

    • user select folder location of files via browse button/dialog. (prefer to select folder only)

    database uses this value chosen by the user to grab files in that folder which meet criteria of certain keywords. Ex. "39920_vendor_20161230.csv" and "39920_voucher_20161230.csv".

    or
    • two browse dialog boxes to find one of each type of file, which is an extra step but perhaps more precise.


    Then
    • I need VBA to instruct the importation of these files. I know I'd need to define the values in each of the browse dialog boxes as a variable, and then use that in the vba as object to import, but I do not have a lot of VBA experience and it has been a while since I've done any...


    Can someone please inform me of how to create the browse boxes and store those values, and also point me to VBA examples of importing data using a varying file name that is defined by the previously mentioned user selected values from the boxes?

    Any help is appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    4,332
    user clicks SELECT FILE button to select file to import
    filepath is stored in textbox on the form until user click IMPORT button
    code will determine what kind of file (vendor / voucher)
    save file to generic filename that is already attached as table
    run append query to collect data

    first ,create generic files of each, they get overwritting everytime.
    these 2 files are attached as tables.
    when user selects a file, it copies it to the generic name (here its: c:\data\vendor.csv, etc)

    build 2 queries to append the external table to the main data.


    user picks the file
    Code:
    sub btnSelectFile_click()
       txtFile = UserPick1File("c:\folder\")
    end sub

    user click IMPORT DATA button to import

    Code:
    sub btnImport_click()
    dim sTarget, sQry, sName
    const kVEND = "vendor"
    const kVOU = "voucher"
    
       if IsNull(txtFile) then 
           msgbox "No File to import"
       else
           select case true
              case instr(txtFile,kVEND )>0
                    sQry = "qaAddVendor"
                    sName = kVEND
    
              case instr(txtFile,kVOU)>0
                    sQry = "qaAddVoucher"
                    sName = kVOU
    
              case else
                    msgbox "Invalid import file"
            exit sub
        end select 
            
           filecopy txtFile, "c:\data\" & sName & ".csv"
           docmd.setwarnings false
           docmd.openquery sQry
           docmd.setwarnings true
       
           msgbox sName  & "imported" 
       endif
    end sub


    put this code into a MODULE for user to pick the fule

    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)   
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
         '.Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,031
    I need VBA to instruct the importation of these files.
    Would you explain more about the "importation" of these files?


    Does the data from each of the CSV files go to different tables?
    Does the "importation" require any processing on a line by line basis?

    If the data from the CSV files gets put into individual tables, you could set up an Input Spec for each of the two types of CSV files (I do this).
    An example
    Code:
    DoCmd.TransferText acImportDelim, "EmpImportSpecification", "tblAddsEdits", strFileName, True
    where
    "EmpImportSpecification" is the Import Specification for a specific CSV file
    "tblAddsEdits" is the table the data is imported into
    strFileName is the variable that holds the CSV file name


    Do you have a dB already created?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    rdougherty is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    4
    Thank you both for your replies. I have not yet had a chance to attempt implementation of your suggestions, but they appear to lead me in the correct direction.

    To answer Steve's questions...

    I simply need to import a vendor and voucher file and name them each something generic. Once this is accomplished, they will either overwrite existing tables or populate emptied ones (via append qry). I do not need anything to be evaluated row by row as the import takes place. I just need a nearly automated way to have a non-access dev import these two files. After this occurs, I have queries set up to parse out the data in the manner needed to transform it into a format we are currently using manual steps in Excel to complete, leaving possibility for human error (and more time).

    I do like the idea of using import specs to define parameters for each file's importation. Does the "EmpImportSpecification" param use the saved imports I create when manually importing the files, and do I merely replace this with the corresponding name? For instance, there is one column that needs to be interpreted as text that access wants to drop off leading zeros (zip code).

    Thank you both, and I will post more questions as I work on this!

  5. #5
    rdougherty is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    4
    Quote Originally Posted by ranman256 View Post

    Code:
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office 11.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    Do I need to install something special to gain access to this library of references within my version of Access? It is Office 2010 64bit. Thank you,

  6. #6
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,031
    Does the "EmpImportSpecification" param use the saved imports I create when manually importing the files,
    When you manually import a CSV file, there is a button, "Advanced", that allow you to create an import spec and save it with whatever name you want. You can specify what the field types are when creating the spec. You can then use the saved import spec when importing a CSV file. When specifying the field type in the spec, the field type must match the field type in the table.

    In my code, I execute a delete query, then import the CSV file data to a table. After that, I have code that processes the data.

    See HELP for the syntax for the "DoCmd.TransferText" command.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    rdougherty is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    4
    ranman256 et al,

    my requirements have changed slightly... (after thinking about it more and realizing my original idea will not work flawlessly). I no longer wish to have the dialog box search for any file in the user-selected folder containing the keywords "vendor" and "voucher", but rather, have the user specify which file is the vendor file, and which is the voucher file.

    A requirement I failed to articulate previously, is that when the user selects the files, I'd like a new file to export to that location later, once the files are run through a series of queries. I would like the code on this form to not only define textfile1 and textfile2 names & locations, but also parse out of that location, the folder path of those files so that that folder path can be used later as an export path for the eventual new spreadsheet.

    Can you instruct me on how to adapt what you posted above for importing files so that it does not process one of the two (vendor or voucher) files at a time, but instead, allows for the user to first pick two files on the form dialog boxes, storing both values, and then processing them with the same "Import Files" button. I do not necessarily need the code to verify that the user selected the correct file by way of evaluating that it contains the appropriate keywords ("vendor","Voucher"). (choosing the wrong file would not irrevocably break any process, and would be immediately noticeable)

    I am also at a loss for finding the library references you mentioned earlier. can you assist with that too? I do not know if my version of Access has that library...?

    Many thanks,

  8. #8
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,031
    Quote Originally Posted by rdougherty View Post
    I am also at a loss for finding the library references you mentioned earlier. can you assist with that too? I do not know if my version of Access has that library...?
    Open the IDE editor (ctl G), click on TOOLS/ REFERENCES.
    "Microsoft Office 11.0 Object Library" might be for Access 2007.
    In Access 2010, the reference is "Microsoft Office 14.0 Object Library"
    Don't know what your version will be......

    For the attached example db to work, you must have references set to
    "Microsoft Office xx.0 Object Library" and
    "Microsoft Scripting Runtime".
    See above on how to set the references....


    There are a few constants to set in the VBA code.
    In the database window, double click on "Module1".
    Look for
    Code:
    '*** change the table names to the correct tables
    
    '*** change the ImportSpec names to the correct names
    The table names and import specs must exist!!

    Scroll down to the "SELECT CASE" code. You will see (2 places)
    Code:
    '****** comment out the MSGBOX lines and uncomment the DoCmd lines  *****
    The code will not import until you make these changes.

    Before the above changes are made:
    If you click on the button ""Import", it will allow you to select files and see how the code works without doing the import.
    To select multiple files, hold down the control key while clicking on the file name(s).


    After changes:
    CSV files are (should be) imported...
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. VBA Importing .txt Files WITH FileNames
    By jdmeagher in forum Programming
    Replies: 6
    Last Post: 12-08-2015, 02:41 PM
  2. Replies: 10
    Last Post: 06-16-2014, 07:37 AM
  3. Automated import from different Excel files
    By lugnutmonkey in forum Import/Export Data
    Replies: 4
    Last Post: 02-05-2013, 09:03 AM
  4. Import all files
    By raytackettsells in forum Import/Export Data
    Replies: 4
    Last Post: 08-10-2012, 10:59 AM
  5. Automated import
    By salisbut in forum Import/Export Data
    Replies: 2
    Last Post: 07-16-2010, 11:56 AM

Posting Permissions

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