Solved

Importing Multiple CSV files into Access 2007

Posted on 2011-03-22
7
1,036 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now