• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

Importing CSV to Access with TransferText results in "Field (F1) does not exist" error

I have several CSV files that I need to import into existing tables in an Access database on a weekly basis.  I'm running the macro from Excel, since it executes several functions (including saving a time-stamped copy of the file to an archive folder) before proceeding with the upload.

My code is as follows:

'Upload
Dim objAccess As New Access.Application
objAccess.OpenCurrentDatabase "DATABASE.mdb", False
objAccess.DoCmd.TransferText acImportDelim, , "Table_Name", "CSV_File.csv", False

Note that I've specified that the file does not include a header row.  However, when I try to execute this I receive a "Field (F1) does not exist" error.

While searching for a solution, I found this thread on EE:

http://www.experts-exchange.com/Databases/MS_Access/Q_21064984.html

However, I'm not clear on what the ultimate solution was.  Is it possible to directly import a CSV file into an existing Access table?  This thread almost sounds like it's necessary to create a temp table first.

Thanks.
0
Nicostick
Asked:
Nicostick
  • 5
  • 5
  • 4
1 Solution
 
JonoBBCommented:
I seem to recall that using the transfer text method requires your data to contain a header row else you get that error message??

Anyway, why dont you try importing into a temporary table and then running an append query from the temp table to the *live* table?
0
 
NicostickAuthor Commented:
Thanks for the response, JonoBB.

I want to avoid using temp tables, since I have several different CSV files that all need to be uploaded to separate tables with completely different fields.  This would mean creating multiple temp tables and queries, I think, which adds a lot of clutter to the process.

The TransferText method allows you to state that the data doesn't contain a header row; shouldn't it be able to accept data without headers?

Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
NicostickAuthor Commented:
Hi capricorn,

This thread looks to be discussing importing multiple Excel sheets into a single table.  I need to import multiple CSV files into separate tables.

Thanks
0
 
Rey Obrero (Capricorn1)Commented:
try this

Private Sub cmdGetExcelFile_Click()
Dim strFilename As String, strFileName1 As String, sTableName As String, strPath As String
Dim i As Integer, RList
Dim fs As Object
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\ExcelFiles"
    .fileName = "*.xls"
    If .Execute(SortBy:=msoSortbyFileName, _
    SortOrder:=msoSortOrderDescending) > 0 Then
        For i = 1 To .FoundFiles.Count

strPath = .FoundFiles(i)
strFilename = Dir(strPath)
strFileName1 = Left$([strFilename], InStr(1, [strFilename], ".") - 1)
sTableName = Mid(Replace(strFileName1, " ", ""), InStr(1, (Replace(strFileName1, " ", "")), "-") + 1)

 
   
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "" & sTableName & "", strPath, True
   Next i
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
oops,

change this

           .fileName = "*.xls"

to

             .fileName = "*.csv"
0
 
NicostickAuthor Commented:
Thanks for the code, capricorn.  However, the issue I'm facing isn't with locating the files; those will be selected either through dialogue boxes are retrieved from set locations.  The problem I have is with the actual transfer of the data into Access, which is when I get the "Field does not exist" error.  The code above uses "TransferSpreadsheet", which I don't think will work with CSV files.
0
 
Rey Obrero (Capricorn1)Commented:
oops, sorry Nicostick
I think i am half awake
0
 
JonoBBCommented:
Nicostick, I have done some testing on this, and it would seem that access cant import unless there are headers in the source txt file...even if you specify header row = none

I am not sure why this is the case, but I even tried doing it with a macro.

Once I included headers in the text file, then no problems in importing

Weird.....
0
 
NicostickAuthor Commented:
Thanks, JonoBB.  That is, indeed, weird.  I'm thinking that my best bet may just be to use ADO, then, if I'm going to have to either hard-code the header rows or create tables/queries to move the data across anyway.  I was hoping for a quick fix with the TransferText, but it sounds like it's just too limited.

I'm planning to migrate this to MSDE in the next couple of months anyway, so it might make sense to just put the extra effort in now and get the ADO framework in place.
0
 
JonoBBCommented:
0
 
JonoBBCommented:
Bit of a pain, having to create an import specification, isnt it??
0
 
JonoBBCommented:
Oh, and thanks for the points!
0
 
NicostickAuthor Commented:
Thanks for the link.  Considering that I'll need to key in the field names for each of the CSVs in the import specifications, anyway, I think I'll bite the bullet and try to sort the ADO out.  This is a solution to the question I posed, though, so I'm giving it an "A".

Expect to see some questions on ADO posted in the near future.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now