Results 1 to 6 of 6
  1. #1
    Ada01 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    8

    Import Procedure - Variable File Names

    I receive a file into a network directory each week (once a month, I receive an additional two files).

    I would like to write an import procedure in a language Access can handle that will pull in the first file in the directory, import it, apply the correct data to the data records (based on the file name) and then move the file to an archive folder.

    I have never tried to write a procedure like this and have not been successful in finding anything in the resources i have

    The file name is similar to this for each and every file: "F150253S.TXT"

    F = system operand (always "F" for each file)
    15 = year (this will change with the year and is part of the date function I need to write)
    025 = Julian Day. (I have the Julian calendar for 2013-2020 as a table. I need the records to be appended with 1/25/2015 in this case. 032 would need to = 02/01/2015
    3 = "ghost" character in the system that was part of the original development, the BA can't tell me what it means, but it is part of every file


    S = report type. This can be "S", "M", or "B" - "M" or "B" occur once every four weeks.

    There needs to be some fixed column splits done to the text files and there are no headers.

    Is this programming possible, or is this beyond the realm of access?

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am sure it is possible although not simple.

    One step at a time. The first step is figuring out how to determine the file name for import - user input to textbox or select with FileDialog or dynamically construct with VBA? For VBA to construct file name, would need consistent rules to base algorithm on. Maybe some user input cannot be avoided. The report type seems to be the least fixed element.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with June.

    Step 1 - select the path to the files (see attached dB)

    Step 2 - open the text file and import the data

    Step 3 - Move the file to the Archive folder


    The attached dB shows how to pick the folder the files are in. The default is to use a folder picker; you can use a hard coded path (constant) or get the path from a table.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ssnafu's given you a good example of how to pick a folder and how to extract the elements you want, to add to this the functionality of processing files you need the functionality of filesystemobject commands to cycle through all files in a folder

    Code:
    dim fs
    dim fsFolder
    dim fsFile
    dim sPath
    
    'you can inherit this value from the field on Ssnafu's form by changing it to 'spath = txtpath if you remove the f*.txt operator from the end
    spath = "C:\FILEFolder\"
    
    set fs = createobject("Scripting.filesystemobject")
    set fsfolder = fs.getfolder(sPath)
    
    for each fsfile in fsfolder.files
        sCurrFile =  fsfile.name
        Debug.print scurrfile
        'do whatever you're going to do with the file
    next fsfile
    you can also use filesystemobject commands to move/copy/delete file using fs.copyfile etc.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There is also VBA FileCopy function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Revised dB....

    I used the DIR function to get the rest of the files to process (if any).

    I used the Name statement to move the processed file.

    Here is the select folder code (without the "modBrowseFolder" required module - see dB).
    Code:
    Option Compare Database
    Option Explicit
    
    '**************************************************
    ' NOTE: requires a reference to Microsoft Office xx Object Library
    '**************************************************
    
    Private Sub cmdImport_Click()
    
        'set this to your default path
        Const SourcePath As String = "C:\Accmdb\TestImport"
    
        Dim SystemOperand As String
        Dim FileYear As String
        Dim JulianDay As String
        Dim ghost As String
        Dim Reporttype As String
    
        Dim strImportFileName As String
        Dim tmpFilePath As String
        Dim sPath As String
        Dim OldFile As String, NewFile As String
        Dim ArchivePath As String
    
        Dim How2PickPath As Integer '
        'use default path or pick the path to the folder
        '1  = folder picker
        '2 = use constant path
        '3 = get path from table
        How2PickPath = 1
    
    
        Select Case How2PickPath
            Case 1  ' user selects the path
                sPath = CurrentProject.Path & "\"
                sPath = BrowseFolderExplorer("Select a Folder", msoFileDialogViewPreview, sPath)
    
            Case 2  'hard code path
                sPath = SourcePath
    
            Case 3  'look up from table
                sPath = DLookup("SourcePath", "tblPaths")
    
        End Select
    
        'where to move the files TO
        ArchivePath = DLookup("ArchivePath", "tblPaths")
    
        strImportFileName = ""
    
        If Len(Trim(sPath)) > 0 Then
    
            tmpFilePath = sPath
            sPath = sPath & "\F*.txt"
    
            Me.txtPath = sPath
    
            'gets the first file that matches the specifications.
            strImportFileName = Dir(sPath)
    
            Do While strImportFileName <> ""
                '-------------------------
                'these are optional - just here to debug the code.
                Me.txtFileName = strImportFileName
                Me.txtSystemOperand = Left(strImportFileName, 1)
                Me.txtFileYear = Mid(strImportFileName, 2, 2)
                Me.txtJulianDay = Mid(strImportFileName, 4, 3)
                Me.txtghost = Mid(strImportFileName, 7, 1)
                Me.txtReporttype = Mid(strImportFileName, 8, 1)
                '-------------------------
    
                '---------------------------------
                ' Here is where you would process the text file
                ' and put the data into the correct tables
                '---------------------------------
    
                ' give time to close the text file
                DoEvents
    
                '---------------------------------
                ' here you would move the text file to an archive folder.
                OldFile = tmpFilePath & "\" & strImportFileName
                NewFile = ArchivePath & "\" & strImportFileName
    
                Name OldFile As NewFile
                '---------------------------------
    
                'Get the next file to process
                strImportFileName = Dir
    
            Loop
    
            MsgBox "Done"
    
        End If
    
    End Sub

    ************************************
    @rpeare,
    I know I make mistakes, but its "ssANfu" not "Ssnafu. Please smack your computer and give it a time out!!

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

Similar Threads

  1. Replies: 4
    Last Post: 01-03-2012, 08:11 PM
  2. Import File Names
    By cassidym in forum Import/Export Data
    Replies: 3
    Last Post: 01-11-2011, 03:19 PM
  3. Return Procedure variable to VBA
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 6
    Last Post: 06-15-2010, 08:49 AM
  4. Refering to variable form names inside a variable
    By redpetfran in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 01:39 PM
  5. Passing variable values to Stored Procedure
    By rodrigopcnet in forum Access
    Replies: 1
    Last Post: 04-14-2010, 10:35 AM

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