Hi,
I am creating data sets to export to Microsoft Excel. There is one field in particular that is causing a lot of pain. The is "Posting Period". The Excel file is a CSV and will be used to upload to an ERP system. This system requires posting periods to be in the following format:
eg. Apr 2019 - in other words, three characters, a space then the year as a four digit numeric.
Well, as soon as this value is pasted into Excel from a results set in SSMS, it becomes Mar-19 and is now a date-type format! I have tried formatting the field in SQL as varchar or char but this makes no difference. To achieve what I want in Excel, you have to put a single apostrophe at the beginning. eg. 'Mar 2019. If you type that into Excel, the apostrophe disappears. Lovely. I have managed to get this exact value from SQL but when that is pasted in, hey presto! it becomes 'Mar 2019. What's going on???
Can anyone help me on this?
many thanks
You're fighting excel, excel "samples" the rows and decides what the type should be , the ' forces excel to view it as text
To see this, try the following...
In SSMS
select 'Apr 2019'
copy the results row
Paste to excel
Excel displays 'Apr-19'
Format cell as "Text"
Excel displays 43556
Paste again into same cell
Excel now displays 'Apr 2019'
If you now save as csv then the 'Apr 2019' will be respected
Thanks Uberbloke. The issue is that this process needs to be done on an industrial scale which means that the multi-stage operation you suggest, whilst it may work, is not really viable.
You are correct in saying that I am "fighting Excel" (Why? lol) however, there absolutely must be a way of presenting Excel with a value, pasted into a cell in whatever way, that it likes. I have added a " ' " to the beginning of the string by using this syntax in SQL ' ' ' '. Whilst this displays a singe quote in the SSMS results, I can see that behind the scenes in Excel, the double-single apostrophe has come in as a " which it then interprets as a " ' ". Goodness me!
I disagree with @uberbloke - you are not fighting Excel - you are actually fighting yourself. A CSV file is not an Excel file - and the fact that Excel is causing issues is really a non-issue.
If you open the CSV file in Notepad - or any other text editor - you will see that the value in that field in the file is correctly formatted as 'MMM yyyy'. Sending that CSV file to your ERP system will work with those values as expected.
The next step will be to determine if the values need to be quoted - which will only apply to those fields in the file that could contain a comma and how you are actually generating the file.
1 Like
.When you open csv in excel, it takes over and does what it think is proper date data formatting.
Just ingest its content into erp system in dev or qa and it should work.what is the data type the erp system target table column where this "date" data will be inserted into?
Re: reading the post and the comments I think the crucial phrase from the OP is "...as soon as this value is pasted into Excel from a results set in SSMS"
You need to bypass Excel
In SSMS, Rather than paste, why can you not do "File, Save Results As..." and select CSV?