Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-29
10
Medium Priority
?
444 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_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 40

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 40

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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
 
LVL 40

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 40

Accepted Solution

by:
als315 earned 1050 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 40

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

722 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