Import multiple txt files into one table (1 Viewer)

We receive sales data files daily for one of our stores. We are trying to upload these to central database to analyse the sales. I can import individual files using the import function and have played with the textimport function but all the files have individual names and there are over 700 of them.

Does anyone know of a way I can automate loading these up? I want to be able to do the bulk history in one go and then upload 7 days at a time in the future.

Thanks

The script below imports multiple .txt files that have been put in an imports folder placed in the directory of the database. The data is imported into a temporary table and after that you can do whatever you need with the data. After import the files are moved to an archived folder.

The import specifications of the txt files need to be setup and named Import Specs.

Option Compare Database
Option Explicit

'------------------------------------------------------------------------------
' Procedure: subImport
' Author: Dave Swanton
' Purpose: Read in new data file from an txt file
' Dependencies: None
' Parameters: None
'-------------------------------------------------------------------------------



Public Sub subImport()
On Error GoTo Err_subImport

Dim stDocName As String
Dim fs As FileSearch
Dim ifn As String
Dim sql As String
Dim today As String
Dim fso As Scripting.FileSystemObject
Dim oktogo As Boolean
Dim specname As String
Dim repdate As String
Dim myfile As Scripting.TextStream
Dim i As Long
Dim y As Integer
Dim ShortFn As String
Dim specname As String

specname = "Import Specs"
DoCmd.SetWarnings False
sql = "DELETE FROM tbl_temp_Import"

DoCmd.RunSQL sql 'Empty Temp Table



DoCmd.SetWarnings False
oktogo = False
ifn = CurrentProject.Path & "\Imports\"
Set fs = Application.FileSearch
With fs
.LookIn = ifn

.Filename = "*.txt"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then

For i = 1 To .FoundFiles.Count

ShortFn = Right(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
DoCmd.TransferText acImportDelim, specname, "tbl_temp_Import", .FoundFiles(i), True
subArchive .FoundFiles(i)
y = y + 1


Next i
Else
MsgBox "Please ensure that the source file is present and try again" & vbCr _
& "Required file location: " & vbCr & ifn, vbExclamation + vbOKOnly, "Input File Missing"


Exit Sub
End If
End With




MsgBox "Import complete. " & y & " files Imported", vbOKOnly + vbInformation, "Import Complete"

Exit_subImport:
' Turn warning messages back on
DoCmd.SetWarnings True

Exit Sub

Err_subImport:
MsgBox Err.Description
Resume Exit_subImport

End Sub


'------------------------------------------------------------------------------
' Procedure: subArchive
' Author: Dave Swanton
' Purpose: Archive Import files into Archived Imports Folder
' Dependencies: None
' Parameters: None
'-------------------------------------------------------------------------------


Public Sub subArchive(src As String)

On Error GoTo Err_subArchive
Dim dest As String
Dim fso As Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

dest = Left(src, InStrRev(src, "\")) & "\Archived repancy Files\" & Right(src, Len(src) - InStrRev(src, "\"))
If fso.FileExists(dest) Then
fso.DeleteFile dest
Name src As dest
Else
Name src As dest
End If
Exit_subArchive:
Exit Sub

Err_subArchive:
MsgBox Err.Description
Resume Exit_subArchive

End Sub

Hi there,

I am trying to use "Daveswanton77" code as shown above, but get the following error as the following lines

"User-defined type not defined"

Dim fs As FileSearch
Dim fso As Scripting.FileSystemObject
Dim myfile As Scripting.TextStream

Any ideas appreciated

Thanks
Geoff

Ensure that references to as per attachment are selected

So close

I was hoping to use this as well, I have a similar problem. 20 - 30 .txt files to import to a specific table each day. But I am using Access 2000 and Office 2000. I don't have the references available as per the attached jpg.

Any way that I can still do this?

Debug on the above code is throwing up;

Dim fs as FileSearch

Compile error
User-defined type not defined

KenBurton said:

I was hoping to use this as well, I have a similar problem. 20 - 30 .txt files to import to a specific table each day. But I am using Access 2000 and Office 2000. I don't have the references available as per the attached jpg.

Any way that I can still do this?

Debug on the above code is throwing up;

Dim fs as FileSearch

Compile error
User-defined type not defined

Click to expand...

Hi,

I have a different method which works a treat, and doesn't need as much code. I currently import on a dialy basis around 300 text files. I have created import specifications where required, but essentially all of my text files conform to the exact same layout and therefore I do not require any more than 3 import specifications.

So if all your text files conform to the same layout this will be easy for you.

The first part is time consuming, but once done and assuming there will not be any changes to text file formats, it's there for good.

using one of your text files to manually import the data and create the import specification your code might look something like this:

DoCmd.TransferText acImportFixed, "Import Spec 63 Rev 2", "tblNewImportSpec63Rev_2", "Z:\aga.fof\aga.txt", False, ""

Docmd.TransferText acimportFixed identifies that its a text file and that it is in Fixed Width format [this also works for CSV format]

"Import Spec 63 Rev 2" is the name I have given to my import specification.

"tblNewImportSpec63Rev_2" is the name of the table in which I want to import the data to.

"Z:\aga.fof\aga.txt", False," is the location of where the text file can be found, which in this case is my Z Drive and Folder Named : aga.fof and the name of the text file: aga.txt

This process assumes that your folder and text files will always have the same name.

I hope this is of some assistance.

John