Solved

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

Posted on 2012-03-29
10
423 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:FAH_
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:als315
ID: 37785779
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
 

Author Comment

by:FAH_
ID: 37789288
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
 
LVL 39

Expert Comment

by:als315
ID: 37790076
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
 

Author Comment

by:FAH_
ID: 37801867
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
 

Author Comment

by:FAH_
ID: 37802808
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:als315
ID: 37804619
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
 

Author Comment

by:FAH_
ID: 37805997
What I need to do is whenever its importing the txt file also have its ID inserted to the FileID column...
0
 
LVL 39

Accepted Solution

by:
als315 earned 350 total points
ID: 37806269
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
 

Author Comment

by:FAH_
ID: 37830196
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
 
LVL 39

Expert Comment

by:als315
ID: 37831102
Try to set brackets:
INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, [AF-B01] AS [Program ID] FROM tblTmp;
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now