יצוא לאקסל מהSQL דרך הקוד

הירשם עכשיו!
  • הוסף לסימניות
  • #1
יש כאן למישהי קוד שעובד?
חפרתי באינטרנט ושום דבר לא הולך..
 
  • הוסף לסימניות
  • #6
לא
SQL SERVER
אבל אם ישלך משהו שעובד אז תשלחי ל י
 
  • הוסף לסימניות
  • #9
stackoverflow.com

Export SQL query data to Excel

I have a query that returns a very large data set. I cannot copy and paste it into Excel which I usually do. I have been doing some research on how to export directly to an Excel sheet. I am runnin...
stackoverflow.com stackoverflow.com

stackoverflow.com

T-SQL: Export to new Excel file

I have a script that does various things and the end result is one large table. I was wondering how I could export this final table to a new Excel file (with column headers as well). I would need ...
stackoverflow.com stackoverflow.com

solutioncenter.apexsql.com

SQL DevOps tools | ApexSQL

SQL DevOps tools required to drive an automated DevOps workflow
solutioncenter.apexsql.com
 
  • הוסף לסימניות
  • #13
---EXEC bcp--------------------------------------------------------------------
CREATE procedure [dbo].[bcp_export_csv]
(
@query varchar(1000),@fileName varchar(500)
)
AS
BEGIN
DECLARE @bcpCommand varchar(3000)
DECLARE @SelectQuery varchar(2000)
DECLARE @OutPath varchar(2000)
DECLARE @FileName varchar(2000)
DECLARE @format varchar(2000)

SET @outPath =' queryout "C:\Temp\' + @fileName+ '-' + REPLACE(CONVERT(char(10),GETDATE(),111),'/','-')+'.csv"'
SET @format=' -C1255 -c -t, -T'-- -S'+ convert(varchar,@@ServerName)
SET @bcpCommand = 'bcp'+ @query + @outPath + @format
EXEC master..xp_cmdshell @bcpCommand
END


-----------------------------------
CREATE procedure [dbo].[export_report] ()
AS
BEGIN
DECLARE @SelectQuery varchar(2000)
SET @SelectQuery =' "exec db1.. TABLE_NAME '''+column_name+''','''+ column_name2+'''" '
exec bcp_export_csv_report @SelectQuery,@file_name
-----------------------------------

​
 
  • הוסף לסימניות
  • #16
נכתב ע"י אמא של תמר:
תוכלי להדפיס לי את :
@bcpCommand
וכן - מה השגיאה שאת מקבלת ?​
לחצ/י כדי להרחיב...

CREATE procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec xp_cmdshell ''del '+@data_file+''''
exec(@sql)

GO
 
  • הוסף לסימניות
  • #17
נכתב ע"י אמא של תמר:
תוכלי להדפיס לי את :
@bcpCommand
וכן - מה השגיאה שאת מקבלת ?​
לחצ/י כדי להרחיב...
השגיאה:
1595751582155.png
 
  • הוסף לסימניות
  • #19
תיקון:
הוא מייצא לאקסל דרך הפרוצדורה הזאת:

CREATE procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec xp_cmdshell ''del '+@data_file+''''
exec(@sql)

GO


אך ללא נתונים!
 

פרוגבוט

תוכן שיווקי
פרסומת
עליך להתחבר או להירשם כדי להשיב כאן.
למעלה