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 Server.
To workaround this, we can ignore the fields from exporting that has data types that are
not supported and also doesn't make any sense exporting (for example field with image data type)
To workaround the issue of data types that are poorly converted when data is passed between R and SQL Server, we can make use of TRY_CONVERT(VARCHAR(MAX),field name)
So in below example, I am going to export all the tables data (non temporal tables) that falls under "Sales" schema from WideWorldImporters sample database.
USE [WideWorldImporters] GO SET NOCOUNT ON DROP TABLE IF EXISTS #ExportTablesList, #FinalExportList SELECT CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME)) Columns -- To cover poor data type conversions ,T.name TableName ,DENSE_RANK()OVER(ORDER BY T.name) TableCount ,TY.name DataType INTO #ExportTablesList FROM Sys.tables T JOIN sys.columns C ON T.object_id = C.object_id JOIN sys.types TY ON C.[user_type_id] = TY.[user_type_id] WHERE Schema_name(T.schema_id) = 'Sales' AND temporal_type = 0 --NON_TEMPORAL_TABLE AND TY.name <> 'image' -- Ignore the datatypes that are not required to be exported SELECT TableName,MAX(TableCount) TableCount , STUFF( ( SELECT ', ' + C.Columns From #ExportTablesList As C WHERE C.TableName = T.TableName FOR XML PATH('') ), 1, 2, '') AS Columns INTO #FinalExportList From #ExportTablesList As T GROUP BY TableName DECLARE @I INT = 1 ,@TableName NVARCHAR(200) ,@SQL NVARCHAR(MAX) = N'' ,@RScript NVARCHAR(MAX) = N'' WHILE @I <= (SELECT MAX(TableCount) FROM #FinalExportList) BEGIN SELECT @SQL = CONCAT('SELECT TOP 10 ',Columns,' FROM [WideWorldImporters].[Sales].',QUOTENAME(TableName),';') ,@TableName = TableName FROM #FinalExportList WHERE TableCount = @I SET @RScript = CONCAT(' Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/") library(rJava); library("xlsx"); write.xlsx2(InputDataSet, file = "H:/Temp/Blog/ImportExportExcel/Sales.xlsx", sheetName= "',@TableName,'", col.names=TRUE, append=TRUE) ') EXEC sp_execute_external_script @language = N'R' ,@script = @RScript ,@input_data_1 = @SQL SET @I = @I + 1 END
In above example, I have tried to export into single excel file under multiple sheets with sheet name as table name. This is achieved by setting argument "append=TRUE" in write.xlsx2 function.
While exporting multiple tables data, if we need any specific configurations to be done say like export top 100 records, order by key field or filter on particular field - all these can be done by tweaking the SQL that is built dynamically (like I have done in above example).
Similarly it can also be exported to different excel files instead of different tabs within single sheet with dynamic file names by tweaking the append & file arguments in write.xlsx2 function. Below script does this:
USE [WideWorldImporters] GO SET NOCOUNT ON DROP TABLE IF EXISTS #ExportTablesList, #FinalExportList SELECT CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,') AS ',QUOTENAME(C.NAME)) Columns -- To cover poor data type conversions ,T.name TableName ,DENSE_RANK()OVER(ORDER BY T.name) TableCount ,TY.name DataType INTO #ExportTablesList FROM Sys.tables T JOIN sys.columns C ON T.object_id = C.object_id JOIN sys.types TY ON C.[user_type_id] = TY.[user_type_id] WHERE Schema_name(T.schema_id) = 'Sales' AND temporal_type = 0 --NON_TEMPORAL_TABLE AND TY.name <> 'image' -- Ignore the datatypes that are not required to be exported SELECT TableName,MAX(TableCount) TableCount , STUFF( ( SELECT ', ' + C.Columns From #ExportTablesList As C WHERE C.TableName = T.TableName FOR XML PATH('') ), 1, 2, '') AS Columns INTO #FinalExportList From #ExportTablesList As T GROUP BY TableName DECLARE @I INT = 1 ,@TableName NVARCHAR(200) ,@SQL NVARCHAR(MAX) = N'' ,@RScript NVARCHAR(MAX) = N'' WHILE @I <= (SELECT MAX(TableCount) FROM #FinalExportList) BEGIN SELECT @SQL = CONCAT('SELECT TOP 10 ',Columns,' FROM [WideWorldImporters].[Sales].',QUOTENAME(TableName),';') ,@TableName = TableName FROM #FinalExportList WHERE TableCount = @I SET @RScript = CONCAT(' Sys.setenv(JAVA_HOME="C:/Program Files/Java/jdk-11.0.1/") library(rJava); library("xlsx"); write.xlsx2(InputDataSet, file = "H:/Temp/Blog/ImportExportExcel/',@TableName,'.xlsx", sheetName= "',@TableName,'", col.names=TRUE, append=FALSE) ') EXEC sp_execute_external_script @language = N'R' ,@script = @RScript ,@input_data_1 = @SQL SET @I = @I + 1 END
More information on this topic, to be continued in part 3 ...
No comments:
Post a Comment