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.

Buck_Beasom

Asked: 2013-01-07

Advertise Here

1 Solution

LVL 29

IrogSintaCommented: 2013-01-07

Are you on Access 2007 or newer? If so, using SavedImports is easier.

LVL 120

Rey Obrero (Capricorn1)Commented: 2013-01-07

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

LVL 120

Rey Obrero (Capricorn1)Commented: 2013-01-07

..if you are importing a .csv file, you should be using

DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True

Buck_BeasomDatabase DesignerAuthor Commented: 2013-01-07

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.

LVL 29

IrogSintaCommented: 2013-01-07

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"

Buck_BeasomDatabase DesignerAuthor Commented: 2013-01-07

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

How To Import Text File From Code

Join & Write a Comment Already a member? Login.

Featured Post