How to use R script in Power BI
All about SQLServer
by
3y ago
In this post, I have embedded the video link to my you tube channel and in the video I have showed how to use R script in Power BI. I have also posted the scripts shown in the video. How to use R script in Power BI --To enable external scripts execution EXEC sp_configure  'external scripts enabled', 1 RECONFIGURE WITH OVERRIDE --To print R version EXECUTE sp_execute_external_script @language = N'R'     , @script = N'print(version)'; GO --To list all R packages installed EXEC sp_execute_external_script @language = N'R'     , @script = N' OutputDataSet <- data.frame(installed.package ..read more
Visit website
SQL Server Import/Export to Excel using Python script
All about SQLServer
by
3y ago
Introduction In previous article, we saw how to import/export excel to/from SQL Server by executing R script within T-SQL.  In this post, let us see another similar approach to import excel into SQL Server and export SQL server data to excel by executing Python script within T-SQL. There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD. BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly    BCP - Workaround has to be done to incl ..read more
Visit website
T-SQL : Data profiling in On-premise SQL Server / Azure SQL database
All about SQLServer
by
3y ago
In this post, let us see how we can do data profiling on On-premise SQL Server / Azure SQL database tables using T-SQL script. Data profiling provides below high level information about data: Number of rows and size of the data in the object, date for the most recent update of the data and the object schemaNumber of null records, distinct values for columns and data type of columnMinimum, maximum values for numeric columns and check for existence of special characters, leading/trailing spaces for columns of string data type Two stored procedures are created to generate data profiling output ..read more
Visit website
T-SQL : Search for string or phrase in SQL Server database
All about SQLServer
by
4y ago
In this post, let us see how to search for a string / phrase in SQL Server database using hybrid solution of T-SQL LIKE operator & R grep function. Currently the options that exists in SQL Server to perform a search operation are LIKE operator Using string functions CHARINDEX, PATINDEX Full text search Consider below example: To search and return only records with string "VAT" . Expected result is to return record 1,5 & 6. DECLARE @Tmp TABLE (Id INT, Descrip VARCHAR(500))INSERT @Tmp SELECT 1,'my VAT calculation is incorrect'INSERT @Tmp SELECT 2,'Private number'INSERT @Tmp SELECT 3,'Inn ..read more
Visit website
SQL Server Export/Import excel using R script - Part 6
All about SQLServer
by
4y ago
In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3, Part 4 & Part 5. In this post, let us see how to invoke sql files in a folder and export the output of the query as Excel files. As a prerequisite, we need R packages "xlsx"  & "rJava", which I had showed in my previous posts on how to install and load them. As we are going to make use of the powershell SQL Server cmdlets "Invoke-Sqlcmd", let us make sure SQL Server powershell modules are downloaded & installed from Powershell. For this example, I have created below fold ..read more
Visit website
SQL Server Export/Import excel using R script - Part 5
All about SQLServer
by
4y ago
In continuation to my previous posts on SQL Server Export/Import excel using R script Part 1, Part 2, Part 3 & Part 4. In this post, let us see how to import multiple excel files in a folder (including sub folders) into different SQL Server table created dynamically based on excel file structure. For this example, I am going to make use of excel files generated using second script in Part 2 post. Also I have created sub folder named "Copy" and copied the same set of files into it, I have done this to ensure script has traversed through all the folders and works fine. Also to test if t ..read more
Visit website
SQL Server Export/Import excel using R script - Part 4
All about SQLServer
by
4y ago
In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 ,Part 2 & Part3 where we saw about exporting to Excel and importing to SQL Server table using "xlsx" package which is a powerful package but it has external dependency on Java ("rJava" package). In this post, let us see how to import excel with multiple sheets using "readxl" package into different SQL Server tables. I have done below example using SQL Server 2019 on windows 10 (64 bit) and R (3.4.4).Refer my previous post on how to install R services.  I have also used Wideworldimporters sa ..read more
Visit website
SQL Server Export/Import excel using R script - Part 3
All about SQLServer
by
4y ago
In continuation to my previous posts on SQL Server Export/Import excel using R script - Part 1 & Part 2, where we saw about exporting to Excel. In this post, let us see how to import Excel into SQL Server table using R script.  In part 2 post, I have exported few tables under "Sales" schema from "WideWorldImporters" sample database into single excel file with six sheets:  Sheet 1 - CustomerTransactions Sheet 2 - InvoiceLines Sheet 3 - Invoices Sheet 4 - OrderLines Sheet 5 - Orders Sheet 6 - SpecialDeals While importing we have two scenarios  1) Excel file structure is defined & k ..read more
Visit website
SQL Server Export/Import excel using R script - Part 2
All about SQLServer
by
4y ago
In continuation to my previous post on SQL Server Export/Import excel using R script - Part 1, let us see few other examples in this post. In previous post, I had selected only first six key fields from [WideWorldImporters].[Sales].[Orders] table and passed this as inputdataset (dataframe) to write.xlsx2 function. Instead if I do "SELECT * FROM [WideWorldImporters].[Sales].[Orders]", I got error as shown in below screenshot: This is because data type conversions is getting performed implicitly and some of the data types are not supported when data is passed between R libraries and SQL Serve ..read more
Visit website
SQL Server export adhoc SQL or table to CSV file
All about SQLServer
by
4y ago
In this post, let us see how to export adhoc sql query output or list of tables, views data into csv file. I am going to make use of R script to do this. I have tried this example with SQL Server 2019 & WideWorldImporters sample database. For this approach, I have created a scalar function and Stored procedure. Scalar function - returns a unique table name for a session. This function is required only if adhoc sql query has to be exported to csv file. Stored procedure - Has below three input parameters and writes the output to csv file i)   @ExportPath - path for exporting csv file ..read more
Visit website

Follow All about SQLServer on Feedspot

Continue with Google
OR