If you have a large data set consisting more than 1 M (=million) rows, it is not useful to download to Excel worksheet. You can use text file and then create a Power Query based on the text file. However, Access is my choice for storing this large data amount.

I have downloaded data from eforexcel.com page. Thanks for organizing the sample data. I chose 5M rows of HR data.

Create a blank database and download the data from an external source. The path is external data – new data source – from file – text file.

Import the text file, just activate the first row containing field names. Luckily data falls under correct headers.

In my version, I changed the decimal symbol from comma to dot. Then it is not confusing as field delimiter is comma.

It took some time to get data loaded, but it still works. Now I have 5M records in Access in one table.

I can still check with SQL script the total number of records.

Different education records are here:

Let’s see how many hits.

Data was divided evenly.

This is the SQL script

SELECT educationfield, COUNT(educationfield)

FROM 5M

GROUP BY educationfield;

A data model with 5M records was downloaded into Excel.

The same results can be found in Excel too.

Let’s make another check. Do genders differ when it comes to business travelling ?

The business travelling is pretty evenly divided.

SELECT gender, businesstravel, COUNT(businesstravel)

FROM 5M

GROUP BY gender, Businesstravel;

If you need to make an adhoc analysis for a data with 5M records, Access is a viable choice for storing the data. The reporting was done in this example in Excel. All the data was stored in one table and no relations were needed. One thing to notice, the Access file with the data was quite big 2 GB. That is taking capacity from the hard disk.