Dear Mr Manish, I suggest you to take a look at Pentaho Data Integration (http://community.pentaho.com/projects/data-integration/) and Data Cleaner (http://datacleaner.org/)

Best regards.

Hello Mr. Pérez ,

I am looking at these two tools.

Hoping to get the best solutions that I expect.

Regards,

Manish

Hello sir,

I suggest you to go for ETL tools, or WinPure software for best result.

It depends on what you mean by data cleaning but broadly I agree with Vinod to look into ETL tools. Especially if you are talking about huge amounts of data.

Hello Mr. Pérez ,

I have tried following tool,

Pentaho Data Integration (http://community.pentaho.com/projects/data-integration/)

to may dismay, the tool has bugs and failed to start.

There are several on SourceForge. A new one is http://sourceforge.net/projects/dataquality/?source=typ_redirect

Thank you Hamideh Iraj,

Open Refine looks very promising

Dear Mr Manish, I don´t know about that PDI bugs.  Try to donwload from Sourceforge (http://sourceforge.net/projects/pentaho/files/Data%20Integration/)

Best regards.

Can you upload a sample data and final representation of this data ?

I think it is possible to perform in excel.

Your topic and question are not directly related to each other. The topic asks for data cleansing, while the question relates to creating reporting (cube) and also to datawarehousing. These three issues are related to different tasks and technologies.

Data storage and data warehousing

First, you do not have Excel database but only Excel data (source). Database is different technology than spreadsheet application or spreadsheet files. You can do data warehousing based on any data: databases, files, APIs etc. In practice, should get somekind of database system, such as MySQL, SQL Server or Oracle. Export or Upload your Excel data to any real database.

Instead of traditional databases, you might want to try automatic data warehousing appliance such as WhereScape, BIReady, or Attunity.

Data cleansing and transforming

Second, you might benefit from Data Integration or Data Quality tools while uploading your data to databases. SQL Server has Data Integration tools integrated in the same platform. Alternatively, you could try to find other platform, such as Pentaho Kettle or Talend, to transform data from Excel to database. These tools might have data cleaning features that you can insert to your data flows.

The choice of data integration and quality tool depends on your requirements such as speed, volumes, easy-of-use, features and so on.

Dimensional reporting and visualizing

Third, you need Business Intelligence platform to create a dimensional cubes. SQL Server has also BI services integrated to a single platform. Alternatively, you could create a cube with Pentaho or some other BI tools. Instead of that, you might want to try non-cube reporting such as Qlikview or Tableua. Install Qlik or Tableua on top of SQL server, that stores your previous Excel data. You can then define dimensions and facts without traditional physical dimensional cubes.

The choice of BI platform and selecting correct BI tools from all the alternatives depends what kind of processing you want to do for your data warehouse data sets and how you want to deliver them to presentation layer.

My Choice

If I would have to do something quickly in your situation, I would get MS SQL Server and install it on my laptop. I would then upload Excel data to that database. This is the first and most important step if you want to do serious data warehousing and cleansing. Then I would try profiling and cleansing data with DataCleaner or SQL Server Integration Services.

After my data warehouse is loaded with data, I would get Tableua and install it to my laptop. I would connect my Tableua to SQL Server (if data needs serious automatic cleaning) or even directly to Excel-sheet (if no need for building integration and cleaning). I would build dimensions and facts with Tableua and use it to build reports and visualizations.

You can use SQl Server Integration Service (SSIS) tools to upload excel file into SQL Database and then performing cleaning step through SSIS tool.

Regards

http://sqlbits.com/sessions/event11/cleaning_up_dirty_data_with_ssis