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

Struggling to write code to import all files in a folder to an Access Table

P: 8
Hello,

I use to have a database I built several years ago that had a macro using VBA to import all text files in a folder (C:\Data\Reports\Inventory Reports) to a table in Access (Aeppays Daily Consolidated). I had a specification created (DailyAeppayImport) to import the files as delimited and it worked perfectly. After a profile rebuild by my company, I no longer have the database and have struggled now searching online for help as to how I can recreate this code. Any help at all would be greatly appreciated. It just needs to import all the text files (example name: AADMIN-863826.AEPPAYS-HOLDRPT-PASS-FWD.20170616020528.txt) whose names change daily into a single table in Access.
Jun 16 '17 #1

✓ answered by ADezii

  1. As PhilOfWalton suggests, you are getting Errors because of the Period('.') in the Filenames, not including the Extensions(*.txt).
  2. There is, however, a solution and here it goes:
    1. For each File, store it's Original Name (Absolute Path) in a Variable.
    2. Replace each Period('.') with an Empty Space(""), Replace the txt at the end of the Filename (period was removed) with .txt and assign this New Name to a Variable.
    3. Perform the TransferText() Operation on the New Filename stored in strNewName.
    4. After this Operation, Rename the New Filename back to the Original Filename.
  3. Example of Renaming Procedure:
    Expand|Select|Wrap|Line Numbers
    1. ‘File 1 (original Name)
    2. C:\TEMP\AADMIN-443826.AEPPAYS-HOLDRPT-PATSS-FWD.20170627023017.txt    
    3. ‘File 1 (New Name after Replace(), period removed, txt removed, .txt added)
    4. C:\TEMP\AADMIN-443826AEPPAYS-HOLDRPT-PATSS-FWD20170627023017.txt
    5.  
    6. ‘File 2 (original Name)
    7. C:\TEMP\AADMIN-863826.AEPPAYS-HOLDRPT-PASS-FWD.20170627023017.txt 
    8. ‘File 2 (New Name after Replace(), period removed, txt removed, .txt added)  
    9. C:\TEMP\AADMIN-863826AEPPAYS-HOLDRPT-PASS-FWD20170627023017.txt
    10.  
    11. ‘File 3 (original Name)
    12. C:\TEMP\AADMIN-8638926.AEPPAYS-HOLYDRPT-PASS-FWD.20170627023017.txt   
    13. ‘File 3 (New Name after Replace(), period removed, txt removed, .txt added)
    14. C:\TEMP\AADMIN-8638926AEPPAYS-HOLYDRPT-PASS-FWD20170627023017.txt
  4. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilespec As String
    2. Dim strPath As String
    3. Dim strOriginalName As String
    4. Dim strNewName As String
    5.  
    6. strPath = "C:\TEMP\"
    7. strFilespec = "*.txt"
    8.  
    9. strFilespec = Dir(strPath & strFilespec, vbNormal)
    10.  
    11. Do While strFilespec <> ""
    12.   strOriginalName = strPath & strFilespec
    13.   strNewName = Replace(Replace(strPath & strFilespec, ".", ""), "txt", ".txt")
    14.     Debug.Print strOriginalName, strNewName
    15.       Name strOriginalName As strNewName
    16.         DoCmd.TransferText acImportDelim, "MyImportSpec", "Table1", strNewName, True
    17.       Name strNewName As strOriginalName
    18.  
    19.       strFilespec = Dir
    20. Loop
    21.  

Share this Question
Share on Google+
15 Replies


ADezii
Expert 5K+
P: 8,623
For the sake of argument, let's assume that all of your Text Files are in a Folder named Test, namely C:\Test\*.txt. Let's also assume that you previously have defined your Import Specifications and named it My Import Specs. This specification will append all Data within all Text Files in the C:\Test\ Folder to a Table named tblResults. The Code to retrieve all Text Files in C:\Test\ and append their contents to tblResults using My Import Specs Specification is:
Expand|Select|Wrap|Line Numbers
  1. Dim strFilespec As String
  2. Dim strPath As String
  3.  
  4. strPath = "C:\Test\"
  5. strFilespec = "*.txt"
  6.  
  7. strFilespec = Dir(strPath & strFilespec, vbNormal)
  8.  
  9. Do While strFilespec <> ""
  10.   DoCmd.TransferText acImportDelim, "My Import Specs", "tblResults", strPath & strFilespec
  11.     strFilespec = Dir
  12. Loop
P.S. - Change the Value of strPath and the name of your Import Specification as appropriate.
Jun 16 '17 #2

NeoPa
Expert Mod 15k+
P: 31,418
That sounds very unfortunate. Maybe a silly question, but have you tried asking IT if they have backups of your old profile etc? Having to recreate from scratch could turn out to be a lot of work. Frustrating purely to arrive back at where you started.
Jun 17 '17 #3

P: 8
Yeah tell me about it, it has been an incredibly frustrating path to go down.
Jun 27 '17 #4

P: 8
I've utilized the code given to me by ADezii (Thank you very much for your time) and have inserted this code to work on the click of a button from my main form.

I get the following error: Run-time error '2498':

An expression you entered is the wrong data type for one of the arguments.

My code currently sits as follows:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Command0_Click()
  4.  
  5. Dim strFilespec As String
  6. Dim strPath As String
  7.  
  8. strPath = "\\tamp-oa-001\TAMJCAReportPortfolio\Aeppays\Aeppays Text Files\"
  9. strFilespec = "*.txt"
  10.  
  11. strFilespec = Dir(strPath & strFilespec, vbNormal)
  12.  
  13. Do While strFilespec <> ""
  14.   DoCmd.TransferText , acImportDelim, "DailyAeppayImport", "DailyAeppaysConsolidated", strPath & strFilespec
  15.     strFilespec = Dir
  16. Loop
  17.  
  18. End Sub
**I am importing 82 .txt files all with different names. Here is an example of one of them - "AADMIN-863826.AEPPAYS-HOLDRPT-PASS-FWD.20170627023017.txt"

Could it be an issue with the file name?
Jun 27 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
This may help with your initial question.

Expand|Select|Wrap|Line Numbers
  1.     ' Requires reference to Microsoft Office 14.0 Object Library. '
  2. Public Function ShowFileDialog(FileType As Boolean, Flters() As String, Optional Filename As String, Optional Prompt As String) As String
  3.  
  4.     Dim fDialog As Office.FileDialog
  5.     Dim varFile As Variant
  6.     Dim FileStg As String
  7.     Dim i As Integer, j As Integer
  8.     Dim StrFilter1 As String, StrFilter2 As String
  9.  
  10.     ' Set up the File Dialog. '
  11.     If FileType = True Then                  ' Files
  12.         Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  13.         With fDialog
  14.             ' Clear out the current filters, and add our own.'
  15.             .filters.Clear
  16.             i = UBound(Flters)
  17.             If (i <> -1) Then
  18.                 For j = 0 To i - 1
  19.                     StrFilter1 = Flters(j)
  20.                     If (Int(i + 1) / 2) * 2 = i + 1 = True Then
  21.                         StrFilter2 = Flters(j + 1)
  22.                         If StrFilter2 > "" Then
  23.                             If Left(StrFilter2, 1) <> "*" Then
  24.                                 StrFilter2 = "*" & StrFilter2
  25.                             End If
  26.                             If Mid(StrFilter2, 2, 1) <> "." Then
  27.                                 StrFilter2 = Left(StrFilter2, 1) & "." & right(StrFilter2, Len(StrFilter2) - 1)
  28.                             End If
  29.                         End If
  30.                     End If
  31.                     If StrFilter1 > "" And StrFilter2 > "" Then
  32.                         .filters.Add StrFilter1, StrFilter2
  33.                     End If
  34.                 Next j
  35.             End If
  36.             .filters.Add "All Files", "*.*"
  37.  
  38.         End With
  39.     Else                                    ' Folders
  40.         Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
  41.     End If
  42.  
  43.     With fDialog
  44.         ' Set the title of the dialog box. '
  45.         .Title = Prompt
  46.         .InitialView = msoFileDialogViewDetails
  47.         If Filename = "" Then
  48.             .InitialFileName = CurDir
  49.         Else
  50.             .InitialFileName = Filename
  51.         End If
  52.  
  53.         ' Allow user to make multiple selections in dialog box '
  54.         .AllowMultiSelect = False
  55.  
  56.         ' Show the dialog box. If the .Show method returns True, the '
  57.         ' user picked at least one file. If the .Show method returns '
  58.         ' False, the user clicked Cancel. '
  59.         If .Show = True Then
  60.             'Loop through each file selected and add it to our list box. '
  61.             For Each varFile In .SelectedItems
  62.                 'Me.FileList.AddItem varFile
  63.                 FileStg = varFile
  64.                 'Debug.Print varFile
  65.             Next
  66.         Else
  67.             ShowFileDialog = ""
  68.             Exit Function
  69.         End If
  70.     End With
  71.  
  72.     If FileType = False Then                    ' Folders
  73.         i = InStrRev(FileStg, ".")              ' Look for a dot
  74.         j = InStrRev(FileStg, "\")              ' Look for a slash
  75.         If i = 0 Then
  76.             ShowFileDialog = FileStg            ' No dot so should be a folder
  77.         End If
  78.     Else
  79.         ShowFileDialog = FileStg
  80.     End If
  81.  
  82. End Function
  83.  
and here is a test function
Expand|Select|Wrap|Line Numbers
  1. Function TestShowFileDialog() As String
  2.  
  3.     Dim FileType As Boolean             ' True = File, False = Folder
  4.     Dim Filename As String
  5.     Dim Prompt As String
  6.  
  7.     Dim FilterArray(3) As String
  8.  
  9.     FileType = True                 ' File
  10.  
  11.     FilterArray(0) = "Databases"
  12.     FilterArray(1) = ".AccDb"
  13.     FilterArray(2) = "Databases"
  14.     FilterArray(3) = ".Mdb"
  15.  
  16.     TestShowFileDialog = ShowFileDialog(True, FilterArray(), "C*", "Where is the file C?")
  17.  
  18. End Function
  19.  
You may also need additional code to loop through all subfolders

As to your problem, I suspect the file name and in particular the dots between 863826 and AEPPAYS and between FWD and 20170627023017after in your example

Phil
Jun 27 '17 #6

NeoPa
Expert Mod 15k+
P: 31,418
KMarks:
Yeah tell me about it, it has been an incredibly frustrating path to go down.
Interesting comment, but falls shy of answering the question. Can I assume you have asked them? What was their response?
Jun 28 '17 #7

NeoPa
Expert Mod 15k+
P: 31,418
KMarks:
I get the following error: Run-time error '2498':

An expression you entered is the wrong data type for one of the arguments.
What line of your code does this occur on?

A further comment is covered by Require Variable Declaration.
KMarks:
Could it be an issue with the file name?
Not impossible, but let's look at it in order and get the basics right first, before we move on to explore other things if necessary.
Jun 28 '17 #8

P: 8
NeoPa, sorry yes our IT department was the first place I went to. They were the ones who notified me that my previous files and databases were lost beyond repair. It sounded like it was due to us using thin clients and floating profiles? I'm not smart enough honestly to know how true that is exactly.
Jun 28 '17 #9

P: 8
NeoPa, this line in particular is the one highlighted when I get the error:

DoCmd.TransferText , acImportDelim, "DailyAeppayImport", "DailyAeppaysConsolidated", strPath & strFilespec
Jun 28 '17 #10

P: 8
Just as a test I built a quick macro to ImportExportText utilizing the first text file I want to import. I see already that I get an error that it can't find the name it's looking for. I'm beginning to think the file names that are automatically posted for us each day are the issue. AADMIN-863826.AEPPAYS-HOLDRPT-PASS-FWD.20170628025319.txt is an example of one. Each one is this file name format.
Jun 28 '17 #11

PhilOfWalton
Expert 100+
P: 1,430
Well why not have just 2 or 3 files in your folder as a test and change the name to XXX.Txt, yyy.Txt & zzz.Txt, and see if that imports OK.

That will establish whether my theory that the only "." you can have in a file name is just before the file suffix.

Phil
Jun 28 '17 #12

ADezii
Expert 5K+
P: 8,623
  1. As PhilOfWalton suggests, you are getting Errors because of the Period('.') in the Filenames, not including the Extensions(*.txt).
  2. There is, however, a solution and here it goes:
    1. For each File, store it's Original Name (Absolute Path) in a Variable.
    2. Replace each Period('.') with an Empty Space(""), Replace the txt at the end of the Filename (period was removed) with .txt and assign this New Name to a Variable.
    3. Perform the TransferText() Operation on the New Filename stored in strNewName.
    4. After this Operation, Rename the New Filename back to the Original Filename.
  3. Example of Renaming Procedure:
    Expand|Select|Wrap|Line Numbers
    1. ‘File 1 (original Name)
    2. C:\TEMP\AADMIN-443826.AEPPAYS-HOLDRPT-PATSS-FWD.20170627023017.txt    
    3. ‘File 1 (New Name after Replace(), period removed, txt removed, .txt added)
    4. C:\TEMP\AADMIN-443826AEPPAYS-HOLDRPT-PATSS-FWD20170627023017.txt
    5.  
    6. ‘File 2 (original Name)
    7. C:\TEMP\AADMIN-863826.AEPPAYS-HOLDRPT-PASS-FWD.20170627023017.txt 
    8. ‘File 2 (New Name after Replace(), period removed, txt removed, .txt added)  
    9. C:\TEMP\AADMIN-863826AEPPAYS-HOLDRPT-PASS-FWD20170627023017.txt
    10.  
    11. ‘File 3 (original Name)
    12. C:\TEMP\AADMIN-8638926.AEPPAYS-HOLYDRPT-PASS-FWD.20170627023017.txt   
    13. ‘File 3 (New Name after Replace(), period removed, txt removed, .txt added)
    14. C:\TEMP\AADMIN-8638926AEPPAYS-HOLYDRPT-PASS-FWD20170627023017.txt
  4. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFilespec As String
    2. Dim strPath As String
    3. Dim strOriginalName As String
    4. Dim strNewName As String
    5.  
    6. strPath = "C:\TEMP\"
    7. strFilespec = "*.txt"
    8.  
    9. strFilespec = Dir(strPath & strFilespec, vbNormal)
    10.  
    11. Do While strFilespec <> ""
    12.   strOriginalName = strPath & strFilespec
    13.   strNewName = Replace(Replace(strPath & strFilespec, ".", ""), "txt", ".txt")
    14.     Debug.Print strOriginalName, strNewName
    15.       Name strOriginalName As strNewName
    16.         DoCmd.TransferText acImportDelim, "MyImportSpec", "Table1", strNewName, True
    17.       Name strNewName As strOriginalName
    18.  
    19.       strFilespec = Dir
    20. Loop
    21.  
Jun 28 '17 #13

NeoPa
Expert Mod 15k+
P: 31,418
KMarks:
DoCmd.TransferText , acImportDelim, "DailyAeppayImport", "DailyAeppaysConsolidated", strPath & strFilespec
You'll notice that the first parameter is preceeded by a comma (,). This means that the value for your first parameter (TransferType) is actually being passed in the second (SpecificationName) instead. obviously, with positional parameters, this is also true of all subsequent parameters.

I always recommend using named parameters when calling procedures, but it's particularly helpful when dealing with those that have quite a few, as well as those which have optional ones. Following that approach would certainly have avoided this little problem.
Jun 28 '17 #14

NeoPa
Expert Mod 15k+
P: 31,418
KMarks:
NeoPa, sorry yes our IT department was the first place I went to. They were the ones who notified me that my previous files and databases were lost beyond repair. It sounded like it was due to us using thin clients and floating profiles? I'm not smart enough honestly to know how true that is exactly.
That's fair enough. Generally the profile is quite separate from you files, but not always if files are stored in the wrong places. They frequently are. Very few ever get advice or guidance on where to, and not to, store their data.

It may be worth another stab at asking particularly for your old profile, but like you I'd expect that to turn up nothing of value, from what you've already said.
Jun 28 '17 #15

NeoPa
Expert Mod 15k+
P: 31,418
Also, it seems ADezii has already checked out Phil's suggestion for you and confirmed his suspicion. Not only that, but in true ADezii style, has designed some code that will help you to get past this particular problem. Try it out and I suspect you'll be wanting to set his answer as the Best Answer when you've got it to work.
Jun 28 '17 #16

Post your reply

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