G
Guest
How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.
can now handle 1 million rows - but I cannot figure out how.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
gls858
Kevin said:How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.
In Access go to File > Get external Date > Import
Only if you have Excel 2007.
Where is this data how are trying to import it?
gls858
G
Gord Dibben
Buy the new 2007 version of Excel to get a million rows.
Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP
G
Guest
If, by chance, he actually has XL2007 - isn't there some sort of feature that
will restrict it to 65K rows (for backward compatibility maybe) that has to
be turned off???
will restrict it to 65K rows (for backward compatibility maybe) that has to
be turned off???
G
Gord Dibben
If a workbook was created in an earlier version only 65536 rows will be shown
and available.
From a post by Bob Umlas
If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas
Excel MVP
Gord
and available.
From a post by Bob Umlas
If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas
Excel MVP
Gord
G
Guest
Thanks Gord.
Gord Dibben said:If a workbook was created in an earlier version only 65536 rows will be shown
and available.
From a post by Bob Umlas
If you're opening a file last saved in 2003, it will open in "compatibility
mode" which displays the same limits as xl2003. You need to convert it to
the current format. Use Office Button/Convert. Note that this will delete
the original. You'll then have all the cells.
Bob Umlas
Excel MVP
Gord
G
Guest
See the above responses - especially the first one from Gord Dibben. The
'simple' answer is, as he said, get Excel 2007.
Barring that, the only practical way to do it is to use VBA code to open up
the Access database and start reading in records, one by one, and keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the same
number of rows in Excel 2003 that you can have in Excel 2007, you'll need a
total of 16 sheets.
But getting the new version of Excel may be the quick, easy answer. I just
finished giving some assistance to someone from another web site who had need
of the same thing: to be able to read in over 64K rows of data from either
..txt or .csv files and first thing I told him was to get Excel 2007. He did,
and this evening I coded up a process so that he can read the output from his
test equipment - sample file he sent to me had over 185,000 rows of entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.
'simple' answer is, as he said, get Excel 2007.
Barring that, the only practical way to do it is to use VBA code to open up
the Access database and start reading in records, one by one, and keeping a
counter of the records imported. When that gets to the limit for your
version of Excel (varies from 16K to 64K over the years) then you have the
code insert a new page and switch to that page to continue importing the
data: repeat as required until all records are read. Note: to get the same
number of rows in Excel 2003 that you can have in Excel 2007, you'll need a
total of 16 sheets.
But getting the new version of Excel may be the quick, easy answer. I just
finished giving some assistance to someone from another web site who had need
of the same thing: to be able to read in over 64K rows of data from either
..txt or .csv files and first thing I told him was to get Excel 2007. He did,
and this evening I coded up a process so that he can read the output from his
test equipment - sample file he sent to me had over 185,000 rows of entries.
Works like a champ - I even tested with one file that we created that
actually filled all 1,048,576 rows and it held up under the strain.
G
Guest
Thanks Gord. I do have Excel 2007 - I made the Company upgrade my laptop to
Excel 2007 specifically for the 1 million row feature. I got a little
stressed out Friday afternoon when I opened an existing file and could not
expand the file beyond the old 63k + limitation.
I have not yet found the "Office Button / Convert" feature - but I copied
the old worksheet into a new Excel 2007 file, and then expanded it - and it
works!
I am now officially divorcing Access and re-marrying my old spouse - EXCEL!
Excel 2007 specifically for the 1 million row feature. I got a little
stressed out Friday afternoon when I opened an existing file and could not
expand the file beyond the old 63k + limitation.
I have not yet found the "Office Button / Convert" feature - but I copied
the old worksheet into a new Excel 2007 file, and then expanded it - and it
works!
I am now officially divorcing Access and re-marrying my old spouse - EXCEL!
R
Ragdyer
<<<"I am now officially divorcing Access and re-marrying my old spouse -
EXCEL!">>>
WATCH OUT ! ! !
Aaron will get you!<bg>
EXCEL!">>>
WATCH OUT ! ! !
Aaron will get you!<bg>
G
Guest
Good luck with the settlement!
Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.
If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/redir.aspx?AssetID=AM101864291033&CTT=5&Origin=HA100860481033
Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.
If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/redir.aspx?AssetID=AM101864291033&CTT=5&Origin=HA100860481033
J
Jim Cone
Thanks for providing that reference.
I just cleaned it up and its came out at 600 kb
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"JLatham"
<HelpFrom @ Jlathamsite.com.(removethis)>
wrote in message
Good luck with the settlement!
Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.
If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/redir.aspx?AssetID=AM101864291033&CTT=5&Origin=HA100860481033
I just cleaned it up and its came out at 600 kb
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"JLatham"
<HelpFrom @ Jlathamsite.com.(removethis)>
wrote in message
Good luck with the settlement!
Some things Excel does better than Access, some things Access excels at.
Often the combination of them working together is an awesome tool.
If you haven't seen it yet, Microsoft has an Excel workbook (2MB) that maps
out the Excel 2003 commands to their new locations in Excel 2007. You can
get a copy of it from the link near the bottom of this page:
http://office.microsoft.com/search/redir.aspx?AssetID=AM101864291033&CTT=5&Origin=HA100860481033
G
Guest
I found another link to it later. In Excel 2007 Help, under "What's New" the
second topic page also provides a link to it. I found it originally while
reading some of the 'Crabby Office Lady' articles which led me to it.
second topic page also provides a link to it. I found it originally while
reading some of the 'Crabby Office Lady' articles which led me to it.
V
vitruvian
How can I import Access data that exceeds 65 rows? I understand that Excel
can now handle 1 million rows - but I cannot figure out how.
Hi Kevin,
Why don't you use the PivotTable function...
What it does is import all of the data into the memory of Excel and
caches it (very small file size/footprint), and then you can build the
table/report however you want...
As you use Data > Import External Data > New Database Query, you will
get to a point in the Wizard to use a PivotTable...
To be honest it's a little tricky for first time users, but are very
very powerful once you get the hang of it