import multiple text files into 1 access table (1 Viewer)

lwarren1968

Registered User
Joined
Jan 18, 2013
Messages
36
At first I wanted to import several .txt files into access and then I was going to perform a Union Query to combined. All worked great accept for the fields are importing into one. With that being said, I've changed my mind!

I'd like to import several .txt files into access and have them load into one table. Can anyone help me fix what is missing below:

Sub ImportTextFiles()
Dim strPath As String
Dim strFile As String
Dim strTable As String
Dim strSpecification As String
Dim intImportType As AcTextTransferType
Dim blnHasFieldNames As Boolean

' Modify these values as needed
strTable = "G:\TRUE VALUE"
strSpecification = "True Value_Full_tbl"
blnHasFieldNames = False
intImportType = acImportDelim

' Let user select a folder
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "You didn't select a folder", vbExclamation
Exit Sub
End If
End With
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

' Loop through the text files
strFile = Dir(G:\TRUE VALUE"*.txt")
Do While strFile <> ""
' Import text file
DoCmd.TransferText _
TransferType:=intImportType, _
SpecificationName:=strSpecification, _
TableName:=strTable, _
FileName:=strPath & strFile, _
HasFieldNames:=blnHasFieldNames
strFile = Dir
Loop
End Sub
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,623
At a glance, I'd expect strTable to contain the name of the target table, not a path.
 

lwarren1968

Registered User
Joined
Jan 18, 2013
Messages
36
I changed, but I'm still getting the files importing separately, in addition data is uploading into one Field. Thee is something with .txt files that are diff. from excel files?
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,623
I'd expect the specification and import type to control that. Is that your specification?
 

lwarren1968

Registered User
Joined
Jan 18, 2013
Messages
36
I have never dabbled in VBA until yesterday. I can't even say I understand it honestly.
I'm self taught on this end using Access very little, but have been tasked with trying find a way to pull of of this together into one table. So, any help on your end would be appreciated.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,015
Shouldn't this strFile = Dir(G:\TRUE VALUE"*.txt") be

strFile = Dir("G:\TRUE VALUE\*.txt")

and in fact really be strFile = Dir(strPath & "\*.txt") ?
 

pbaldy

Wino Moderator
Staff member
Joined
Aug 30, 2003
Messages
33,623
You never answered whether you created that specification. Can you attach the db and a couple of the text files?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom