Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
COURSE of the MONTH
12 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How To Import Text File From Code
Posted on 2013-01-07
I am using this code to attempt an import of a fixed width .csv text file:
Private Sub cbImport_Click()
Dim strFileName As String
Dim strSQL As String
Dim strImportSpec As String
If IsNull(Me.txtFile) = True Then
MsgBox "You must find a file for import!", vbExclamation, "File Required"
Exit Sub
End If
strSQL = "DELETE GL_ImportTable.* FROM GL_ImportTable"
DoCmd.RunSQL strSQL
strFileName = Trim(Me.txtFile)
strImportSpec = "Import_GL"
DoCmd.TransferText acImportFixed, "Import_GL", "GL_ImportTable", strFileName, True
End Sub
I have used it with both a variable and the string shown for the Specification name and in each case I get a message that says:
"The text file specification "Import_GL" does not exist."
Obviously it does, and I have even copied the name right out of the specification list into the code.
Any help will be appreciated.
Thanks.
Private Sub cbImport_Click()
Dim strFileName As String
Dim strSQL As String
Dim strImportSpec As String
If IsNull(Me.txtFile) = True Then
MsgBox "You must find a file for import!", vbExclamation, "File Required"
Exit Sub
End If
strSQL = "DELETE GL_ImportTable.* FROM GL_ImportTable"
DoCmd.RunSQL strSQL
strFileName = Trim(Me.txtFile)
strImportSpec = "Import_GL"
DoCmd.TransferText acImportFixed, "Import_GL", "GL_ImportTable", strFileName, True
End Sub
I have used it with both a variable and the string shown for the Specification name and in each case I get a message that says:
"The text file specification "Import_GL" does not exist."
Obviously it does, and I have even copied the name right out of the specification list into the code.
Any help will be appreciated.
Thanks.
[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
2
2-
2
6 Comments
Active 1 day ago
have you created the import specification?
what version of access are you using?
'to create an import specification
'
1. File>get external data>import
2. select in the File of types box Text files (*.txt etc..
3. select the file
4. in the import text wizard window select delimited/fixed
5. Click advanced
6. in the import specification window
type the name of the field in the Field Name column
(here you can use the field names of the destination table, specify data type,
check the box Skip if you do not want to import the column)
7 click save as, give the specification a name <-- this is the specification name that you will use in the command line below
Now you can use the code below to import the CSV file via code
DoCmd.TransferText acImportFixed, "ImportSpecificationName",
what version of access are you using?
'to create an import specification
'
1. File>get external data>import
2. select in the File of types box Text files (*.txt etc..
3. select the file
4. in the import text wizard window select delimited/fixed
5. Click advanced
6. in the import specification window
type the name of the field in the Field Name column
(here you can use the field names of the destination table, specify data type,
check the box Skip if you do not want to import the column)
7 click save as, give the specification a name <-- this is the specification name that you will use in the command line below
Now you can use the code below to import the CSV file via code
DoCmd.TransferText acImportFixed, "ImportSpecificationName",
Active 1 day ago
..if you are importing a .csv file, you should be using
DoCmd.TransferText acImportDelim, "ImportSpecificationName",
DoCmd.TransferText acImportDelim, "ImportSpecificationName",
Using Access 2007 (and some folks using 2010.) I just want to be able to park the import behind a button so users can get the data without having to go through the process themselves.
Going to try Capricorn's suggestions, as the raw file has no column headings.
Thanks.
Going to try Capricorn's suggestions, as the raw file has no column headings.
Thanks.
Active 7 days ago
You can create a Saved Import (if you have Access 2007/2010). Just click Text File in the Import section of the External Data tab of the ribbon. When you complete the import wizard it will ask you if you want to save the import steps. This Saved Importt will now be listed as a task you could run whenever you click Saved Imports in the Import section of the External Data tab.
You can call this Saved Import from code using this line:
You can call this Saved Import from code using this line:
DoCmd.RunSavedImportExport
Featured Post
Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses
Course of the Month12 days, 16 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.