Multiple Text File Import Code that needs tweeking to accomodate Access 2010!

[bdhFS]

Novice to Programming
Access 2010
Windows XP

Source of Code: http://www.datawright.com.au/access_...text_files.htm

I am trying to Import multiple lots .csv files at once (headers are exactly the same) and this occurs frequently. I am a novice when it comes to programming but I can usually find something on the internet that will me my needs. This code below I found and it looks like exactly what I need...however, the code bombs on <With Application.FileSearch> which I think no longer is used in Access 2010. I have done some searching and found several sites that have replace text, but I am never sure I it fits into the grand scheme of this code. At least when I try and replace code it is full of errors. So can someone help me make this code work for Access 2010.




Option Compare Database

Function ImportCSVFiles()
Dim FilesToProcess As Integer
Dim i As Integer
Dim bArchiveFiles As Boolean
Dim sFileName As String
Dim sOutFile As String
Const TOP_FOLDER = "C:\Imports" 'folder location of files to import
Const ARCHIVE_FOLDER = "C:\ArchivedImportedTextFiles" 'folder location of files that have been exported
Const DEST_TABLE = "tblMetImports" 'table files will be imported into
Const IMPORT_SPEC = "MetImportSpec" 'Import Specifications
Const PATH_DELIM = "\"

bArchiveFiles = True 'Reminder, set to False if you DON'T want to move imported files to new folder

With Application.FileSearch
.NewSearch
.LookIn = TOP_FOLDER
.SearchSubFolders = False 'we only want to search the top folder
.FileName = "*.csv"
.Execute
FilesToProcess = .FoundFiles.Count

'check that files have been located
If FilesToProcess = 0 Then
MsgBox "No files found, nothing processed", vbExclamation
Exit Function
End If

For i = 1 To FilesToProcess
'import each file
DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, .FoundFiles(i), True
'archive the imported files
If bArchiveFiles Then
'code for archiving imported files...
sFileName = StrRev(Left(.FoundFiles(i), Len(.FoundFiles(i)) - 4))
sFileName = Left(sFileName, InStr(1, sFileName, PATH_DELIM) - 1)
sFileName = StrRev(sFileName)
sOutFile = ARCHIVE_FOLDER & PATH_DELIM & sFileName & " " & Format(Date, "yyyymmdd") & ".csv"
FileCopy .FoundFiles(i), sOutFile
Kill .FoundFiles(i)
End If
Next i
End With
End Function

Function StrRev(sData As String) As String
Dim i As Integer
Dim sOut As String
sOut = ""
For i = 1 To Len(sData)
sOut = Mid(sData, i, 1) & sOut
Next i
StrRev = sOut
End Function

[ssanfu]

I couldn't test the copy and Kill part of the code.. I have A2000.

I used an array to hold the file names. Kinda brute force - but it should work..

the code in blue I added or changed. I modified the code so the function "StrRev" isn't needed

Option Compare Database
Option Explicit
Option Base 1  '<<--this is necessary

Option Compare Database
Option Explicit
Option Base 1

Function ImportCSVFiles()
   On Error GoTo ItBroke

   Const TOP_FOLDER = "C:\Imports"   'folder location of files to import
   Const ARCHIVE_FOLDER = "C:\ArchivedImportedTextFiles"   'folder location of files that have been exported
   Const DEST_TABLE = "tblMetImports"   'table files will be imported into
   Const IMPORT_SPEC = "MetImportSpec"   'Import Specifications
   Const PATH_DELIM = "\"

   Dim FilesToProcess As Integer
   Dim i As Integer, x As Integer
   Dim bArchiveFiles As Boolean
   Dim sFileName As String
   Dim sOutFile As String

   Dim txtFileArray() As String
   Dim blDimensioned As Boolean

   bArchiveFiles = True   'Reminder, set to False if you DON'T want to move imported files to new folder
   blDimensioned = False

   sFileName = Dir(TOP_FOLDER & "\*.csv")
   Do While Len(sFileName) > 0
      If blDimensioned = True Then
         'Yes, so extend the array one element large than its current upper bound.
         'Without the "Preserve" keyword below, the previous elements in our array
         'would be erased with the resizing
         ReDim Preserve txtFileArray(1 To UBound(txtFileArray) + 1) As String
      Else
         'No, First time through loop...so dimension it and flag it as dimensioned.
         ReDim txtFileArray(1 To 1) As String
         blDimensioned = True
      End If
      'Add the file name to the last element in the array.
      txtFileArray(UBound(txtFileArray)) = TOP_FOLDER & "\" & sFileName
      sFileName = Dir
   Loop

   ' get number of files
   FilesToProcess = UBound(txtFileArray)
   'check that files have been located
   If FilesToProcess = 0 Then
      MsgBox "No files found, nothing processed", vbExclamation
   Else
      For i = 1 To FilesToProcess
         'import each file
         DoCmd.TransferText acImportDelim, IMPORT_SPEC, DEST_TABLE, txtFileArray(i), True
         'archive the imported files
         If bArchiveFiles Then
            'code for archiving imported files...
            sFileName = Mid(txtFileArray(i), InStrRev(txtFileArray(i), "\"), InStrRev(txtFileArray(i), ".") - (InStrRev(txtFileArray(i), "\")))

            sOutFile = ARCHIVE_FOLDER & sFileName & "_" & Format(Date, "yyyymmdd") & ".csv"
            '            Debug.Print sOutFile
            
            'not sure if the following will still work correctly
            FileCopy txtFileArray(i), sOutFile
            Kill txtFileArray(i)
         End If
      Next i
   End If

Exit_ItBroke:
   Exit Function


ItBroke:
   If Err.Number = 9 Then 'subscript out of range
      MsgBox "No files found, nothing processed", vbExclamation
      Resume Exit_ItBroke
   End If

   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMultiple"
End Function

[bdhFS]

I will makes changes and see what happens, thanks for you response.

[bdhFS]

The code worked, but I guess not totally as I thought it would. Under "C:\Imports"

are another set of 100s of folders identified by download date. Within each folder sets the .csv files that needs to be imported. So, you did answer my question! Code may need some tweeking still, I guess.

[ssanfu]

Sorry. I missed the search subfolder requirement...

I don't have the tables, or import specs but I think this will do what you want.

[bdhFS]

BRAVO!!! You made my day! Exactly what I needed. You rock and your help is very much appreciated.

(Quick question, is it easy to add the folder it came from as well in the Archived Files?)

[ssanfu]

(Quick question, is it easy to add the folder it came from as well in the Archived Files?)
Not sure I understand the question.
You want to create a folder with the same name in the archive folder, then move the imported files into the same folder name in the archive location?

Should be able to with a little more code. How will you handle source folders with the same name?

[bdhFS]

Currently the Import folder contains 100s of folders, and each folder holds 12 files with that exact same name. So when the are getting moved in the code they are getting over written by the next set of uploads.

Folder 20111402
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

Folder 20111502
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

etc...

[ssanfu]

So if the top leverl folder is C:\Imports
with subfolders

C:\Imports\Sub1
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

C:\Imports\Sub2

C:\Imports\Sub2\sSub1
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

C:\Imports\Sub2\sSub2
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

C:\Imports\Sub3

What do you want to see in C:\ArchivedImportedTextFiles?

Do the folder names change?

I will try and look a this tonight..... sometime between and .

[ssanfu]

Because of the continuing questions, I marked this as unsolved - for now.

[bdhFS]

Yes, I put them in a folder under C:\Project\Imports and they are going to C:\Project\ArchivedImportedTextFiles

C:\Project\Imports\20111402
ZZZ0012.csv
BBB0235.csv
EEE8296.csv

C:\Project\Imports\20111502
ZZZ0012.csv
BBB0235.csv
EEE8296.csv


C:\Project\ArchivedImportedTextFiles

20111402_ZZZ0012_20130429.csv
20111402_BBB0235_20130429.csv
20111402_EEE8296_20130429.csv
20111502_ZZZ0012_20130429.csv
20111502_BBB0235_20130429.csv
20111502_EEE8296_20130429.csv

[ssanfu]

You just want to add the folder name to the archived name??? OK, think I understand now.....

[ssanfu]

I found that the code would only search for the csv files in the top folder and the first level subfolder.
So I modified the code again.