Link to home
Start Free TrialLog in
Avatar of Bhagat S
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.OLEDB.12.0;Data Source=samplefile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

Let me know if further info required, Thanks in advance.
Avatar of Jan Karel Pieterse
Jan Karel Pieterse
Flag of Netherlands image

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.
ASKER CERTIFIED SOLUTION
Avatar of Bhagat S
Bhagat S

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial