By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Import multiple txt files and filename?

P: 1
Hi,

I have a question regarding import of multiple Text files.

These files all have different names but will always be found in the same location.

C:\Documents and Settings\computer\Desktop\F FILES

I have little VBA knowledge and am wondering if I can import ALL these files into an existing Access table tbl_Table via a macro?

A number of problems:

1. It does not import directly into the existing table, instead it creates a new table tbl_Table , I'd like to import directly into the existing table without using append or similar.

2. I cannot get it to import ALL files in the folder.

3. I'd like to import the first 7 characters of the filename into a field (KEY).

I've already read the Knowledgebase and found the Batch Import, but this does not help as you have to manually enter the name of each file and I have over 3,700 individual txt files.

I found code but I want to work with a text file.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdImport_Click()
  2. On Error GoTo ErrHandler
  3.  
  4. Dim oFSystem As Object
  5. Dim oFolder As Object
  6. Dim oFile As Object
  7. Dim sFolderPath As String
  8. Dim SQL As String
  9. Dim i As Integer
  10.  
  11. sFolderPath = "C:\My Documents\"
  12.  
  13. Set oFSystem = CreateObject("Scripting.FileSystemObject")
  14. Set oFolder = oFSystem.GetFolder(sFolderPath)
  15.  
  16. For Each oFile In oFolder.files
  17. If Right(oFile.Name, 4) = ".dbf" Then
  18. SQL = "Insert into [tblFORMGUIDE]" _
  19. & " Select """ & Left(oFile.Name, 7) & """ as [Key],*" _
  20. & " from " & Left(oFile.Name, Len(oFile.Name) - 4) _
  21. & " IN """ & sFolderPath & """ ""dBASE 5.0;"""
  22.  
  23. DoCmd.SetWarnings False
  24. DoCmd.RunSQL SQL
  25. DoCmd.SetWarnings True
  26. i = i + 1
  27. End If
  28. Next
  29.  
  30. MsgBox i & " dbf files were imported."
  31. Exit Sub
  32.  
  33. ErrHandler:
  34. MsgBox Err.Description
  35. End Sub
  36.  
  37.  
Any help or examples would be greatly appreciated.



Thanks.
Attached Files
File Type: zip Import Access 2000.zip (30.8 KB, 167 views)
Apr 14 '12 #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,054
mbc321,

The code below should get you started, but your real challenge is going to be transferring the KEY to the table you want to import to.

It might be possible to keep that field blank during the import, then create a recordset which queries the table, retrieving only those records that have a blank KEY, then updating that field for every record. I am not comfortable that would be the best way, but it might work.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub ImportTextFiles()
  5. On Error GoTo EH:
  6.     Dim strFileName As String
  7.     Dim strKey
  8.     strFileName = Dir(DirectoryName)
  9.     Do While strFileName <> ""
  10.         strKey = Left(strFileName,7)
  11.         DoCmd.TransferText acImportDelim, , tblTableName, DirectoryName & "/" & strFileName, False
  12.         strFileName = Dir()
  13.     Loop
  14.     'Here is where you could create your recordset to update KEY
  15.     Exit Sub
  16. EH:
  17.     MsgBox Err.Number & " " & Err.Description
  18.     Exit Sub
  19. End Sub
  20.  
Jul 25 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.