Importing single or multiple CSV files in Power BI is breeze however at times due to big file size having GBs of data or millions of records this task may become difficult and resultant it may throw unexpected errors or simply chocks your PC memory.

Let’s look at some ways you can import a big CSV file in Power BI:

  1. Get Data > Text/CSV
  2. Import CSV in SQL then Get Data > SQL Server
  3. Get Data > ODBC
  4. Python, R and some more methods can also be used

We will explore ODBC method which is generally fast, less memory intensive and less error prone.

Steps for importing CSV/Text data via ODBC:

1) Add CSV/Text DSN via ODBC Data Source Administrator
Click on Start Menu > Windows Administrative Tools > ODBC Data Source Administrator
If you are not able to find it. Please check below link for details:
https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver15


Click Add. Select “Microsoft Access Text Driver (*.txt, *.csv)” from the list and click Finish


On set up screen, enter the Data Source name and Description. Click OK

Now you should be able to see new option. Click Apply and OK to exit.

2) In Power BI, click Get Data. Select ODBC and connect.

3) ODBC setup
DSN:
Select TXTfile or the relevant text DSN

Connection String:
dbq=d:\yourfolder\;extensions=asc,csv,tab,txt;

SQL statement:
Select * from yourfile.csv
Click OK.

4) Authentication
If asked for authentication select “Default or Custom” or select relevant option if file is not stored locally. Click Connect.

5) Preview & proceed
Click Load or Transform

Data should load approx. in 2-4 mins for ~10 million records depending on your PC configuration.

Once loaded the Power BI model will compress the data and you can expect the data size to be around 10% of original size. So a 1GB csv file will be 100MB pbix file.

Hope this helps! Feel free to drop your comments.

Cheers!

Advertisement