Solved

Importing Multiple CSV files into Access 2007

Posted on 2011-03-22
7
1,039 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
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 …

786 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