Did you ever faced a scenario were you needed to load a collection of CSV/Text files into SQL Server tables?
What solution did you choose?
- TSQL BULK INSERT?
- SSIS Package (generated from SSMS Tasks->Import Data or manual)
- PowerShell “Import-CSV”
And what if the SQL Server destination tables must be typed (numeric, date, text columns,…) and the CSV file has formatting issues (ex: text columns without quotes, datetimes not in ISO format) and you need to transform the columns into the desired types?
A much quicker solution to transform CSV files into the desired shape is using a PowerBI Desktop query (or PowerQuery), for example in seconds I can:
- Load the CSV
- Replace a value from all the columns (in this case “NULL” from a real null)
- Auto detect the datatypes
Now to load these queries into a SQL Server database, it’s very easy thanks to DevScope powershell module “PowerBIETL” (also available at PowerShellGallery):
Install-Module PowerBIETL
Import-Module PowerBIETL
Export-PBIDesktopToSQL -pbiDesktopWindowName "*sample*" -sqlConnStr "Data Source=.\SQL2014; Initial Catalog=DestinationDB; Integrated Security=SSPI" -sqlSchema "stg" -verbose
The cmdlet “Export-PBIDesktopToSQL” will take care of:
- Connects to the PBI Desktop and read the tables
- Automatically create the tables on the SQL Database (if they do not exist)
- Thanks to DevScope “SQLHelper” powershell module and “Invoke-SQLBulkCopy” cmdlet
- Bulk copy the data from PBI Desktop into the SQL Table
The cmdlet has 4 parameters:
- -PBIDesktopWindowName (mandatory)
- A wildcard to find the PowerBI Desktop window
- -Tables (optional, defaults to all the tables)
- Array of tables to import
- -SQLConnStr (mandatory)
- Connection to a SQL Server database
- -SQLSchema (optional, defaults to “dbo”)
- The schema under the tables will be created
As a result all the tables from the PBI Desktop file will get copied into the SQL Server database:
Off course this will only work to those “one-time-only” or manual scenarios, but I assure you that is much quicker than using a SQL Integration Services package 
Rui Romano
Awesome solution! Any way we can use it with the SQL UPDATE function?
LikeLike
Thanks, not sure if I understand your goal :). Could you please explain your goal?
LikeLike
Sure. I pull data into numerous excel workbooks (100+), which contain weekly sales data for grocery store products. Currently that data populates 70 reports in Power BI. Sometimes historical data is updated and as a result, when our dataset is refreshed (every 4 weeks) we pull the latest 6 months of data into these workbooks. Using your tool, I would create one data table in SQL (Azure), but I couldn’t load all of the data once, it would have to load one report at a time. I would want the data to UPDATE the table to avoid duplications. The duplications could occur due to overlapping categories or time periods. I hope that makes sense.
LikeLike
Ok, so basically you want to read from pbix 6 months of data and insert that data into SQL without deleting the existing data. Is that right?
If Yes, then you could execute the export cmdlet to a staging table (that will always get truncated) and then execute a MERGE cmd in TSQL to insert only new data and update existing records.
Make sense?
LikeLike
Yes, thank you. I will give it a try. Again, nice work and thank you for sharing!
LikeLike