Import Multiple Text files into a single table

[vvasude2]

Hi all,

How can i import text files with same specefication into one table

I should be able to select multiple text files and import them automatically in the table.

The import specefication for all of them is the same.

Thanks for your support

BR,
vvasude2

[Mihail]

Create two tables with the structure you need.
Use first table for import a text file at a time and the second one to keep all imported data

Something like this:

Do
    Remove information from the first table (Delete query)
    If the last text file has been imported Then
Exit Do
    End If
    Import (next) text file in first table
    Append first table to the second one (Append query)
Loop

[vvasude2]

I have about 160 files that i need to merge and it would be not feasible to import them one at a time.

Do you happen to know the code, where i can choose an entire folder and the contents of the file gets merged based on the import spec i define

[Mihail]

This code find all files with a certain extension in one of my PC folder:
C:\Documents and Settings\Camy\My Documents\Downloads

Hope this is what you are looking for.

Option Explicit
 
Public Sub Test() 'How to apply
Dim FolderPath As String
    FolderPath = "C:\Documents and Settings\Camy\My Documents\Downloads"
 
Dim FileExtension As String
    FileExtension = ".*" 'All files in folder
'    FileExtension = ".txt" 'All text files in folder
'    FileExtension = ".zip" 'All zip files in folder
 
Dim FilePath As String
    FilePath = FilesInDirectory(FolderPath, FileExtension)
    Do While FilePath <> ""
        FilePath = FilesInDirectory
        Debug.Print FilePath
    Loop
End Sub
 
Public Function FilesInDirectory(Optional Folder As String, Optional FileExtension As String) As String
Static FolderPath As String
Dim FullPath As String
    FullPath = Folder & "\*" & FileExtension
Dim NameOfFile As String
    If Folder <> "" Then 'First apply
        FolderPath = Folder
        NameOfFile = Dir$(FullPath)
        If NameOfFile = "" Then
            MsgBox ("No file mutched criteria in this folder")
            FilesInDirectory = ""
Exit Function
        End If
    Else
        NameOfFile = Dir$
        If NameOfFile = "" Then 'No more files
            FilesInDirectory = ""
Exit Function
        End If
    End If
 
    FilesInDirectory = FolderPath & "\" & NameOfFile
 
End Function

[ChrisPadgham]

When you say you want to merge them, what are the rules for merging. It may be simpler to load each file into a separate table and do the merge using SQL

[NeoPa]

vvasude2:
Do you happen to know the code, where i can choose an entire folder and the contents of the file gets merged based on the import spec i define

No. There is no such command. The pseudo-code provided in post #2 is the closest you'll get to how it can work (and the logic therein is fundamental and solid). What are you saying is wrong with that? Are you expecting this to be done for you?