Export a SQL table to CSV file (Comma Separated Value) or any other file is something very common and easy to do, but still I’ve seen people having some common mistakes.
Here is the basic way to export a table to a CSV file.
This process is very similar if you want to export it to any other file extension like Excel, Access, txt, or more…
Step 1: Right click the database name and navigate to Task -> Export Data
Step 2 (the hardest): Click continue.
Step 3: Select SQL Server as datasource, and select your database (in this example we will use AdventureWorks)
Step 4: Select Flat Text File as destination, select a folder and file name.
And here is the tricky part, if you want this CSV file to be opened by Excel in a clean way, you need to add ” as a Text qualifier, it also helps if you have any commas inside your column values like “Chicago, IL.”. Without the Text qualifier it will create a new column after any comma.
Step 5: Select “Write a query to specify data to transfer”
Step 6: Write your query
Step 7: Click continue, unless you want to change any of the default options.
Step 8: Click Next.
You will a message stating that SQL Express edition does not allows to save that task as an SSIS package.
Step 9: Click Finish
Step 10: See results:
Now double click the CSV file created, and it should open in Excel in a clean way: