Solved

Importing Multiple CSV files into Access 2007

Posted on 2011-03-22
7
1,041 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:blastmasterJUS1
7 Comments
 
LVL 10

Expert Comment

by:t_hungate
ID: 35194256
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
 

Author Comment

by:blastmasterJUS1
ID: 35194873
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
 

Author Comment

by:blastmasterJUS1
ID: 35194981
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 26

Expert Comment

by:jerryb30
ID: 35195109
Are they the only files in the tree?
If you change (for test purposes) to a sub-folder, does it work?
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 500 total points
ID: 35207637
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
 
LVL 26

Expert Comment

by:jerryb30
ID: 35208192
I think you'll need the Microsoft Scripting Runtime set as a reference.
0
 

Expert Comment

by:mmhashem2k
ID: 36521247
Hi jerryb30, how can i use the code you porvided here
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question