Problems importing from spreadsheet

[Guest]

I have a spreadsheet (excel 2000) from which I wish to export to an Access
2000 database table.
I have set up a simple macro to try and automate this process. The macro is
as below:
-------------------------------
Action : TransferSpreadsheet
Transfer Type: Import
Spreadsheet Type: Microsoft Excel 8-9
Table Name: Raw Data
File Name: C:\Documents and settings....
Has Field Names: Yes
-------------------------------

When I try to run the macro, it informs me that "Field 'F131' doesn't exist
in destination tabel 'Raw data"

I have had a look in the MS resource website, and have found a description
of a similar problem ( see article ID 208380). However, its solution was to
set the 'Has Field Names' control to "yes" and ensure that the feild names in
the spreadsheet match that of the database table. As you can see from the
above, I have already done this, and so am not sure why this problem is
occuring!

Can anybody help?

Phil

 

[John Nurick]

Hi Phil,

Try this: in Excel, delete all the columns to the right of your table.
It's possible that some of the cells in these columns have been used and
Access is trying to import them. Alternatively, define a named range in
Excel that includes nothing but the "field names" and the data you want,
and then set your macro to import that range.

 

[Guest]

Thanks for your help john,

I've tried your suggestion re: deleting all the right hand columns but it
hasn't worked. The problem with defining the named range, is that I want to
automate the process to accept tables of differing sizes ( ie different
amount of rows). If I define a range, it will ignore any rows outside of that
range.

As far as I can see, this should not be a problem, and so Im a bit confused
why Access is not allowing me to do what I want!

Got any more ideas????

Yours in desperation

Phil

 

[John Nurick]

Hi Phil,

The 'F131' in the error message suggests that there are at least 131
columns in your data. In that situation I'd be almost certain to have
mistyped one or more of them; are you absolutely certain that that
hasn't happened?

One way of checking this is to export both the Excel data and the Access
data to CSV files and then compare the first lines of the files.

 

[Guest]

Thanks for the tip,

I've compared all of the column headings and they're identical!

I've also started again, but the results are still the same. I think it must
be a MS Access problem ( although nobody else seems to experience it!).

Unless you have any other ideas, I think I may scrap the idae of using
Access, and try and do it from Excel!

Thanks for your patience and help

Phil

 

[John Nurick]

Here are some more things I'd try before giving up:

A: Create a new query and switch to SQL view. Type an SQL statement
using this syntax with your details:

INSERT INTO
MyExistingTable
SELECT *
FROM
[Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet1$A:Z]
;

This will ignore anything outside the specified range of columns.

B: Import the data to a new table and compare the structures of the new
table and the existing one.

C:
1) create a new workbook
2) copy your data (including column headings) but nothing else from the
existing workbook to the first worksheet in the new workbook.
3) save & close
4) import from the new workbook.

If this works, I'd suspect there's some problem in the original
workbook.

D: Export from Excel to a CSV file and import the CSV file into Access.

 

[Guest]

Its sorted!!

Your comment regarding issues with the spreadsheet itself was correct. There
were several N/A values in the spreaqdsheet, but anly some of them weren't
being imported. Afetr removing all of the N/A values to leave only blank
cells, the data imported over perfectly!

Many thanks for your help

Phil

Here are some more things I'd try before giving up:

A: Create a new query and switch to SQL view. Type an SQL statement
using this syntax with your details:

INSERT INTO
MyExistingTable
SELECT *
FROM
[Excel 8.0;HDR=Yes;database=C:\MyWorkbook.xls;].[Sheet1$A:Z]
;

This will ignore anything outside the specified range of columns.

B: Import the data to a new table and compare the structures of the new
table and the existing one.

C:
1) create a new workbook
2) copy your data (including column headings) but nothing else from the
existing workbook to the first worksheet in the new workbook.
3) save & close
4) import from the new workbook.

If this works, I'd suspect there's some problem in the original
workbook.

D: Export from Excel to a CSV file and import the CSV file into Access.

Thanks for the tip,

I've compared all of the column headings and they're identical!

I've also started again, but the results are still the same. I think it must
be a MS Access problem ( although nobody else seems to experience it!).

Unless you have any other ideas, I think I may scrap the idae of using
Access, and try and do it from Excel!

Thanks for your patience and help

Phil

 

[Guest]

I have a spreadsheet (excel 2000) from which I wish to export to an Access
2000 database table.
I have set up a simple macro to try and automate this process. The macro is
as below:
-------------------------------
Action : TransferSpreadsheet
Transfer Type: Import
Spreadsheet Type: Microsoft Excel 8-9
Table Name: Raw Data
File Name: C:\Documents and settings....
Has Field Names: Yes
-------------------------------

When I try to run the macro, it informs me that "Field 'F131' doesn't exist
in destination tabel 'Raw data"

I have had a look in the MS resource website, and have found a description
of a similar problem ( see article ID 208380). However, its solution was to
set the 'Has Field Names' control to "yes" and ensure that the feild names in
the spreadsheet match that of the database table. As you can see from the
above, I have already done this, and so am not sure why this problem is
occuring!

Can anybody help?

Phil


I had a similar problem but I used the FILE/GET EXTERNAL DATA/IMPORT option within Access. However, not only should the field names in Excel exactly match the ones already set up in Access, the field types must be the same too. As you use the Wizard in Access to import, click on advanced and you can set the field types to match the Access ones. It should then work.

I have a problem though just in case you have come across this one. Using
the above scenario, the access table contains an autonumber field. The Excel
file didn't. When I imported the excel file, the autonumber jump from last
record in original database at around 17000 to next field being something
like 4646090003 although the record was showing 17001 in the display box at
the foot of the table.