Field 'F1' doesn't exist in destination table (1 Viewer)

Insane_ai · Jul 26, 2019

I am working to automate an import. I have defined the import specification and it works if I run it manually but it fails when called by VBA.

Code:

Public Function SelectFile(Optional ByVal sFilter As String) As String
'Microsoft Office Object Library - Reference
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      .AllowMultiSelect = False
      .Title = "Please select a folder"
      .Filters.Clear
      If .Show = True Then
         SelectFile = .SelectedItems(1)
      End If
   End With
   Set fDialog = Nothing
End Function
 
Public Sub Import_Python()

Dim strFile As String
    strFile = SelectFile()
[COLOR=red]DoCmd.TransferText acImportDelim, Python_Import_Spec, "Import", strFile, False[/COLOR]
 
End Sub

The line that produces the error is in red.

Run-time error 2391
Field 'F1' doesn't exist in destination table 'Import'

pbaldy · Jul 26, 2019

Specification is a string argument; it needs to be in quotes.

Insane_ai · Jul 26, 2019

pbaldy said:
Specification is a string argument; it needs to be in quotes.

Adding quotes around the specification causes Run-time error '3001' Invalid argument.

pbaldy · Jul 26, 2019

This works in an automated process:

Code:

DoCmd.TransferText acImportDelim, "Loomis", "tblLoomisSafeData", "C:\AccessAp\LoomisTransactions.txt", True

where "Loomis" is the import spec. Your file doesn't have field names?

Insane_ai · Jul 26, 2019

No, the file I'm working with does not have field names. It's a raw CSV dump that I have to manipulate.

arnelgp · Jul 26, 2019

open the csv file in excel and go to the last column.
there must be data in there, so your table column count does not matched with that of the csv file.
in other word, an extra column is present in the csv.

Insane_ai · Jul 26, 2019

arnelgp said:
open the csv file in excel and go to the last column.
there must be data in there, so your table column count does not matched with that of the csv file.
in other word, an extra column is present in the csv.

That was one of the first things I tried. I don't understand why this works manually but runs into problems when automating it.

Pat Hartman · Jul 27, 2019

Are you using the same import spec when you run it manually?

Insane_ai · Jul 29, 2019

Pat Hartman said:
Are you using the same import spec when you run it manually?

Yes, even going through the advanced feature to choose the import spec. during the manual import. On a side note, it doesn't seem to matter whether I choose the spec or not during a manual import.

The only time I'm having difficulty is when I try to automate the import with VBA. I may just be stuck with manual mode on this one.

pbaldy · Jul 29, 2019

If you can attach the db and an example of the csv file here someone may be able to figure it out.

Pat Hartman · Jul 30, 2019

That method is typically raised when the number of columns in the source file doesn't match what is defined in the import spec but you should get the error regardless of how you run the import.

Insane_ai · Jul 31, 2019

Thanks to everyone who contributed and I apologize for not responding faster. I kept getting HTTP 500 errors yesterday when I tried to log in to the site.

The solution was a one liner to invoke the saved import specification:

Code:

DoCmd.RunSavedImportExport "[COLOR=black]Python_Import_Spec"[/COLOR]

pbaldy · Jul 31, 2019

Glad you got it sorted. That's a method I've never used.

Pat Hartman · Jul 31, 2019

I NEVER use the "new" method. It does not allow you to change the name or the destination of the export. It provides no advantage over the old method but does have disadvantages. Same for "import". You can't change the name or the folder where the import file is located.

Field 'F1' doesn't exist in destination table (1 Viewer)

Insane_ai

Not Really an A.I.

pbaldy

Wino Moderator

Insane_ai

Not Really an A.I.

pbaldy

Wino Moderator

Insane_ai

Not Really an A.I.

arnelgp

..forever waiting... waiting for jellybean!

Insane_ai

Not Really an A.I.

Pat Hartman

Super Moderator

Insane_ai

Not Really an A.I.

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

Insane_ai

Not Really an A.I.

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread