Solved

SSIS flat file output, date format is manipulated

Posted on 2011-02-19
16
2,151 Views
Last Modified: 2012-06-27
I have a very simple procedure which returns four date fields as YYYY-MM-DD.  These date fields in the table are VARCHAR(255), but convert them to come back as YYYY-MM-DD.

I have a simple data flow task in SSIS, procedure output to csv.  The format of the date fields in the output file come back manipulated, like this:   5/2/1961

If I open the csv in notepad, it is formatted correctly.  It is just in the darned csv, when open in Excel, that the values are re-formatted.

I have to get around this.

I just want SSIS to give me back exactly what I give it, in the procedure output.

Does anybody have any suggestions?
0
Comment
Question by:dbaSQL
  • 10
  • 4
  • 2
16 Comments
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I have tried to add a data conversion between my oledb source and my flat file, just converting it to DT_TEXT.  But the output is not changed.

I would like to try a derived column, but I am unsure how to compile the expression.

My procedure output is this:  1961-05-02
The csv value is this:  5/2/1961

I just want SSIS to leave my value alone, no manipulation, and give back to me what I give it.

Any help on an expression for the derived column?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
The customer specficially requested YYYY-MM-DD format, and they use Excel.
I just can't tell them they're going to have to open it in notepad.  There has to be a way around this.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
If you open the csv in Excel, and right click, format cells, the values can easily be corrected to display as YYYY-MM-DD.

I wonder if there is a means of creating a template file, that SSIS can output to, where the date values will be formatted properly?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
This has nothing to do with SSIS and everything to do with Excel re-formatting the data that it sees as a date.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Yes, I realize that.  It is SSIS, however, that I am using to create the file.  I am hoping that there is a conversion, or another method that I could use to somehow give Excel a value that it will not manipulate.  

OR,  use an Excel template within which the column values are pre-defined, so that the output is as expected.

If I can convert the format of the data in Excel after the file is generated, surely there is a method to pre-define it.  

I have surfed and seen numerous references to others with the same problem.  I just haven't identified the solution.
0
 

Expert Comment

by:Melakh
Comment Utility
For as long as you're using a csv format you will have this issue, the date format is set in windows Regional Settings and Excel will attempt to apply this whenever it opens the csv. There is no formatting metadata stored in the csv format.

Possible solutions (depending on the requirements of your client) are:
Switch to another file format (Excel .xls will allow what you want for example)
Prefix or Suffix the date field with a character or symbol ('A2010-12-31', '2010-12-31#' etc)
Get your client to change their regional settings in Windows
Get your client to use a different application to view the output
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
>>Switch to another file format (Excel .xls will allow what you want for example)
Are you saying I need only to change my flat file destination to .xls, and this problem is gone?
0
 

Expert Comment

by:Melakh
Comment Utility
I'm not in front of SSIS to check right now, however...

You will need to create a template xls file into which to put your output (if this is a regular thing, create a File System Task to create a new copy of a default template each time you run it).

That template file should have the column you plan to insert the date into formatted as per the YYYY-MM-DD format you want to see.

Alternatively, you can set the format to text and convert your date to text in SSIS before outputting it to the Excel file.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 17

Accepted Solution

by:
dbaSQL earned 0 total points
Comment Utility
I got it.  Weirdest of all things, but I got it.

The four date fields in my proc, which are string in the table def, and string in the procedure, and string in the output, yet Excel sees them as dates --- I just added a blank space in front of each of them.

Excel tries to open everything as “General” by default.  If it looks at my data and thinks “I can make a date out of that”, it will.  One little tiny, blank space in front of my date values confuses Excel, and it cannot translate them into dates anymore.

Nothing else has changed, my data flow task to csv Flat File, it's all good.  The values are in the format the customer has requested, and he/she can use Excel to open the files, without issue.

sweet
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>If it looks at my data and thinks “I can make a date out of that”, it will.  <<
Right, that is what I stated, if you are outputting as an CSV, how can it possibly have anything to do with SSIS?  Answer: It can't.

>>One little tiny, blank space in front of my date values confuses Excel, and it cannot translate them into dates anymore.<<
This usually handled by adding an apostrophe, which makes Excel treat it as text.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
As I said in my first post -  

If I open the csv in notepad, it is formatted correctly.  It is just in the darned csv, when open in Excel, that the values are re-formatted.

I understood that it was Excel, I was simply looking for a way around the problem.  And I found it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>If I open the csv in notepad, it is formatted correctly<<
Of course it does.  Again SSIS is not the problem.  What you should be doing is exporting to Excel and not to a CSV.  But then that is just me.

>>I understood that it was Excel<<
Then you should have awarded points appropriately.  From the EE Guidelines:

How do I close a question?
There are five ways:

Accept an Expert's comment as the solution
If an Expert has given you the solution to your question, or has led you to the solution, select this option.

Accept multiple solutions
If several Experts collaborated to provide a solution, use this option and split the points among them.

Accept your own comment as the solution
If you solved your problem with no assistance from any Expert, post your solution and then click the

Accept As Solution button in your own comment. Your points are automatically refunded.
Accept your own comment, and award points to Experts for their assistance
If you answered your own question, but wish to award points to Experts for their attempts to help, use this option.

Delete your question
If you are not getting the kinds of responses that will lead to a solution, you should consider deleting your question and trying again. The Delete Question button is located near the text of your original post.


Now, are you going to tell me that you received "no assistance" ?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
No, what I would say is that I received some very helpful input, but that I solved the problem myself.  Had I changed my dtsx output file to Excel, as Melakh suggested here, ID:34937028, then I would have awarded points differently.

>>> What you should be doing is exporting to Excel and not to a CSV.  But then that is just me.

I chose to export to csv, because that is what the customer requested.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>No, what I would say is that I received some very helpful input, but that I solved the problem myself. <<
Then you clearly have not read/understood the EE Guidelines or have chosen to ignore them.  In any case, I don't care, that is your choice and your decision and you will have to live with it.  But do keep that in mind when you would like someone here to go the extra mile for you, they may think twice.

I wish you the best of luck in your endeavors.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
>>Then you clearly have not read/understood the EE Guidelines or have chosen to ignore them.

While it is possible that I have misunderstood the guidelines, I can assure you, I have not chosen to ignore them.  I find EE to be a tremendous resource, and I am very fortunate to be a member.  The assistance that I have received, and have been able to provide, is key to my own professional development.

Please feel free to let me know how you feel I should have awarded the points on this inquiry.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
Darndest of all things, but I guess Excel is not as smart as it thinks it is.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now