How to copy FixedPath excel file to another path with same name combined with cu
-
How to copy Fixed Path excel file to another path with same name combined with current date and fill data on it ?
I work on python script on sql server 2017
I need to copy fixed path G:\ExportFiles\StudentExport.xlsx' to destination G:\ImportExportExcel with same name combined with current datetime
and after that execute query to fill data on it .
issue on my query below export data from sql server table to excel file static header as column names on table
but i need to get column name on excel from fixed path template excel because header changed multi time dynamically
declare @ExportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @FixedPath NVARCHAR(MAX)='G:\ExportFiles\StudentExport.xlsx'
CREATE TABLE #FinalExportList(TableCount INT IDENTITY(1,1),Cols NVARCHAR(MAX),TableName NVARCHAR(200))
insert into #FinalExportList(TableName,Cols)
values
('dbo.students','TRY_CONVERT(VARCHAR(MAX),StudentId) AS [StudentId], Name')
DECLARE
@TableName NVARCHAR(200)
,@SQL NVARCHAR(MAX) = N''
,@PythonScript NVARCHAR(MAX) = N''
,@ExportFilePath NVARCHAR(MAX) = N''
SELECT @ExportPath = CASE WHEN RIGHT(@ExportPath,1) = '\' THEN @ExportPath ELSE CONCAT(@ExportPath,'\') END
-- Just for testing purpose top 10 records are selected
SELECT @SQL = CONCAT('SELECT TOP 10 ',Cols,' FROM ',TableName,';')
,@TableName = TableName
FROM #FinalExportList
SET @PythonScript = N'
FullFilePath = ExcelFilePath+TableName+".xlsx"
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)'
EXEC sp_execute_external_script
@language = N'Python'
,@script = @PythonScript
,@input_data_1 = @SQL
,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200)'
,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
,@TableName = @TableNameissue on this script export data to excel static but i need header dynamic as i write
because header changed multiple time
attached image descripe my issue
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
"...How to copy Fixed Path excel file to another path with same name combined with current date and fill data on it ?...."
I rename Excel files name to include the date in a DOS Batch file. I don't put in separators. MyFile.XLSX becomes MyFile_20220502_085515.XLSX
Sounds like your request is a bit more complicated, but I don't quite follow what you need.
-
my issue is if i have file already exist
and this file have header
studentid,studentname
then after using query
it become studentid,Name
How to keep header on excel file already exist without change
to do that i try to change
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
to
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1], header=False, startrow= 1,index=False)
but it give me data without header
so what i do please
change will be here
SET @PythonScript = N'import shutil
FullFilePath = ExcelFilePath+"StudentExport.xlsx"
shutil.copy(FixedPath,ExportPath)
InputDataSet.to_excel(FullFilePath,sheet_name=TableName.split(".")[-1],index=False)
'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply