x

Export to Excel or CSV using SSIS

I'm trying to export data from SQL to Excel or even a CSV. The issue I am having is that if I export to EXCEL the DELETE option is not allowed to overwrite the data in the worksheet. The other issue I'm having is when I extract the data in a CSV the comma found in the spreadsheet shifts the data due to the delimiter being comma(,). I have tried using Tab but this puts all my columns and data into one BIG column. I really need the commas in the data! I'm not sure what else to try. I think I tried everything. Does anyone know of a workaround or how I should handle this? Could I maybe delete the file and recreate using SSIS as a workaround?
more ▼

asked May 06, 2011 at 09:28 AM in Default

Noonies gravatar image

Noonies
1.2k 60 64 65

Are you using the flat file connection manager? Instead of having a pre-made CSV, let SSIS generate out a CSV for you with the data. It should be an extremely simple task to export out data to CSV using SSIS. If this is a one time method, then just run your query and save to CSV :-) Though I'm assuming this will be a recurring process.
May 06, 2011 at 09:54 AM Slick84
Yes this will be a process that will occur every Monday. I am using the Flat File Connection and I tried Excel also. The extraction to CSV is definitely easy but the comma in my data is causing the issue. It shifts all columns on every comma seen. I need a work around on this piece.
May 06, 2011 at 10:00 AM Noonies
As promised, here is the blog post with step by step. Using an Excel Destination in SSIS with x64
May 17, 2011 at 03:49 AM Tim
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can't have a comma in data in a CSV file. Otherwise the comma will shift the data. If you need a common delimited file try using some other separator. I know you mentioned TAB put it all in one big column. Can you explain? You should be able to open a tab separated file with EXCEL with no issue, you have to specify that it is tab separated when opening. If the requirement is to get the data from SQL into EXCEL then just chose an EXCEL destination, then comma's won't matter at all. The way to get around the appending of data into EXCEL is to have a SQL Task where you drop the worksheet in the excel destination and then another SQL TASK to create the worksheet. Then you run your dataflow task.

Explanation
In your Control Flow a SQL TASK. In the SQL Statement chose EXCEL, in Connection chose your Excel Connection Manager, SQL Source type is Direct Input, SQLStatement = 'DROP TABLE 'NAME OF YOUR WORKSHEET' GO. Add another SQL TASK, same items as above but your SQLStatement will be a create table statement to recreate the worksheet. Some thing like CREATE TABLE 'NAME OF YOUR WORKSHEET'('NAME' VARCHAR(16), 'ACCOUNT'DECMIAL(9,0)) GO.

Connect your first SQL TASK to your second, then the second to your control flow. The create table statement should be the same one that is created when you create your Excel destination.

Let me know if you have trouble doing this and I will either find you some links or try to post some screen shots.
more ▼

answered May 06, 2011 at 10:23 AM

Tim gravatar image

Tim
36.4k 38 41 139

Perhaps I am misunderstanding what you are trying to say. CSV files will happily support commas in the data field as long as their is either some sort of text quoting or character escaping is allowed. The most common version of this is using double quotes " as a text signifier and wrapping any text fields that might contact a comma in the quoting.
May 06, 2011 at 11:35 AM TimothyAWiseman
Yes text qualifiers work great if you have end users who can work Excel well enough to specify how the file should be sorted and opened. What I was gathering from @noonies is what was needed was a true Excel file. When it was attempted to just pipe it out as CSV to be opened in Excel with no effort the data was being shifted. I haven't tried it with recent versions of Excel but can you create a CSV with text qualifiers and Excel open it properly without having to go through a wizard of sorts to specify how the columns should be separated?
May 06, 2011 at 11:41 AM Tim
@TRAD - Oh okay I was trying to use DELETE FROM . I didn't think to use DROP and then a task for CREATE. Let me see if this will work for me. Thank you both for responding. I will let you know if this works.
May 06, 2011 at 12:00 PM Noonies
@TRAD - No go! I get the following error: [Execute SQL Task] Error: Executing the query ""DROP TABLE 'Sheet1$' GO"" failed with the following error: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
May 06, 2011 at 12:33 PM Noonies
Is Donations the name of the worksheet that you created when you added the Excel destination object? One of the things I have learned is you can make changes easily to the column names, or worksheet and the package work properly. Do this, create a new excel file, and capture the create statement that SSIS uses to create the worksheet. Paste that into your SQL TASK for creating the worksheet, and also in the first SQL Task to DROP the table. Then change the create to drop and remove all the table creation portion of the script in your drop task. Now try to debug your package.
May 06, 2011 at 01:04 PM Tim
(comments are locked)
10|1200 characters needed characters left

if this is a recurring job follow these steps:

step 1: Create SSIS package based on your requirements and don't execute the package yet.

step 2: Go to the path where you have saved the excel file and save a copy as ex: XYZtemplate.xls. you need to have XYZ.xls and XYZtemplate.xls files at that location.

step 3: create a batch file as below at the same location where you have saved the excel files.

DEL C:\MyDocs\ABC\XYZ.xls

COPY C:\MyDocs\ABC\XYZtemplate.xls C:\MyDocs\ABC\XYZ.xls

step 4: Finally add an execute process task as first step in your package then the rest.

step 5: In the execute process task editor go to the process and in the executable call the above batch file.

C:\MyDocs\ABC\XYZ.bat
more ▼

answered May 19, 2011 at 12:12 PM

suhas gravatar image

suhas
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x941

asked: May 06, 2011 at 09:28 AM

Seen: 6308 times

Last Updated: May 06, 2011 at 09:28 AM