Importing Multiple CSV files into Access 2007

Hello experts.

I need to import multiple CSV files into a table in Access. The CSV files are all of the same format however, they all live in sub folders within the "Export" directory.

This is what I have so far but it will only import CSV files that are in the Export directory,  not the sub directories within 'Export'.

Please help! Thanks!



Dim csvFile, csvpath
csvpath = "C:\Export"

csvFile = Dir(csvpath & "\*.csv")

While csvFile <> ""

     DoCmd.TransferText acImportDelim, "FullCSVImportSpec", "tblCSV", csvpath & "\" & csvFile, , "" '

     csvFile = Dir
Wend
blastmasterJUS1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jerryb30Connect With a Mentor Commented:
Following will import all files in selected folder, including subfolders

'************** 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
Function listfiles()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
ListFilesInFolder strfilepath, True
End Function

Public Function BrowseFolder(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))
        BrowseFolder = left$(szPath, wPos - 1)
    Else
        BrowseFolder = vbNullString
    End If
End Function
'*********** Code End *****************

Function ImportFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject

Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File

    Set FSO = New Scripting.FileSystemObject
   
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
   
    For Each FileItem In SourceFolder.Files
      if fileitem.name like "*.csv" then
       DoCmd.TransferText acImportDelim, "FullCSVImportSpec", "tblCSV", fileitem.path, , "" '
endif

'here is where you execute
Function ImportFiles()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
ImportFilesInFolder strfilepath, True
End Function
0
 
Tony HungateDirector of TrainingCommented:
You have the main concept.

Provided that your export directories are not changing, you can also use the docmd.texttransfer to import such a file with a saved import description. Just do the import once manually and press on the last wizard page the [Advanced] button to save it and give it a name for the automated import. You should only have to do a manual import of each directory once.  After you have it saved you can run it again via vba using this. You can put it on a button, or have it execute when you open a form, or the database; up to you.

 docmd.RunSavedImportExport "YourSavedExportName"

Hope that helps.

~:TLH:~

 
docmd.RunSavedImportExport "YourSavedExportName"

Open in new window

0
 
blastmasterJUS1Author Commented:
thanks. The problem is that my code will only import csv files in the export folder. I need it to look within the sub folders. They are all named differently and will change.They will always live in the Export folder though..
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
blastmasterJUS1Author Commented:
And hope I didn't cause any confusion due to the fact that I named the root folder "Export", since what i'm trying to do, is IMPORT!
0
 
jerryb30Commented:
Are they the only files in the tree?
If you change (for test purposes) to a sub-folder, does it work?
0
 
jerryb30Commented:
I think you'll need the Microsoft Scripting Runtime set as a reference.
0
 
mmhashem2kCommented:
Hi jerryb30, how can i use the code you porvided here
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.