Import multiple text files automatically

[instructorTek]

Hi RuralGuy everything worked out great, sorry I didn't let you know sooner, I can now finish my app, all thanks to you.

[RuralGuy]

That's great! Are you ready to use the "Solved" thread tool?

[jeraldc]

That's great! Are you ready to use the "Solved" thread tool?

RuralGuy,

Your code is awesome. However, your code let's me select the directory, and once I do, I run into this error:



I'm assuming your code will grab all the text files, why is it looking for a particular filename?? Thanks a lot.

[RuralGuy]

Talk about a time machine. Did you single step the code? Is it importing anything or does it hang up on the 1st attempt.

[jeraldc]

Talk about a time machine. Did you single step the code? Is it importing anything or does it hang up on the 1st attempt.

Haha yes :-), this article is still useful! Here is your code, and it gives me the error on the first attempt.

Public Sub NIPSImport()
'-- Import all of the NIPS raw data to the NIPS table.
Dim OurFolder As String
Dim OurFile As String
'-- First have the user select the folder
OurFolder = BrowseFolder1("Select the folder that contains the NIPS Text Files.")
'-- Dir returns a ZLS ("") when no more files match the criteria
OurFile = Dir(OurFolder & "\*.txt")
Do While OurFile <> ""
DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", OurFile
'-- Get the next *.txt filename
OurFile = Dir
Loop
End Sub


However, this code doesn't error out when I manually import a single file using the text import wizard, from there somehow it recognizes the object name and then your code works on the entire directory. Any ideas? Thanks.

[RuralGuy]

Is there a nips_030720 12.txt file and does it have an embedded space in the name? Does the NIPS table already exist?

[jeraldc]

Is there a nips_030720 12.txt file and does it have an embedded space in the name? Does the NIPS table already exist?

Yes, the NIPS table already exists. Here's an image of the folder. Any idea how to fix this? Note if this helps, I'm using Access 2010 but saved the DB as 2003 compatible.

[RuralGuy]

It sounds like you have done everything correctly. I'm sorry but I don't know what could be causing the problem at this time.

[jeraldc]

Yeah I know, it all looks good... Thanks again!

[ssanfu]

Thanks Allan. This code will save me some time. I've been wanting to have an option like this for a while, but haven't taken the time to search/ write my own code.

jeraldc

I just ran the code after a little modification:

      MsgBox "found file > " & OurFile
'      DoCmd.TransferText acImportDelim, "ScoreImportSpecs", "tblScores", OurFile

I set a break point at the line
OurFolder = BrowseFolder1("Select the folder that contains the scores.")

It stepped through without error.
Which line does the error occur?

When single stepping (SS) through the code, watch the "local window" pane in the IDE. Does the data in the variables look correct?

Care to share the mdb, with just the form, table and a couple of the text files? Munge the sensitive data in the text files or add test data.

[jeraldc]

The error occurs in this line.

DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", OurFile

Access is looking for the object 'nips.txt' filename ... here it is. Nothing personal or sensitive in it since it's public information.DRAFT - NIPS-STAMPS Recon v01.mdbnips_02012012.txtnips_03132012.txtnips_03152012.txt

[ssanfu]

So I had to open the mdb in A2K7, then convert it to A2K.
When you open the mdb, open the IDE and check the references. Since I have A2k, and you have A2K3, there will probably have a missing reference.


OK, things I found:

You were missing the global declarations.

You cannot name a module the same name as a function/sub. I put both import functions in the same module and renamed it "ImportFunctions" (no spaces) .

You should always have "Option Explicit" at the top of every code page (I added it). There is a setting that adds the line to any new code page.

------------

I modified the code from Allan a little to report the file being processed - the file name is displayed in the status bar area.
And I modified the delete routine. You are actually clearing the tables, not the database. Picky, picky.....I know

So it seems to work for me......
How about you??

[jeraldc]

Hey ssanfu,

Thanks a lot! I didn't get an automated response from the server on your comment so I was going out solving it myself (wish I saw your response here first, but I was able to enhance my code a bit and make it work!)

This code works beautifully below! I found out (you are right as well) that the "\*.txt" was not identifying an object for the text file in the code OurFile = Dir(OurFolder & "\*.txt"). So I created and reference the object name below.


Public Sub NIPSImport()
On Error GoTo bImportFiles_Click_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim OurFolder As String

OurFolder = BrowseFolder1("Select the folder that contains the NIPS Text Files.")
strFolderPath = OurFolder & "\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", strFolderPath & objF1.Name, False
'Check if archive folder exists, if not create one
If Dir(OurFolder & "\Archive", vbDirectory) = "" Then
MkDir (OurFolder & "\Archive")
Else
'Do nothing if the directory already exists
'MsgBox "Archive directory already exists"
End If
'Move the files to the archive folder
Name strFolderPath & objF1.Name As OurFolder & "\Archive\" & objF1.Name
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing


bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Sub

[VetRalph]

Hello RuralGuy,

I am new to this forum and to Access 2010. I have used Ascess before but an older version.
This code works Great. I have one question. Is there a way to eliminate the import screen telling how many records were imported and not imported?

Thanks
Ralph

[VetRalph]

Put the following code in a standard module name basImport.

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.

Hello RuralGuy,

I am new to this forum and to Access 2010. I have used Ascess before but an older version.
This code works Great. I have one question. Is there a way to eliminate the import screen telling how many records were imported and not imported?

Sorry if I am messing up the post.

Thanks
Ralph