Bhagat S
asked on
Problem: numeric field has number stored as text in excel when using SSIS to export data
Using SSIS, I am exectuing a stored procedure and which returns one column which contains values serperated by the | delimiter so the datatype is nvarchar(max).
I can successfully seperate it into columns using the FINDSTRING function, but when I exporting the data to excel (2007-2010 version as an xslx), everything is in the text format.
Excel warns that the cell is a 'number stored as text' on numeric fields.
So I require help on how to ensure numeric fields come out as numbers.
For info the template excel file as headers on the first row that are all text.
Useful info:
Excel Connection String is:
Provider=Microsoft.ACE.OLE DB.12.0;Da ta Source=samplefile.xlsx;Ext ended Properties="EXCEL 12.0 XML;HDR=YES";
Let me know if further info required, Thanks in advance.
I can successfully seperate it into columns using the FINDSTRING function, but when I exporting the data to excel (2007-2010 version as an xslx), everything is in the text format.
Excel warns that the cell is a 'number stored as text' on numeric fields.
So I require help on how to ensure numeric fields come out as numbers.
For info the template excel file as headers on the first row that are all text.
Useful info:
Excel Connection String is:
Provider=Microsoft.ACE.OLE
Let me know if further info required, Thanks in advance.
You'd best convert the texts to number in SQL server. Have a look at this article: https://blog.sqlauthority.com/2007/07/07/sql-server-convert-text-to-numbers-integer-cast-and-convert/
Once in Excel it is easy to convert the text strings to true numbers in a couple of ways.
1) Paste Special
In a blank cell enter a zero and then use Ctrl + C to copy to the clipboard. Highlight the column of text numbers and go to the Paste Special dialogue. In the top section select Values and then in the middle section select Addition. Click OK. This will force excel to re-evaluate the contents of the cells by adding zero to them and will convert them to numbers.
2) Error marker
On the cells with the text string there should be a green triangle error marker and a drop-down next to the cell with a few options. the top of the drop-down will show the error and the first option will be "Convert to Number". If you select all of the data, ensuring that the first cell selected has an error marker, selecting the "Convert to number" on that first cell will convert all of the highlighted cells.
1) Paste Special
In a blank cell enter a zero and then use Ctrl + C to copy to the clipboard. Highlight the column of text numbers and go to the Paste Special dialogue. In the top section select Values and then in the middle section select Addition. Click OK. This will force excel to re-evaluate the contents of the cells by adding zero to them and will convert them to numbers.
2) Error marker
On the cells with the text string there should be a green triangle error marker and a drop-down next to the cell with a few options. the top of the drop-down will show the error and the first option will be "Convert to Number". If you select all of the data, ensuring that the first cell selected has an error marker, selecting the "Convert to number" on that first cell will convert all of the highlighted cells.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.