Solved

How To Import Text File From Code

Posted on 2013-01-07
6
537 Views
Last Modified: 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.
0
Comment
Question by:Buck_Beasom
[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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38752053
Are you on Access 2007 or newer? If so, using SavedImports is easier.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38752061
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", "myTableName", "C:\myCsv.csv", True
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38752069
..if you are importing a .csv file, you should be using


 DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Buck_Beasom
ID: 38752088
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.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38752127
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:
DoCmd.RunSavedImportExport "Name Of Your Saved Import"
0
 

Author Closing Comment

by:Buck_Beasom
ID: 38752174
As always from Capricorn, easy, fast and right. I had not gone through the "Advanced/Save As" step because I thought simply saving the import spec would accomplish that. Once I did that step everything was Jake.

Thanks.
0

Featured Post

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.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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