MS Access Query to Run a macro to import text files from a folder

I have already created a query to summarize the results. However I want to use the results and import text files based on the results.

For example

I have
Apples
Honey
Milk
Eggs
Fish

I run a query which brings me only Apples, Milk and Honey and now I need to take these results and import text files based on them in a specified folder.

i.e. Apples.txt, Milk.txt and Honey.txt


I know it requires a while loop such that Look through each members of the field until the end and for each field take the name and import the text file into a table.

Unfortunately I'm not well versed in sql to know the syntax.
FAH_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can use this code:
Dim rst As Recordset
Dim path As String, file As String, SQL As String
path = "c:\tmp\"
SQL = "Select * From Table1"
Set rst = CurrentDb.OpenRecordset(SQL)
Do While Not rst.EOF
    file = path & rst!Product & ".txt"        'use name of field with file name
    If Dir(file) <> "" Then
        ' DoCmd.TransferText acImportDelim, , YourTableName, file 'Correct syntax due to structure of your text file
        MsgBox "File: " & file & " imported", vbOKOnly
    Else
       MsgBox "File: " & file & " not found", vbOKOnly
    End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Open in new window

Correct path and SQL according to yours
0
FAH_Author Commented:
I want to add couple of things

First will it append the .txt files into the table or override it.
Secondly I want to display the textfile ID which is the same as Program ID in the new table with the respective results from the text file.

How do I put delimited as "|"



Sub ExtractFiles()

Dim rst As Recordset
Dim path As String, file As String, strSQL As String
path = "c:\SAP Extracts\Divisions\"
strSQL = "SELECT [BudgetHeirarchy-DivisionNames-Table].[Program ID]"
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionNames-Table]"
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-DivisionNames-Table].FilesToRun)=-1));"
Set rst = CurrentDb.OpenRecordset(SQL)
Do While Not rst.EOF
    file = path & rst!Program_ID & ".txt"        'use name of field with file name
    If Dir(file) <> "" Then
        ' DoCmd.TransferText acImportDelim, , YourTableName, file 'Correct syntax due to structure of your text file
        DoCmd.TransferText acImportDelim, , DivAll, file
       
        MsgBox "File: " & file & " imported", vbOKOnly
    Else
       MsgBox "File: " & file & " not found", vbOKOnly
    End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
0
als315Commented:
At first you should create import specification with import master. In first page select Append...:
importThen you should choose delimited text and set delimiter:
delimiterOn next page press "Advanced" button and select additional parameters (date format etc.):
advancedThen press "Save as" button and save specification with any name"myspec", for example).
Your code will be like this:
Sub ExtractFiles()

Dim rst As Recordset
Dim path As String, file As String, strSQL As String
path = "c:\SAP Extracts\Divisions\"
strSQL = "SELECT [BudgetHeirarchy-DivisionNames-Table].[Program ID]"
strSQL = strSQL & " FROM [BudgetHeirarchy-DivisionNames-Table]"
strSQL = strSQL & " WHERE ((([BudgetHeirarchy-DivisionNames-Table].FilesToRun)=-1));"
Set rst = CurrentDb.OpenRecordset(SQL)
Do While Not rst.EOF
    file = path & rst![Program ID] & ".txt"        'use name of field with file name
    If Dir(file) <> "" Then
         DoCmd.TransferText acImportDelim,"myspec" , "DivAll", file
        
        MsgBox "ID: " & rst![Program ID] & " File: " & file & " imported", vbOKOnly
    Else
       MsgBox "ID: " & rst![Program ID] & " File: " & file & " not found", vbOKOnly
    End If
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FAH_Author Commented:
I'm getting error 3625 saying that It cannot find Import specification "DEx"  I have attached a screenshot of the import specs and Its listed there

DoCmd.TransferText acImportDelim, "DEx", "DivAll", file
0
FAH_Author Commented:
I have gotten it to work however how do I enter the field name to go into the field_ID field before the CCGROUP

Attached are screen shots

I'm getting the error here :DivExtracts.FileID = rst.[Program ID]


Do Until rst.EOF = True
strcriteria = rst.[Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "DEx", "DivExtracts", "c:\SAP Extracts\DivisionExtract\" & strcriteria, True
DivExtracts.FileID = rst.[Program ID]
0
als315Commented:
You should use ! sign for fields (look at my example):
 rst![Program ID]
What is DivExtracts.FileID? If it is imported table, you should open it at first. If you like to add to all records column with Program ID, it is better to import to temporary table and then add records with query to final table (in this case you can add necessary fields, check and correct other fields)
0
FAH_Author Commented:
What I need to do is whenever its importing the txt file also have its ID inserted to the FileID column...
0
als315Commented:
In this case you can:
1. Import text file to temporary table
2. With add query add records from imported file to main table (in query you can add Program ID)
Code could be:
Do Until rst.EOF = True
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "DEx", "tblTMP", "c:\SAP Extracts\DivisionExtract\" & strcriteria, True
Sql = "INSERT INTO tblMain ( [Program ID] ) SELECT tblTmp.*, " &  rst![Program ID] & " AS [Program ID] FROM tblTmp"
CurrentDb.Execute Sql, dbFailOnError

Open in new window

Change tblMain to your main table name (check also field names
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FAH_Author Commented:
Do Until rst.EOF = True
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\" & strcriteria, True
SQL = "INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, " & rst.[Program ID] & " AS [Program ID] FROM tblTmp;"
Debug.Print (SQL)



I'm getting an error 3061. Expected 2


On the Debug Output its coming as

INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, AF-B01 AS [Program ID] FROM tblTmp;


Please help urgently
0
als315Commented:
Try to set brackets:
INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, [AF-B01] AS [Program ID] FROM tblTmp;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.