Multiple Text Files Into One Table

[raldrich]

I am running a design of experiments where I have 109 fields that represent 3 fields of actual data, 3 fields of file information, and 103 fields that represent the variables which I vary based on the design of experiment I am running. I need to keep track of all 109 fields. My simulations can take hours/days, and so I export my data (into text files) as needed from my simulation software. I have been importing my text files using Get External Data, and selecting text file, and appending all my files into one. However, I am at a point where certain runs of my simulation might be invalid, and I need a way to take out certain files after I have imported them. I have attempted to import the text files into a tables, and to create a create table query and an append query, however, since this is a design of experiments I have 103 fields where I have duplicate cell values within a field. I need to be able to keep track of the variables so that I can create a pivot chart for analyzing the data. I currently have 15 text files (ranging from 5000 records, to 100,000+ records) that need to be turned into one table, but I will need to be able to add and remove text files/tables as needed. I am very new to access, and tutorials are not helping me with what I need to do. I can't keep going through the import text file on every file I have, every time I need to remove a data set. Plus I am concerned on missing files or appending duplicate files to a table as the number of text files grow. I need to combine multiple tables into one where the field names are all the same, and most of the field values are the same. Because of the nature of my simulations, I run the same scenario (so all 103 variable fields are the exact same value up to 2000 times), and my 3 data fields are what I am concerned with (which 2 of the data columns are a true/false value; 1 or 0). What i will be doing is averaging these 2000 values to get a probability of an occurrence given a certain design of experiments. I have a feeling SQL might be what I need, but I have absolutely zero experience. I am this close to creating a vba code, but there has to be a way to do what I want to do without using a macro code. Any help, suggestions, or tips are welcome!!!!

[June7]

If you want to be able to 'take out certain files' then need a field in table to identify this group of records. Can use the import wizard to import a text file then run an UPDATE sql to populate the group identifier field.

[ranman256]

Here's some code that will scan each file in a folder then attached it to the db, then run an import query...
You will need to attach the 1st file, build a query to import, and the spec file.
Then the code will attach each file and import it into the same table ,until all files have imported.
Modify as you like.

Public Sub ImportAllFilesInFolder()
Dim sTbl As String, sSql As String, sPath As String, sFile As String
Dim FSO, oFolder, oFile, oRX
Dim vImpQry
Const kTBL2IMP = "tTbl2Imp"     'name of the attached file
On Error GoTo ErrImp
    
sPath = "C:\foldername"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(sPath)
     'empty target table
'DoCmd.OpenQuery "qdMTdata
For Each oFile In oFolder.Files
      'MsgBox oFile.Path
      CurrentDb.TableDefs.Delete kTBL2IMP
      
            'link next file
      DoCmd.TransferText acExportDelim, "SPEC NAME", kTBL2IMP, oFile.Path, True
      
            'append
      DoCmd.OpenQuery "qaImportData"
Next
      
      'clear memory
Set FSO = Nothing
Set oFolder = Nothing
Exit Sub
ErrImp:
MsgBox Err.Description, , Err
End Sub