Solved

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

Posted on 2004-10-05
14
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

617 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