Solved

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

Posted on 2004-10-05
14
520 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 12226763
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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