How to copy FixedPath excel file to another path with same name combined with cu

Question

Post reply

How to copy FixedPath excel file to another path with same name combined with cu

ahmed_elbarbary.2010

Hall of Fame

Points: 3109

April 29, 2022 at 11:46 pm

#4024496

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 = @TableName

issue 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

fixed file cop

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80305 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

homebrew01 SSC Guru Points: 55519 More actions · Answer 2

"...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.

This reply was modified 1 year ago by homebrew01.

ahmed_elbarbary.2010 Hall of Fame Points: 3109 More actions · Answer 3

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)
'