importing data into access from excel

[Over analyzed]

I wanted to know if anyone else is having issues with getting external data into access from excell? For some reason this function is not working for me today.

Any help would be greatly appreciated.

Thanks

 

[Moniker]

You'll have to be a lot more specific than that. What errors are you getting? How are you trying to import? What steps have you taken to identify the issue you're having? What specifically is the issue?

 

[Over analyzed]

You'll have to be a lot more specific than that. What errors are you getting? How are you trying to import? What steps have you taken to identify the issue you're having? What specifically is the issue?

Thanks for the reply- I truly appreciate all your help.

Due to the limitations of excel being 65k rows I am taking 3 years (05,06,07) information from various spread sheets and trying to incoporate them to one access data base.

Normally how I do this is, open a blank/new data base in access, then create data base by entering data, followed by going to import external data.
Then just find the respective excel docs and import them. How ever when I do this now it will not allow me to do so.

When ever I select an excel file it says make sure the file exists, or incorrect file selected etc. Yesterday I spent a majority of the day trying to figure out a solution. The only way I can get what I need out of access is:

1) open excel file
2) cut and paste information to new excel file
3) save it as the excel default (book 1, 2 etc)
4) then go to access and import external data.

A little tedious and unnecessary to say the least.

Thank you for reading this and your help in the matter.

 

[Moniker]

Is it just a standard Excel file, not an XLA? When you copy/paste the Excel data from one workbook to a new workbook, can you import from that new workbook? It's difficult to recreate your problem as I've never heard of it before. However, it sounds like you've got some versioning issues, as in you're trying to import Excel 2007 workbooks or something similar. Have you ever been able to import these sheets?

 

[GaryPanic]

no - try range
and select a year into a empty table
repeat for all 3 years into 3 tables
then append 2 tables in to 1 complie

should work

no reason why its doing this

but as a start make sure both excel book and access are not on a server but on a local pc (works faster and smarter that way)

 

[Ryko]

I prefer to save an excel sheet as text, link the text file it to ms access then append to a table.

 

[Over analyzed]

Is it just a standard Excel file, not an XLA? When you copy/paste the Excel data from one workbook to a new workbook, can you import from that new workbook? It's difficult to recreate your problem as I've never heard of it before. However, it sounds like you've got some versioning issues, as in you're trying to import Excel 2007 workbooks or something similar. Have you ever been able to import these sheets?

The excel file and just standard excel file. (around 50k rows of information per excel file)

When I copy and past excel data from one workbook to a new I can then import to access.

I had no issues doing this last week. I am using 2002 excel and access

 

[Over analyzed]

also this used to be the fastest way for me to do this.
End result I want to be able to do pivots in excel with all the data in access

 

[boblarson]

Just an FYI for everyone too, in case you hadn't heard -

Excel 2007 now allows 1,048,576 rows by 16,384 columns

 

[Over analyzed]

Just an FYI for everyone too, in case you hadn't heard -

Excel 2007 now allows 1,048,576 rows by 16,384 columns

I am waiting for my IT team to upgrade....

 

[The_Doc_Man]

Bob, 1M x 16K ? Holy crap, that's an aggregate of 34 bits - 20 for the rows x 14 for the colums. Used to be that you had 32 bits - 16 each for rows & columns. This is a serious difference.

Among other things, this makes it possible to leave some things in Excel that formerly would have been migrated to Access due to sizing issues.

 

[boblarson]

Bob, 1M x 16K ? Holy crap, that's an aggregate of 34 bits - 20 for the rows x 14 for the colums. Used to be that you had 32 bits - 16 each for rows & columns. This is a serious difference.

Among other things, this makes it possible to leave some things in Excel that formerly would have been migrated to Access due to sizing issues.

Isn't that the truth.

 

[Moniker]

Doc, out of curiosity, what math did you use to get your numbers? I think I'm missing something obvious.

 

[The_Doc_Man]

Rule of thumb to get orders of magnitude

2^10 approx = 10^3 so 10^6 is roughly 2^20.

Then 16K = 10^3 * 16 = 2^10 * 2^4 = 2^14

So it now takes 34 bits to express addresses whereas it used to be 65K rows which is 16 bits.

I'm just noting that they have expanded the address space they expect to be able to use. The only saving grace is that Excel, like Access, is designed to be sparsely populated, not densely so. Otherwise, you would 34 bits of addressing * cell size, which could trivially be 16 Gbytes - ON TOP OF the addressing markers. That's more memory than you can put in most systems unless they are set up for 64-bit addressing. Like, maybe Longhorn?

The HP Integrity server could do that. But most simple systems could not. We are talking blade servers with massive memory mapping abilities or perhaps a swap disk with a really long Mean Time Between Failures, 'cause that kind of data sets would beat the hell out of it performance-wise.

 

[Bat17]

Oh the fun we will get when people start trying to import spreadsheets with 365 columns

Over analyzed
Try checking the general tab in options in XL an make sure that "Ignore other Application" has not beed checked

Peter

 

[Over analyzed]

ok now this is getting really annoying to say the least.
Why is it that when I have an excel file, lets say john doe that is saved on my desk top - I open access, go to create table by entering data
file-import external data- find file john doe go to import it BAM error message = make sure john doe is a valid name or it contains invalid characters or the the name is to long?????

Next if I take excel file john doe cut and paste it and create a new excel doc which by default saves as book 1 it works just fine????

this is just nuts.....

On a side note happy friday every one

 

[GaryPanic]

BAT17 -- where are in M/stone - I am in the sticks just outside
M.Stone

 

[Bat17]

Harrietsham, Halfway between M/stone A/ford on A20