Importing into Access from Excel

Topic

New Reply

Attached is a spread sheet that I am trying to bring into an Access Database but I keep getting an error message stating it cannot be done. I realize this spreadsheet starts at column A and ends at column BE which is quite wide. The data is only numbers and text and counts about 6500 rows. Any idea what I am doing wrong. Another question, Is it possible to import formula into Access?

Thanks

Reply | Quote

Replies

A couple of things to try:

[*]Remove the first two blank rows. Column headings should be in Row 1[*]Remove special characters like # % from the field names

No you cannot import formulae.

Reply | Quote

Another question, Is it possible to import formula into Access?

Further on this. When you import from Excel the import goes into a table. An Excel spreadsheet supports both stored values and calculations (using formulas)
An Access table contains just stored values, so there is no scope for formulas in an Access table.

In Access, calculations are performed in queries, in code, in form or in reports, and there is no scope to import to them. But importing is not that important. In Excel you need to repeat the formula for each row of data. In an Access query, you write the formula just once, and it is applied to every line of data.

Reply | Quote

I have been trying to import the attached sample into access but Access keeps denying . Any ideas what’s wrong with this spread sheet.

Thanks

Reply | Quote

I get a message that the search key was not found in any record when I try to import it, but I can link to it just fine. I used Access 2007, and I see you are using 2003. I suspect there is something hidden in the data which is causing the error, but it isn’t obvious.

Reply | Quote

Some of the Column Headings (eg Gross Pay ) begin with a space character. Try removing all leading spaces in column headings.

ps: I have merge this thread back into the one where you first raised this issue.

Reply | Quote

Thank you. I will eliminate the leading spaces and try again

Reply | Quote

It is probably not the spaces. You have two columns with the same name. When I put a 2 after one of them, I was able to import into Access 2007 just fine. I did notice a lot of leading blanks in row 1, so you might want to take a look at that. I did remove the leading blanks, but really think the duplicate heading was the problem.

I hope this is a one time thing for a report and not the start of an Access database. It jumps out at me that you will end up with 4 sets of fields to designate four facilities (?). In a database you should have one set with 4 rows. But that is a separate subject.

Reply | Quote

It is probably not the spaces.

I did not notice the duplicate columns. But leading spaces have caused me a lot of angst in the past, so they were my prime suspect.

Reply | Quote