Link to home
Start Free TrialLog in
Avatar of FAH_
FAH_

asked on

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.
Avatar of als315
als315
Flag of Russian Federation image

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
Avatar of FAH_
FAH_

ASKER

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
At first you should create import specification with import master. In first page select Append...:
User generated imageThen you should choose delimited text and set delimiter:
User generated imageOn next page press "Advanced" button and select additional parameters (date format etc.):
User generated imageThen 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

Avatar of FAH_

ASKER

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
Avatar of FAH_

ASKER

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]
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)
Avatar of FAH_

ASKER

What I need to do is whenever its importing the txt file also have its ID inserted to the FileID column...
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FAH_

ASKER

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
Try to set brackets:
INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, [AF-B01] AS [Program ID] FROM tblTmp;