DJRave


Hi,

For a project I need to import multiple textfiles into Access. Between 40 and 50 files everytime. Using the wizard wouldn't be a great sollution in this case. I've tried to use the transfertextmethod but the only problem is that I need to specify the exact location of all files in VBA but since the number of files varies every month this creates an error when a file isn't present that month.

DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\01.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\02.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\03.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\04.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\05.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\06.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\07.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\08.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\09.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\10.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\11.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\12.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\13.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\14.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\15.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\16.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\17.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\18.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\19.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\20.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\21.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\22.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\23.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\24.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\25.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\26.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\27.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\28.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\29.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\30.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\31.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\32.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\33.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\34.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\35.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\36.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\37.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\38.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\39.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\40.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\41.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\42.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\43.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\44.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\45.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\46.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\47.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\48.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\49.txt"
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", "C:\Test\50.txt"

when I only have 45 files it creates an error as soon as the script tries to import file number 46

I want to create a script to import all files from a specified folder regardless of the number of files present. This way it doesn't matter whetter there are 40 or 50 files.

Does anybody have an idea how I can solve this problem I've tried it myself but I can't come up with one myself.




Re: Importing multiple textfiles into Access

ADG


Hi

The Scripting Filesystem object can help you out here by checking the file exists. See below

Dim fso
Dim x As Long
Dim filespec As String

Set fso = CreateObject("Scripting.FileSystemObject")
For x = 1 To 50
If x < 10 Then
filespec = "c:\data\0" & Trim$(Str$(x)) & ".txt"
Else
filespec = "c:\test\" & Trim$(Str$(x)) & ".txt"
End If
If (fso.FileExists(filespec)) Then
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", filespec
End If
Next






Re: Importing multiple textfiles into Access

DJRave

It took me some time to figure out but it works great.

Thankx a lot






Re: Importing multiple textfiles into Access

SimplyM

Question - Can this load into a specific table Thanks



Re: Importing multiple textfiles into Access

DJRave

Yes this is possible. The green line specifies the way the data is imported.

"Import" is the name of the importspecification Access has to use while importing.

"ImportARGAS" is the name of the table in which all data is imported. Just change it and the table in which the data is imported changes.

"Filespec" is the full path of the file you want to import.

Dim fso
Dim x As Long
Dim filespec As String

Set fso = CreateObject("Scripting.FileSystemObject")
For x = 1 To 50
If x < 10 Then
filespec = "c:\data\0" & Trim$(Str$(x)) & ".txt"
Else
filespec = "c:\test\" & Trim$(Str$(x)) & ".txt"
End If
If (fso.FileExists(filespec)) Then
DoCmd.TransferText acImportDelim, "Import", "ImportARGas", filespec
End If
Next

Good luck!