Link to home
Start Free TrialLog in
Avatar of Buck Beasom
Buck BeasomFlag for United States of America

asked on

How To Import Text File From Code

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.
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Are you on Access 2007 or newer? If so, using SavedImports is easier.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America 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
..if you are importing a .csv file, you should be using


 DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True
Avatar of Buck Beasom

ASKER

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