Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8

    Question Import multiple text files automatically


    Hi good day I have several student text files stored in a folder. The text files are named with the student names and contain students quiz information (first name, last name, class, score) e.g.
    file name: james,frank
    file contents: frank ,james,4A,75
    I would like to create a Ms Access app that automatically opens each text file and saves the file information within a table in the database, i.e. all the student information will be compiled into a single database table. Thanks in advance and help will be appreciated.
    instructorTek

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Do you know how to create an import specification?

  3. #3
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8

    Import multiple text files automatically

    Hi good day, unfortunately I do not know how to create an import specification that would achieve this task automatically.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The automatic part would be handled with code but it will need an existing import specification in order to work. Step one, create the import specification and save it. Then we can look at the filenames and their occurance to see what it will take to automate the process.

  5. #5
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8

    Import multiple text files automatically

    Well each text file contains comma separated data as follows:
    first name, last name, class, score
    and it imports into a table with the same field names.
    Each text file only contains data for one student.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So do we look for the files *.txt in the directory? How will you know it needs to be done again? I would strongly suggest you *not* use embedded spaces in any names in Access (fields, tables, procedures, etc.)

    Edit: BTW, your indicated filename sample was james,frank. I doubt the comma would be allowed in the FileName by the Operating System.

  7. #7
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8
    Yes we can look for all text files within a directory eg. C:\StudentFiles\*.txt
    The names of the access fields actually will not have spaces eg. FirstName, LastName etc. I actually have student text files names such as james,frank etc using Win Xp Pro Operating System but the file names could very well be underscored such as james_frank

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by instructorTek View Post
    I actually have student text files names such as james,frank etc using Win Xp Pro Operating System but the file names could very well be underscored such as james_frank
    I'll be darn...sure enough. WinXP lets you do it. I didn't think it would. A string parser might get confused since comma is a very common field delimiter. Under_Score is probably better.
    So how do we know it needs to be done again? Is this import going to be under a button or just magically happen when needed?

  9. #9
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8
    When all the students complete their assigned quiz all the text files will be saved to the folder. A button import will suffice.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    And how did you plan to avoid duplication (pushing the button too many times)?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Sorry but I have to go run some errands. I'll be back in a few hours.

  12. #12
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8
    Hmm, I really didn't cater for duplications any ideas will be appreciated. The user just needs it to compile the files so its their responsibility to do it once I guess. Thanks for all the help so far, I really hope you can help me solve the problem. I have to go teach a class, will be back later too. Thanks again.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Put the following code in a standard module name basImport.
    Code:
    Option Compare Database
    Option Explicit
    '************** Code Start **************
    'This code was originally written by Terry Kreft.
    'It is not to be altered or distributed,
    'except as part of an application.
    'You are free to use it in any application,
    'provided the copyright notice is left unchanged.
    '
    'Code courtesy of
    'Terry Kreft
    Private Type BROWSEINFO
       hOwner As Long
       pidlRoot As Long
       pszDisplayName As String
       lpszTitle As String
       ulFlags As Long
       lpfn As Long
       lParam As Long
       iImage As Long
    End Type
    Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
                                                 "SHGetPathFromIDListA" (ByVal pidl As Long, _
                                                                         ByVal pszPath As String) As Long
    Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
                                               "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
                                               As Long
    Private Const BIF_RETURNONLYFSDIRS = &H1
    Public Function BrowseFolder1(szDialogTitle As String) As String
       Dim X As Long, bi As BROWSEINFO, dwIList As Long
       Dim szPath As String, wPos As Integer
       With bi
          .hOwner = hWndAccessApp
          .lpszTitle = szDialogTitle
          .ulFlags = BIF_RETURNONLYFSDIRS
       End With
       dwIList = SHBrowseForFolder(bi)
       szPath = Space$(512)
       X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)
       If X Then
          wPos = InStr(szPath, Chr(0))
          BrowseFolder1 = left$(szPath, wPos - 1)
       Else
          BrowseFolder1 = vbNullString
       End If
    End Function
    '*********** Code End *****************
     
    Public Sub GetScores()
    '-- Import all of the student scores to the tblScores table.
       Dim OurFolder As String
       Dim OurFile As String
       '-- First have the user select the folder
       OurFolder = BrowseFolder1("Select the folder that contains the scores.")
       '-- Dir returns a ZLS ("") when no more files match the criteria
       OurFile = Dir(OurFolder & "\*.txt")
       Do While OurFile <> ""
          '-- I created and saved the ScoreImportSpecs earlier.
          '-- I also created the tblScores table with the following
          '-- four text fields:
          '-- FirstName, LastName, ClassCode, Score
          DoCmd.TransferText acImportDelim, "ScoreImportSpecs", "tblScores", OurFile
          '-- Get the next *.txt filename
          OurFile = Dir
       Loop
    End Sub
    Then call GetScores from the Click event of a CommandButton.

    You will need to create the tblScores first and the Import Specification and save it as ScoreImportSpecs.

  14. #14
    instructorTek is offline Novice
    Windows XP Access 2003
    Join Date
    May 2009
    Posts
    8
    Hi RuralGuy, thanks so much for all your assistance. I will try it out and let you know how it works. Thanks again,
    instructorTek

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you ever get this working to your satisfaction?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-28-2009, 05:42 PM
  2. linking forms by ID - automatically add ID
    By jlcaviglia-harris in forum Forms
    Replies: 0
    Last Post: 04-17-2009, 03:51 PM
  3. Importing multiple files at once
    By NoiCe in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2009, 10:10 AM
  4. Have database open automatically
    By Norman Masson in forum Access
    Replies: 6
    Last Post: 01-08-2008, 07:41 AM
  5. Import multiple dbf files into existing table?
    By sbg2 in forum Import/Export Data
    Replies: 0
    Last Post: 06-12-2006, 02:06 PM

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