1

I am new to SQL and I'd like to export the result of my query as a .csv file. This question was answered with a point and click solution.

I wish to automate this query such that it runs every day at 6 am. I come from an R background, so I'm wondering if there's a command similar to write.csv. Is it possible to simply write a command in the last line of the query to tell the computer to write the result as a .csv file in a given folder?

Arturo Sbr
  • 5,567
  • 4
  • 38
  • 76

1 Answers1

1

You can use the code below, but just make sure you already have a version of your csv file saved to your documents/desktop/etc.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [yourfile.csv]')
SELECT Field1, Field2, Field3, Field 4, Field 5 FROM DatabaseName

Edit: If you are having an issue with Microsoft.ACE.OLEDB.12.0 not being registered check out below link.

https://blog.sqlauthority.com/2015/06/24/sql-server-fix-export-error-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

Example:

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Users\My Documents;HDR=YES;FMT=Delimited','SELECT * FROM [EmployeeInfo.csv]')
        SELECT
FirstName
,LastName
,DOB
 FROM Employees
Gutzy
  • 82
  • 11
  • Thank you @Gutzy. What do you mean `SELECT {fields} FROM DatabaseName` ? Should I be posting the command at the top of the query and treating the SELECT (and all other sections) as I normally would? – Arturo Sbr Nov 29 '18 at 22:12
  • Oh, and how do I specify the path to [yourfile.csv]? – Arturo Sbr Nov 29 '18 at 22:23
  • 1
    Hi @ArturoSbr, please refer to example I added to my original comment. You can specify your path in the Database= in the Insert Into statement. Field is whatever you want it to be in your query. In my example I am pulling some fields from the Employee table in my database. Hope this helps! – Gutzy Nov 29 '18 at 23:28