# Alternative to Excel for handling large datasets



## liiamra

Hello Everyone,

I have few datasets containing millions of records. 

Is there any program that can be used for the purpose of handling and managing such large datasets? Kindly note that each dataset is contained in one table.

ATM, I am using Excel, but the problem is that I have to split the dataset first, and then always write codes, even when a very simple figure is needed. I tried OpenRefine for data analysis. It is a very good program, but it is not very efficient in handling large datasets.

I don't really have problem with storing the data in one table; SQLite or others can do it, but I need a tool that allows me to analyze and handle the data.

I tried Access, but it failed with my datasets.

Any advice is highly appreciated,
Thanks all,
LA


----------



## xenou

Any sql based database will work.  You can also use "big data" software:
NOSQL Databases

Most proprietary databases and software programs have "free" versions that are limited in size (anywhere from 2-10GB).  Most open source software gets you the full blown program without limitations.

A lot really depends on what you mean by "analyze".


----------



## liiamra

Thanks Xenou,

I will give a direct example. Suppose I have a table with three columns: type, price, and country.

Simple speaking, I need to be able to extract statistical figures, such as the average, min-max and so on. Then, I would need to be able to filter and export results based on certain criteria. 

If Access was able to handle large datasets, it could be very sufficient soltuion as I can create forms and reports so it would be easy to generate any subset out of the main. I mean, I could create a friendly user interface that others could easily use. In addition, forms and reports allow me to make calculations in order to find errors.

Hope my request is better defined now.

Thanks again and all the best,
liiamra


----------



## RoryA

That sounds like the sort of thing PowerPivot could do.


----------



## Namestaken

Check out R: The R Project for Statistical Computing

It has a learning curve but it is one of the most versatile tools you can use even with huge datasets.


----------



## xenou

With four fields (Id autonumber, Price decimal, type text(10), and country text (25) I can squeeze about 30,000,000 records into Access (1.91 Gb).  I'm not experienced with how well it performs at that size (my databases in frequent top out at around 0.5 Gb).  It can sum the price column in about 7 seconds.  You can also link Access databases to effectively increase the storage size (if you can have more than one table).

I haven't used Power Pivot - it definitely should be in the list though too.

And R.  Agree it is a bigger learning curve but a very neat program.  I think it would be limited to available memory (it runs in RAM - of course available RAM is often measured in Gb these days).


----------



## Smitty

RoryA said:


> That sounds like the sort of thing PowerPivot could do.



Absolutely!


----------



## SydneyGeek

SQL Server Express is another free option. 
You can either build your views there or take up Rory's suggestion if you have Excel 2010 or higher.

Denis


----------



## Cindy Ellis

Another option for statistical analysis of large or small datasets is MiniTab.  Definitely pricey, but very powerful.  Other than Excel (which is my first go-to program), I've used MiniTab, SPSS, and R, listed in order of ease of use (for me, at least).  Another statistical program that is very powerful is SAS. 
Hope that helps,


----------



## liiamra

Dear all,
Thanks a lot for your help.


*#RoryA:* I don't have problems if Excel could handle more than million records. As explained above, my issue is the size.


*#Namestaken:* I already have experience with R. Although R-Studio makes the UI looks good, I need a tool (language or program) that allows me to create my own interface.


*#Xenou:* In my case, I have 52 columns, mostly text-strings, with 3 long ones. I already tried Access, but it failed. It also has the 2GB limitations. Maybe I could use Access with SQL Server.


*#Smitty: *Please see the 3rd line above.


*#SydneyGeek:* Rory's suggestion wouldn't work, but as I explained to #Xenou, one option might be Access+SQL.


*#Cindy:* Thanks for your suggestions; helpful indeed. I am currently using mainly Matlab, and sometimes Stata. SAS is very powerful, and we have multiple license in our center. I might need to have a look at it.



Honestly speaking, all your suggestions are helpful - many thanks, but in my case, I need to create a user interface that allows me to handle the data.


I have previously, few years ago, developed a payroll system using Access and it was superb (you may have a look at it here). I was looking at something that allows me to do something similar, but on a larger scale. ATM, I don't need to add data, but just analyze.

Thanks again and all the best,
liiamra


----------



## liiamra

Hello Everyone,

I have few datasets containing millions of records. 

Is there any program that can be used for the purpose of handling and managing such large datasets? Kindly note that each dataset is contained in one table.

ATM, I am using Excel, but the problem is that I have to split the dataset first, and then always write codes, even when a very simple figure is needed. I tried OpenRefine for data analysis. It is a very good program, but it is not very efficient in handling large datasets.

I don't really have problem with storing the data in one table; SQLite or others can do it, but I need a tool that allows me to analyze and handle the data.

I tried Access, but it failed with my datasets.

Any advice is highly appreciated,
Thanks all,
LA


----------



## SydneyGeek

If you like and are comfortable with Access for building the interface, stick with it. 
As xenou suggested, hook it up to a 'proper' back-end database to handle the volume. Really doesn't matter which database you hook it to. I have used front ends with SQL Server or Oracle back ends.

Denis


----------



## Cindy Ellis

If you only have 52 columns, could you split the data not between sheets, but into multiple sets of columns on the same sheet in Excel?  If you're developing a UI to handle the data anyway, you might also be able to manage the data more easily on the same sheet rather than split between sheets.
Regarding Minitab, I don't think there's a mechanism to develop a UI. 
Best of luck with your project.


----------



## RoryA

I wasn't suggesting Excel for the data storage - you would require a database of some sort for that, but PowerPivot can handle millions of records so you can do the analysis and manipulation in Excel/PP.


----------



## liiamra

Dear All,

Really sorry, but I just noticed your replies after I logged-in again, though I thought I would receive an email.

Anyways, Thanks again for your suggestions. 

Denis, Cindy, Rory, and everyone else. Thanks alot for your comments and hints; they are indeed very helpful.

Regards+
liiamra


----------

