By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

import xls to access with TransferSpreadsheet

P: 1
Hi all, i import xls to access with TransferSpreadsheet but here is the message "F1" doesn't exist in destination table ".." , but all columns are the same. where is the problem?
Jul 23 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
Hi all, i import xls to access with TransferSpreadsheet but here is the message "F1" doesn't exist in destination table ".." , but all columns are the same. where is the problem?
does your destination table have a field called F1?

you really should consider saving your spreadsheets as .csv then you can create an import specification to use during your import using the transfertext method. In this way, you can name your fields something a little more meaningful and define their data types as well.

J
Jul 31 '07 #2

100+
P: 114
I am trying to import data to two tables (STARTS and TASKS) from two Excel spreadsheets. The data from Excel is from an outside program that can't link with Access (so I am using Excel as a liaison between the program and Access). This is all using Office 2003.

I can successfully import data from Excel for the STARTS table through both a TransferSpreadsheet Macro and the File>Get External Data>Import options.

Until recently, my TASKS import worked just as well. However, my TASKS data import now brings up an error for both methods (see below). The fields in the Excel sheet and my TASKS table are identical and in the same order with only one exception: TASKS table has an autonumber ID. Could this be causing the issue? And if so, what to do? I need the TASKS unqiue autonumber ID for other relationships within the database.

Any help would be greatly appreciated. Hopefully, I've provided enough info.

Error when I run the TransferSpreadsheet macro:
"External table is not in the expected format"

Error when I run File>Get External Data>Import:
"The wizard is unable to access information in the file 'Z:\liveReport\ImportTasks.xls.' Please check that the file exists and is in the correct format."
Aug 29 '07 #3

JConsulting
Expert 100+
P: 603
I am trying to import data to two tables (STARTS and TASKS) from two Excel spreadsheets. The data from Excel is from an outside program that can't link with Access (so I am using Excel as a liaison between the program and Access). This is all using Office 2003.

I can successfully import data from Excel for the STARTS table through both a TransferSpreadsheet Macro and the File>Get External Data>Import options.

Until recently, my TASKS import worked just as well. However, my TASKS data import now brings up an error for both methods (see below). The fields in the Excel sheet and my TASKS table are identical and in the same order with only one exception: TASKS table has an autonumber ID. Could this be causing the issue? And if so, what to do? I need the TASKS unqiue autonumber ID for other relationships within the database.

Any help would be greatly appreciated. Hopefully, I've provided enough info.

Error when I run the TransferSpreadsheet macro:
"External table is not in the expected format"

Error when I run File>Get External Data>Import:
"The wizard is unable to access information in the file 'Z:\liveReport\ImportTasks.xls.' Please check that the file exists and is in the correct format."

If you're able to open your excel spreadsheet, and you haven't already done so, then save it as a lower version of Excel and try again. There is a chance that your spreadsheet was created in a later version of Office than the one you're using.
J
Aug 29 '07 #4

100+
P: 114
Excel is the same. This is part of a daily routine; importing data from excel into access, so I want to import to tables I already have set up.

I want my import to import from Excel into an existing table (TASKS in this case). Could the fact that this table has a unique ID that I added (ie is not in the spreadsheet I am importing) cause the import failure?

Is there a way to import to an existing table with a primary key that is not in the imported spreadsheet?
Aug 31 '07 #5

Jim Doherty
Expert 100+
P: 897
Excel is the same. This is part of a daily routine; importing data from excel into access, so I want to import to tables I already have set up.

I want my import to import from Excel into an existing table (TASKS in this case). Could the fact that this table has a unique ID that I added (ie is not in the spreadsheet I am importing) cause the import failure?

Is there a way to import to an existing table with a primary key that is not in the imported spreadsheet?

Just a thought FWIW sounds to me as though you have a corrupted schema.ini file lurking somewhere in the background did you set the import mechanism up? check out schema.ini file import methods for text files within the Access environment.
Aug 31 '07 #6

JConsulting
Expert 100+
P: 603
Excel is the same. This is part of a daily routine; importing data from excel into access, so I want to import to tables I already have set up.

I want my import to import from Excel into an existing table (TASKS in this case). Could the fact that this table has a unique ID that I added (ie is not in the spreadsheet I am importing) cause the import failure?

Is there a way to import to an existing table with a primary key that is not in the imported spreadsheet?
There is a couple of ways, and both are fairly simple. When you convert an excel spreadsheet to a .csv file, you can then use a Specification (spec) that you create by manually importing the file the first time, then clicking on the advanced button and mapping things out.

It's my experience though, when you import any kind if data, it should always go to a "temp" table first, then use an update or append query to clean up and move the data to the final table.

J
Aug 31 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.