Solved

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

Posted on 2004-10-05
14
516 Views
Last Modified: 2012-06-21
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
Comment
Question by:Nicostick
  • 5
  • 5
  • 4
14 Comments
 
LVL 8

Expert Comment

by:JonoBB
ID: 12226574
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
 

Author Comment

by:Nicostick
ID: 12226623
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12226763
0
 

Author Comment

by:Nicostick
ID: 12226916
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12226969
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12226977
oops,

change this

           .fileName = "*.xls"

to

             .fileName = "*.csv"
0
 

Author Comment

by:Nicostick
ID: 12227063
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 12227179
oops, sorry Nicostick
I think i am half awake
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12227308
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
 

Author Comment

by:Nicostick
ID: 12227403
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
 
LVL 8

Accepted Solution

by:
JonoBB earned 250 total points
ID: 12227409
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12227502
Bit of a pain, having to create an import specification, isnt it??
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12227505
Oh, and thanks for the points!
0
 

Author Comment

by:Nicostick
ID: 12227520
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

709 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now