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.
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.
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-DivisionN ames-Table ].[Program ID]"
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionN ames-Table ]"
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-Divisi onNames-Ta ble].Files ToRun)=-1) );"
Set rst = CurrentDb.OpenRecordset(SQ L)
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
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-DivisionN
strSQL = strSQL & "FROM [BudgetHeirarchy-DivisionN
strSQL = strSQL & "WHERE ((([BudgetHeirarchy-Divisi
Set rst = CurrentDb.OpenRecordset(SQ
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...:
Then you should choose delimited text and set delimiter:
On next page press "Advanced" button and select additional parameters (date format etc.):
Then press "Save as" button and save specification with any name"myspec", for example).
Your code will be like this:
Then you should choose delimited text and set delimiter:
On next page press "Advanced" button and select additional parameters (date format etc.):
Then 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
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
DoCmd.TransferText acImportDelim, "DEx", "DivAll", file
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]
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\"
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)
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
strcriteria = rst![Program ID] & ".txt"
DoCmd.TransferText acImportFixed, "Dex1", "tblTMP", "c:\SAP Extracts\DivisionExtract\"
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;
INSERT INTO tblMain ([Program ID]) SELECT tblTmp.*, [AF-B01] AS [Program ID] FROM tblTmp;
Open in new window
Correct path and SQL according to yours