none
Exporting data to a csv file and include column names

    Question

  • I am creating a SSIS package which creates a .csv file.  The package works fine except it is not putting the column names as the first row.

    How do I get the column names to also export?

    Thanks,

    ~John

    Tuesday, July 12, 2011 4:27 PM

Answers

  • Ok, What is happening in Excel is that it shows the column value as general where it displays numeral values as numeric while the actual data is stored as text with leading zeroes. Do not worry about this the data in the file is as you want it to be. 

    If in excel you want it as text select the entire column and format the column type as text.

     


    My Blog    |    Ask Me    |     Test your SSIS skills
    • Marked as answer by Eileen Zhao Thursday, July 21, 2011 2:56 PM
    Tuesday, July 12, 2011 9:13 PM

All replies

  • While setting up the destination connection in the File connection manager select the "Column Names in the first Row". This should do the job.
    My Blog    |    Ask Me    |     Test your SSIS skills
    Tuesday, July 12, 2011 4:34 PM
  • When I do that it replaces the column headings with the data from the first row of my table I am trying to export.
    Tuesday, July 12, 2011 4:47 PM
  • Which one is it?  Your first post said you weren't seeing column names in the CSV, and your last post says they're now being replaced with data.

    Checking the "Column Names in First Row" setting will work for you.

    The only reason it won't might be if you have other things going on in that package we're not aware of.  Please describe your package, connection managers, and data flow in more detail.


    Todd McDermid's Blog Talk to me now on
    Tuesday, July 12, 2011 5:33 PM
  • Okay, let me try to explain this better.

    I am not seeing any column names in my outputed file.  In my connection manager without checking the box for "Column Names in First Row" and doing a preview of the data I see my column names and first 200 rows of data.

    When I check that box the column headers go away and I only see the first fow of data in place of the column headings.  Looking at my source connection manager I can see the column names and data as well.

    The package was designed to do the following:

    1. Creates or truncates the table which holds the data in the database.

    2. Loads a text file into the databsae table just created or emptied.

    3. Does some data clean up on the data to remove extra quotes in the file.

    4. Finally it pulls the data from the database and creates a .csv file.  This is where I am having an issue with it not pulling in the column names.  The file generates okay, but there is no header row.

    Hope that helps better explain my problem.

    ~John

     

    Tuesday, July 12, 2011 5:42 PM
  • Rather than checking the header row in the data viewer, execute the package with the settings I mentioned and see the text file. May be put a row count transform to check the count of rows.
    My Blog    |    Ask Me    |     Test your SSIS skills
    Tuesday, July 12, 2011 6:47 PM
  • I made a few changes to my connection manager and it is now working.

    One thing I did notice however, is that for my first field which is a listing of three digit numbers that I am passing in as text it is dropping the leading zero's from all fields.

    Do you know how I can make it now do that?  The field needs to contain the leading zerors so that the data of 001 comes over as 001 and not 1.

    Thanks for all your help, it is much appreciated.

    ~John

    Tuesday, July 12, 2011 7:26 PM
  • does the source and destination different?

    does any value or column name exists in destination file? or that is clean file?


    http://www.rad.pasfu.com
    My Submitted sessions at sqlbits.com
    Tuesday, July 12, 2011 7:28 PM
  • The source is a SQL Server 2005 database table and the destination is a .csv file.

    It creates a new file everytime it is run.

    Looking at the data in the source it does have the leading zeros for that columns data.

    Tuesday, July 12, 2011 7:34 PM
  • could you post an screenshot of your package control flow and data flow schema?
    http://www.rad.pasfu.com
    My Submitted sessions at sqlbits.com
    Tuesday, July 12, 2011 7:43 PM
  • Are you doing any transforms in the flow where you are converting the column to numeric? or any other such transform.
    My Blog    |    Ask Me    |     Test your SSIS skills
    Tuesday, July 12, 2011 7:45 PM
  • No, the data is coming in as text and going out as text. It is being changed by the spreadsheet when I open it.  If I do the ouput to a .txt file the leading zeros are there, but if I open it in Excel it is formatting that column as General instead of text thus removing the leading zeros.
    Tuesday, July 12, 2011 8:40 PM
  • Ok, What is happening in Excel is that it shows the column value as general where it displays numeral values as numeric while the actual data is stored as text with leading zeroes. Do not worry about this the data in the file is as you want it to be. 

    If in excel you want it as text select the entire column and format the column type as text.

     


    My Blog    |    Ask Me    |     Test your SSIS skills
    • Marked as answer by Eileen Zhao Thursday, July 21, 2011 2:56 PM
    Tuesday, July 12, 2011 9:13 PM
  • I have some values that I convert to money in TSQL. Any values that are .00 don't get printed to the output file. So it looks like this.

    In the query results in SSMS: 125.00

    On the flat file destination: 125

    I've tried several methods to get the .00 to print. Suggestions?

    • Merged by Eileen Zhao Friday, December 28, 2012 8:25 AM
    Thursday, December 13, 2012 9:42 PM
  • Make a modification to the Flat File Connector to set its datatype to NUMERIC(n,2)

    Arthur My Blog

    Thursday, December 13, 2012 10:01 PM
  • SQL Server 2012 T-SQL has the new FORMAT command:

    SELECT FORMAT(CONVERT(MONEY,123456.78),'c0','en-US'),     -- $123,457
           FORMAT(CONVERT(MONEY,123456.78),'c2','en-US'),     -- $123,456.78
    	   FORMAT(CONVERT(MONEY,123456.78),'c2','en-GB'),     -- £123,456.78
    	   FORMAT(CONVERT(MONEY,123456.78),'c2','de-DE')      -- 123.456,78 €

    Prior to SS 2012:

    SELECT CONVERT(varchar(40),CONVERT(MONEY,123456.78),1)  -- 123,456.78

    SSIS Import/Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Programming & Database Design Using Microsoft SQL Server 2012



    • Edited by Kalman Toth Wednesday, March 20, 2013 1:22 PM
    Thursday, December 13, 2012 10:09 PM
  • Not seeing how to set it to NUMERIC(n,2).

    I can set it to DataType = numeric[DT_NUMERIC]

    Precision = 18

    Scale = 2

    which doesn't work.

    Thursday, December 13, 2012 10:10 PM
  • Which would be nice but I'm wokring in 2008 R2.
    Thursday, December 13, 2012 10:10 PM
  • Yes, DT_NUMERIC with  precision of 2 and a scale of zero (0).

    If that does not work it must work with DT_R8 no scale-precision there.


    Arthur My Blog

    Thursday, December 13, 2012 10:15 PM
  • Neither of those worked. What the heck? The columns are coming out of the database as money. I even tried converting them to VARCHAR and the FF connection output as string.
    Thursday, December 13, 2012 10:23 PM
  • Put a Data Conversion Transformation before the Flat File Connection and make the datatype conversion there

    Arthur My Blog

    Thursday, December 13, 2012 10:26 PM
  • I had to set it to Numeric Precision 12 Scale 2. That makes NO sense but it works. :S
    • Marked as answer by falcon00 Thursday, December 13, 2012 10:37 PM
    • Unmarked as answer by falcon00 Thursday, December 13, 2012 10:43 PM
    Thursday, December 13, 2012 10:36 PM
  • Oops! Almost there. Now 0.00 shows as .00 with no leading 0. (*sigh*)
    Thursday, December 13, 2012 10:44 PM
  • Hi falcon00,

    Try this in a Derived Column Expression:

    (DT_STR,50,1252)([YourNumericColumn] == 0 ? "0.00" : (DT_STR,50,1252)[YourNumericColumn])

    This may not be ideal but it does the job.

    Hope this helps.

    ~ J.


    Friday, December 14, 2012 3:13 AM
  • This is only a cosmetic action, because 125.00 or 125 is the same...

    If you use (DT_NUMERIC, 10, 2) instead of (DT_DECIMAL) then the text in the CSV will be 125.00 (tested it in 2008 and 2012).
    Only if the value is 0 then it became ",00" in the csv, which can be solved with an expression like:
    (dt_str,10,1252)([Column 0]) == ",00" ? "0.00" : (dt_str,10,1252)([Column 0])


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Friday, December 14, 2012 9:20 AM
  • Grrrr! Something still isn't working right. The 0 values are being printed as .00. Here are my settings in the derived column

    Derived Column Name = BALANCE

    Derived Column = Replace 'BALANCE'

    Expression = (DT_STR,50,1252)([BALANCE] == 0 ? "0.00" : (DT_STR,50,1252)[BALANCE])

    Data Type = numeric[DT_NUMERIC]

    Length = blank

    Precision = 12

    Scale = 2

    Code Page = blank

    Friday, December 14, 2012 4:30 PM
  • My cursory look tells you compare a string value to a number in (DT_STR,50,1252)([BALANCE] == 0

    It should be (DT_STR,50,1252)([BALANCE] == "0" with the quotes, the expression otherwise looks good.


    Arthur My Blog

    Friday, December 14, 2012 5:14 PM
  • Error at Build DP CV SP records [Derived Column [260]]: The data types "DT_NUMERIC" and "DT_WSTR" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

    Friday, December 14, 2012 5:18 PM
  • Look this is pretty straight forward isn't it? The expression says if Balance is 0 then put 0.00 if it's not then just put the value of the column correct? So why is SSIS not doing what the expression says to do?
    Friday, December 14, 2012 5:27 PM
  • I put this

    (DT_STR,50,1252)(@[User::BALANCE]) == "0" ? "0.00" : (DT_STR,50,1252)@[User::BALANCE]

    expression into the SSIS Expression Tester Tool

    whereas BALANCe is of type Double and it works with the following results: getting 0.00 for a value of 0, and 1.23 for a value of 1.23

    I thus suspect you did not use the expression exactly above.

    So in short, if you have (or convert) the variable BALANCE to DT_R8 beforehand - the problem becomes solved with the expression.


    Arthur My Blog

    Friday, December 14, 2012 7:19 PM
  • BALANCE is actually a DB column and not a variable. When I change the expression to your version I get an error in the derived column transformation editor.

    (DT_STR,50,1252)([BALANCE]) == "0" ? "0.00" : (DT_STR,50,1252)[BALANCE]

    With error:

    Error at Build DP CV SP records [Derived Column [260]]: The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "==". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

    What the editor appears to be happy with is:

    (DT_STR,50,1252)(BALANCE == 0 ? "0.00" : (DT_STR,50,1252)BALANCE)

    So BALANCE in the database is of type money. You've seen my settings in the derived column editor. In the FF connector the data type is DT_STR. In the advanced editor of  the OLE DB source it's DT_NUMBERIC precision 18 scale 2.

    Friday, December 14, 2012 8:28 PM
  • convert BALANCE to DT_STR before the Derived Column Transformation

    Arthur My Blog

    Friday, December 14, 2012 8:50 PM
  • I tried doing it with a data conversion transformation and that had no effect. I went into the advanced editor and changed it there and that blew up my derived column. Even if this whole thing started with the column being a string before we changed it to numeric so I would get 123.00 instead of 123 on the output file.
    Friday, December 14, 2012 9:01 PM
  • Something is not right here. let 's start with this BALANCE column in textual format.

    I never said to touch the source metadata, just add a Data Conversion Transformation.

    Once the column is text (DT_STR) you can format it as you wish


    Arthur My Blog

    Friday, December 14, 2012 9:05 PM
  • AH. In the derived column editor I had to change it to Copy of BALANCE. However, this just puts me back at square zero with output being no decimal points and 0.00 values being printed as 0.

    The bottom line that I keep coming back to is that no matter WHAT data type I cast or set that column to the expression is just getting ignored. Even in this case where the value is coming in as a string it still comes out on the file as 0 instead of 0.00 as I specify.

    Friday, December 14, 2012 9:23 PM
  • Also, make sure the data type of the column [Balance] in the Flat File Connection is set to DT_STR instead of numeric.

    ~ J.

    Sunday, December 16, 2012 11:56 PM
  • It is. Hang on I'm going to start a new thread to summarize all of this. I made some changes of the weekend and it's STILL not functioning right.
    Monday, December 17, 2012 3:31 PM
  • Monday, December 17, 2012 5:12 PM
  • Did you get an answer to your original question? I am also having the same issue. Checking the 'first row includes column names' doesnt work as that data is from the data set. how did you do it?

    I ended up doing a union all to get the first row as column names.

    cheers


    Wednesday, March 20, 2013 10:11 AM
  • I know this is old but do you remember what you did to get this to work?  Checking "Column names in the first data row" does not work.

    Thanks

    Monday, January 27, 2014 7:15 PM