Solved

SSIS flat file output, date format is manipulated

Posted on 2011-02-19
16
2,246 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
ID: 34935165
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
ID: 34935206
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
ID: 34935494
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34935654
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
ID: 34935664
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
ID: 34937028
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
ID: 34937294
>>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
ID: 34937435
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
 
LVL 17

Accepted Solution

by:
dbaSQL earned 0 total points
ID: 34937445
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
ID: 34939036
>>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
ID: 34940067
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
ID: 34940125
>>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
ID: 34940157
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
ID: 34942796
>>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
ID: 34943420
>>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
ID: 34977923
Darndest of all things, but I guess Excel is not as smart as it thinks it is.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach 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 shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

823 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