Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-11-2018, 10:37 AM   #1
lwarren1968
Newly Registered User
 
Join Date: Jan 2013
Posts: 32
Thanks: 8
Thanked 0 Times in 0 Posts
lwarren1968 is on a distinguished road
import multiple text files into 1 access table

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

lwarren1968 is offline   Reply With Quote
Old 12-11-2018, 10:53 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,286
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: import multiple text files into 1 access table

At a glance, I'd expect strTable to contain the name of the target table, not a path.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 12-11-2018, 11:08 AM   #3
lwarren1968
Newly Registered User
 
Join Date: Jan 2013
Posts: 32
Thanks: 8
Thanked 0 Times in 0 Posts
lwarren1968 is on a distinguished road
Re: import multiple text files into 1 access table

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?

lwarren1968 is offline   Reply With Quote
Old 12-11-2018, 11:13 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,286
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: import multiple text files into 1 access table

I'd expect the specification and import type to control that. Is that your specification?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 12-11-2018, 11:19 AM   #5
lwarren1968
Newly Registered User
 
Join Date: Jan 2013
Posts: 32
Thanks: 8
Thanked 0 Times in 0 Posts
lwarren1968 is on a distinguished road
Re: import multiple text files into 1 access table

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.
lwarren1968 is offline   Reply With Quote
Old 12-11-2018, 11:19 AM   #6
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,363
Thanks: 434
Thanked 800 Times in 775 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: import multiple text files into 1 access table

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") ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 12-12-2018, 09:50 AM   #7
lwarren1968
Newly Registered User
 
Join Date: Jan 2013
Posts: 32
Thanks: 8
Thanked 0 Times in 0 Posts
lwarren1968 is on a distinguished road
Re: import multiple text files into 1 access table

I still need help with my question?

lwarren1968 is offline   Reply With Quote
Old 12-12-2018, 09:55 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,286
Thanks: 13
Thanked 4,111 Times in 4,043 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: import multiple text files into 1 access table

You never answered whether you created that specification. Can you attach the db and a couple of the text files?

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Reply

Tags
.txt , import , queries , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to import multiple excel files with different number of sheets into access table mirtomi Modules & VBA 3 07-13-2015 02:39 AM
Import Multiple Text Files to Access Table Using VBA Method buggsbunny4 Modules & VBA 0 09-30-2012 10:15 AM
Access 2003: Import Multiple Text Files with different filenames using a Macro Aimminn Macros 1 06-23-2010 12:20 PM
Import multiple excel files into single access table mandeepdhami4u Tables 27 05-10-2010 05:21 AM
Import multiple csv files into an Access table gsrai31 Modules & VBA 1 10-12-2007 01:00 PM




All times are GMT -8. The time now is 09:13 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World