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