Solved

Importing Multiple CSV files into Access 2007

Posted on 2011-03-22
7
1,046 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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