importing multiple txt files into Access (1 Viewer)

odun · Apr 24, 2005

Hello all:

I have several (over 300) txt files (with very different file names) that I would like to import into Access simultaneously, they all have the same
fields and are in one folder.

Since they are text, the files are delimited with " and I also have a
saved spec that I use before each import.

Do you have a vba code that I could run in access to obtain these text
files?

Your urgent help is required.

Thanks,
Odun

ghudson · Apr 25, 2005

Welcome to the forum.

Your question has been asked and answered in many posts within this forum.

Please search the forum using the keywords from the title of this post and you will find what you seek.

odun · Apr 25, 2005

I searched the forum before posting my question, but did not find any post that could help with my problem.

If you can direct me to a specific post that could import delimited text files into 1 access table with a saved spec, please let me know.

All you kind people out there, if you have any ideas, please share.

Thanks again,
Odun

ghudson · Apr 25, 2005

'Courtesy of Tim K.

'This sub will import all text files @ C:\Import TXT files\ and
'move them to the C:\Archived TXT Files\ folder. This example imports
'delimited text files with the import specification named TextImportSpecs
'and the imported files do not have field names.

Code:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
    
    Dim objFS As Object, objFolder As Object
    Dim objFiles As Object, objF1 As Object
    Dim strFolderPath As String
    
    strFolderPath = "C:\Import TXT files\"
    Set objFS = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFS.GetFolder(strFolderPath)
    Set objFiles = objFolder.files
    
    For Each objF1 In objFiles
        If Right(objF1.Name, 3) = "txt" Then
            DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
            Name strFolderPath & objF1.Name As "C:\Archived TXT Files\" & objF1.Name 'Move the files to the archive folder
        End If
    Next
    
    Set objF1 = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFS = Nothing
    
bImportFiles_Click_Exit:
    Exit Sub
    
bImportFiles_Click_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume bImportFiles_Click_Exit
    
End Sub

odun · Apr 25, 2005

Thanks for the code.

I did try it, but I am gettin this error:

"3011 The microsoft Jet database engine could not find the object ..." it lists the first txt file in the folder I am trying to export.

Here is the code you suggested, I just changed the file path and the spec:

Code:

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
   
   Dim objFS As Object, objFolder As Object
   Dim objFiles As Object, objF1 As Object
   Dim strFolderPath As String
   
   strFolderPath = "E:\TDD\Odun\All Files\Sales Data\SAP Extract\SAP Extract-2004"
   Set objFS = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFS.GetFolder(strFolderPath)
   Set objFiles = objFolder.files
   
   For Each objF1 In objFiles
       If Right(objF1.Name, 3) = "txt" Then
           DoCmd.TransferText acImportDelim, "SAPShip", "tblSHIP Data-SAP-04", strFolderPath & objF1.Name, False
       End If
   Next
   
   Set objF1 = Nothing
   Set objFiles = Nothing
   Set objFolder = Nothing
   Set objFS = Nothing
   
bImportFiles_Click_Exit:
   Exit Sub
   
bImportFiles_Click_Err:
   MsgBox Err.Number & " " & Err.Description
   Resume bImportFiles_Click_Exit
End Sub

[\code]

Thanks for all your help.

Odun

ghudson · Apr 26, 2005

This line jumps out since you did not end if with a back slash...

Code:

strFolderPath = "E:\TDD\Odun\All Files\Sales Data\SAP Extract\SAP Extract-2004[B][COLOR=Blue]\[/COLOR][/B]"

Notice my code did.

odun · Apr 26, 2005

Hi:

I have included the "\" and the code started running. But all of a sudden, I got an error 3000 - "there is no message for this error".

When I looked at my database, my tables and queries have vanished!!! but the reports are still there. Do you know why it would do that.

How can I make the code work. I can always recreate the tables and queries.

Please help.

Thanks again,

ghudson · Apr 26, 2005

Not a clue. The code will fail if the tblSHIP Data-SAP-04 table is missing. Does the code compile? Have you repaired and compacted your db?

I do not know what to tell you. I did not write the code, I just found it floating on a forum one dat and I saved it for if and when I might need it.

odun · Apr 26, 2005

Hi:

When I closed the db and opened it again, the tables and queries where back!!! Don't know what is happening. I tried rerunning the code and voila, it worked!!! It created another table called tbl(with the table name I included in the code), I thought it would just import the information in my existing table (same name as in the code). But I am satisfied with the results.

Thanks a lot for your help.

Odoun

PanzerAttack · Jul 25, 2017

Hello,

I have a number of CSV files, which I want to import but I'm getting the error "3625 The Text file specification 'TextImportSpecs' does not exist. You can not import, export, or link using the specification"

I have tried to tweak the code in this chain but as I'm using CSV not TXT files, I wonder if that's the issue. Can you please advise?

Many thanks

Anthony

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "Z:\Data Quality Management\Test"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "csv" Then
DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "Z:\Data Quality Management\Test2" & objF1.Name 'Move the files to the archive folder
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Sub

PanzerAttack · Jul 25, 2017

Okay, so I maybe getting there.

I have created an import spec and named it "TextImportSpecs"
I have created a table and named it "tblImportedFiles"

Now the error message is "3011 could not find 'filename.cs' it appears to be losing the v at the end of .csv

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "Z:\Data Quality Management\Test"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "csv" Then
DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "Z:\Data Quality Management\Test2" & objF1.Name 'Move the files to the archive folder
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Sub

Minty · Jul 25, 2017

Add a Debug.print objF1 before using the Right() function, to see what you are feeding it.
You'll also need a trailing slash after Test2

Code:

As "Z:\Data Quality Management\Test2[COLOR="Red"]\[/COLOR]"

PanzerAttack · Jul 25, 2017

Okay, sorry to cause the noise, the issue was with the file name exceeding the number of characters I think, as when I renamed 2 of the csv files, it all worked perfectly

importing multiple txt files into Access (1 Viewer)

odun

Registered User.

ghudson

Registered User.

odun

Registered User.

ghudson

Registered User.

odun

Registered User.

ghudson

Registered User.

odun

Registered User.

ghudson

Registered User.

odun

Registered User.

PanzerAttack

Registered User.

PanzerAttack

Registered User.

Minty

AWF VIP

PanzerAttack

Registered User.

Similar threads

Users who are viewing this thread