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]

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