Question Save Query Results into Excel and Automatically run it

[Anonymous]

I have a very simple query

Select * from Employees
where company = 'Company A'
order by firstName

I want to run this query to run automatically on a daily basis and save the results in excel. Is their any way I can achieve this purpose?
I tried using SQL server jobs but the output excel file was corrupted and could not be opened. I choose the target file as an excel file which was already not present in the system.After the job executes successfully, it creates the excel but I couldn't open it, it says the file is corrupted.

I tried creating a package using Export Data option from SQL Server Management Studio. I followed steps from here -
Simple way to export data from SQL Server

But I am extremely confused on how to schedule the package. I don't even know if I created the package correctly. I tried several times but every time I got different errors. Is there any simple and easy way to achieve this purpose?
Thank you for your guidance.

 

[Skydiver]

After the job executes successfully, it creates the excel but I couldn't open it, it says the file is corrupted.

Can you show us the details of this job? Are you sure that you are actually exporting to an Excel file? Or are you actually exporting a text file and giving it a .XLS or .XLSX file extension? It's only the latter that I can see as a sure fire way of getting a "corrupt" Excel file -- because it's not an Excel file. It's a text file.

Anyway, I'm still not seeing how this is a C# question.

 

[Anonymous]

Can you show us the details of this job? Are you sure that you are actually exporting to an Excel file? Or are you actually exporting a text file and giving it a .XLS or .XLSX file extension? It's only the latter that I can see as a sure fire way of getting a "corrupt" Excel file -- because it's not an Excel file. It's a text file.

Anyway, I'm still not seeing how this is a C# question.

I was manually specifying the xls extension. How can I automatically save it for excel then? I am so sorry it is so confusing for me and its almost 10 hours non stop of me trying to figure this out.

 

[Skydiver]

Change the file extension of the current file you have to .TXT. Look inside to see the values are comma delimited. If so, then use .CSV as the file extension instead of .XLS. Excel knows how to read .CSV files.

 

[Anonymous]

Change the file extension of the current file you have to .TXT. Look inside to see the values are comma delimited. If so, then use .CSV as the file extension instead of .XLS. Excel knows how to read .CSV files.

I tried .txt but the output coming is not in proper format....

a lot of space, lines, then data, then space then data...

 

[Skydiver]

Then you cannot export as .CSV using that approach it seems.

 

[Skydiver]

You could just write a simple C# ADO.NET console app to query and write to a CSV. For about 10 times more effort you could write to a .XLSX using OpenXML. For about 5 times more effort (compared to CSV) you could write to .XLSX using Excel object model interop, but will require that you have Excel on the machine and that you are not doing this unattended on a server. For about 2-3 times more effort (compared to CSV), you can get a 3rd party library like ASPOSE to write to a .XLSX without the compilations of trying to run Excel on a server.