Solved

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

Posted on 2012-03-29
10
439 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Give Your Engineering Team a Productivity Boost

Learn why container technology is so powerful and how it can provide your team with productivity gains and other benefits.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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