CODEWITHSUNDEEP
ms-accessvbatext-filesms-access-2013
Joel
Joel

Reputation: 53

Using MS Access to import multiple text files

I have about 600 text files with headers, and I don't really feel like importing them one by one manually into MS Access.

I don't want to append the text files into one Access table. If I have 600 text files, I want the result to be 600 Access tables.

I've searched high and low for this, the closest I've come is some code that would take my text files and append them into one access table. I don't want that.

Upvotes: 1

Views: 13410

Answers (3)

ASH
ASH

Reputation: 20362

This is how I would do it.

Option Compare Database

Private Sub Command0_Click()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        blnHasFieldNames = True

        strPath = "C:\Users\ryans\OneDrive\Desktop\test\"

        strTable = "Table1"

        strFile = Dir(strPath & "*.txt")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile
              DoCmd.TransferText acImportDelim, _
                TableName:="Test1", _
                FileName:=strPath & strFile, _
                HasFieldNames:=True
              strFile = Dir()
        Loop


End Sub

Upvotes: 0

Parfait
Parfait

Reputation: 107767

Consider a For/Loop VBA using the DoCmd.TransferText command that iterates through all text files in a folder directory and imports them with suffix to denote 1-600.

Dim FSO as Object, objFolder As Object, objFile as Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = FSO.GetFolder("C:\Path\To\TextFiles")

i = 1
For each objFile in objFolder.Files
  If Right(objFile.Name, 3) = "txt" Then
    DoCmd.TransferText acImportDelim, , "File_" & i, objFolder & "\" & objFile.Name, True
  End if
  i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set FSO = Nothing

In the empty argument in TransferText you can use a pre-defined specification object which you create during one manual import of the text file. This allows you to name fields, define data types, etc. of the imported text file.

Finally, if all files are structured the same, consider again importing into one table and use queries by relevant fields to break up in the 600 groupings if needed. To use one table, simply replace the "File_" & i argument above with a table string name: "dataFiles". You save on table namespaces, storage from less database objects, and overall helps in better organization as well as the relational model.

Upvotes: 7

John
John

Reputation: 1004

I am under the impression that you need to break the process in three steps

1st code that imports text to a table http://www.datawright.com.au/access_resources/access_import_text_files.htm

Every text gets imported to a temp tables... Then you examine the table fields

2nd code that creates tables http://www.access-programmers.co.uk/forums/showthread.php?t=213261

3rd erase contents of temp table and proceed with the next text...or when you import them you could add info in the temp table...like text name and process them afterwards in a batch

Upvotes: 0

Related Questions