Data format after SQL Server Export to Excel
-
Hi Foks,
I have a task and it is to perform a daily export of data from SQL Server 2008R2 into Excel 2010. The Export should include all columns, all rows and tables.
I have successfully performed this using the Import/Export Wizard but for doing on a daily basis (or out-of-hours) it isn't the most efficient method. I have tried using OPENROWSET but there seems to be problems if you use 64-bit Office which we do.
I have also tried it with BCP but that doesn't insert column headers, something that in this case is very important.
I understand that there may be a way with SSIS but I am not sure what that way is.
The perfect solution would include a single workbook with a separate tab for each table. Each tad would include 100% of the data table contents including column headers.
Does anyone have any ideas how I can do this?
Regards;
Kev
-
Try this article for using SSIS
http://code-remind.blogspot.com/2013/03/ssis-sql-query-output-into-excel-tabs.html
-
You can also cheat and set up a SQL 2012 SSRS report with a data source leading to the current data, since SQL 2012 will actually export to an .xlsx, and then either set up a schedule to run itt automatically every day, or just turn it over to the users themselves.
-
when you are using the export wizard , you can actually save that as a package and schedule the same in the agent jon, you just need to know how to schedule an ssis package.
-
Many thanks to everyone for your input!
Because this was quite an important job (and also because I wanted to go on holiday!) I decided to export the data for each table usign the following:
exec master..xp_cmdshell'sqlcmd -S cbr-sqlnode1\erp_module -d erp_rfa_tst -E -s, -W -Q "select * from dbo.Aktion" > h:\erp_rfa_tst\dbo.Aktion.csv';
I created an NTFS share on the folder containing the CSV files and scheduled the export job to run each morning so she had the most recent data as and when she needed it.
Because the user is an experienced Excel Power-User she managed to import the data into Excel without any problems and as a workaround, was a good solution.
SSIS would have been my preferred method but my experience with it is far too limited to produce an off-the-cuff solution.
Once again, many thanks for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply