 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 20071 Million Rows ??
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. > 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 Buy the new 2007 version of Excel to get a million rows. Gord Dibben MS Excel MVP >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. 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??? > Buy the new 2007 version of Excel to get a million rows. > > Gord Dibben MS Excel MVP > > >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. 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 >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 [quoted text clipped - 6 lines] >> >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. Thanks Gord. > If a workbook was created in an earlier version only 65536 rows will be shown > and available. [quoted text clipped - 20 lines] > >> >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. 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. > 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. 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! > See the above responses - especially the first one from Gord Dibben. The > 'simple' answer is, as he said, get Excel 2007. [quoted text clipped - 19 lines] > > 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. <<<"I am now officially divorcing Access and re-marrying my old spouse - EXCEL!">>> WATCH OUT ! ! ! Aaron will get you!<bg>  SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > 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 [quoted text clipped - 30 lines] > > > 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. 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&Origi n=HA100860481033 > 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 [quoted text clipped - 30 lines] > > > 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. Thanks for providing that reference. I just cleaned it up and its came out at 600 kb SignatureJim 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&Origi n=HA100860481033 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. > Thanks for providing that reference. > I just cleaned it up and its came out at 600 kb > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.