none
Importing dbf file with 1million+ rows into excel RRS feed

  • Question

  • I am new to excel and I can see that everyone here is way more advanced than me.   I have posted my question on several excel forum boards and no one will answer my question.

    I have a .dbf file that contains more than 1,048,576 rows.  I have tried to open it in Access, but receive an error message.  I have opened it in Excel, but it cuts off at 1,048,576.

    I want to get all the data, but I don't know how to access the other rows beyond what Excel will import.   The only way I know to open the file is to right click and press open.  It automatically opens in Excel.

    I hope I've expressed problem so that someone can help me. 

    Sunday, January 25, 2015 10:38 PM

Answers

  • Yeah, that's the maximum number of rows in Excel.  The number of records in MS Access in basically unlimited.  The only limitation is that you can't import a file that will cause Access to grow over 2GB in size (including fragmentation, which occurs during the import process).  Why don't you import it into SQL Server Express (it is totally free).  Then, you can do whatever you want with it.  Also, you can open pretty much any size file using Notepad and Notepad ++.  You can also work with large files using R and Python, both of which are 100% free.

    So, you have lots of options actually.  If you describe the objective, I can try to tailor the optimal solution better.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Wednesday, January 28, 2015 2:48 PM
    • Marked as answer by CaillenModerator Tuesday, February 3, 2015 1:48 PM
    Tuesday, January 27, 2015 4:51 AM

All replies

  •  AFAIK there is no way that you can't get more rows into Excel.

    What error message do you get in Access?

    Are you Opening access first and using the Import facility?

    How long is the dbf file name? If longer than 8 characters then try changing to a name with only 8 characters and then import to Access.

    Try the following link to download a dbf viewer.

    http://www.dbfview.com/how-to-open-dbf.html

    You could also try the free Libre Office that has Base which can open most database files.

    http://www.libreoffice.org/

     


    Regards, OssieMac

    Sunday, January 25, 2015 11:40 PM
  • The error message I receive is:

    ...could not find the object parcelnorth.dbf...

    I get this message when I open Access and simply try to open the file or when I try to import it through the IMPORT dBase file.

    It was a longer name, so I renamed it, but that didn't help.

    THANK YOU SO MUCH for taking the time to help me!

    Lisa

    Sunday, January 25, 2015 11:51 PM
  • Yeah, that's the maximum number of rows in Excel.  The number of records in MS Access in basically unlimited.  The only limitation is that you can't import a file that will cause Access to grow over 2GB in size (including fragmentation, which occurs during the import process).  Why don't you import it into SQL Server Express (it is totally free).  Then, you can do whatever you want with it.  Also, you can open pretty much any size file using Notepad and Notepad ++.  You can also work with large files using R and Python, both of which are 100% free.

    So, you have lots of options actually.  If you describe the objective, I can try to tailor the optimal solution better.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Wednesday, January 28, 2015 2:48 PM
    • Marked as answer by CaillenModerator Tuesday, February 3, 2015 1:48 PM
    Tuesday, January 27, 2015 4:51 AM
  • WOW, that is so awesome.  Thank you so much for helping me.  I really appreciate it so much!  I tried Notepad++, but maybe I did something wrong.  I have made note of these other programs and will investigate using them as well.

    Hope you have a SPECTACULAR day!

    Lisa

    Wednesday, January 28, 2015 1:33 PM