FOR XML Issue With Numbers Coming Across In Scientific Format
Dec 5, 2006
Program Descritpion:
Our product allows our customers to enter their product catalogs into our ASP.NET pages and we save the data using SQL Server 2005. Customer use GridViews to edit their data and teh data is broken into manageable groupings (called Field Groups) stored as meta-data in SQL Server Tables. Then when a user wants to edit a set of data they choose the Field Group and we dynamically generate a data source and a gridview, bind them together, and our users update their data. A bit more complicated but that's teh gist.
Another thing they can do, and where our problem occurs, is they can print reports or do exports into excel using this data. Since there are so many fields, they use the Field Groups to select which fields they want included so everything needs to be built dynamcially, at runtime, using the Field Group meta-data. Essentially, I want my grid to have these 10 fields.
Problem:
For the export we generate an XML data source using FOR XML in SQL Sprocs. The xml is pulled into a xmldatadocument, trasnformed with an xsl file into an Excel XML Spreadsheet. Our problem is that FOR XML is generating our XML real numbers in scientific format. The xsl "format-number" function does not recognize scientific format and returns NaN (not a number) instead of the value in my spreadsheet. If I leave it blank I get the scientific number but Excel doesn't format it correctly, the cell has an error tag that wants me to choose Number stored as text or convert to a number. I need it to show up on teh Excel form already formatted without that message.
I can't change the DataType of the field in SQL, too many other things depend on it and it needs to be a real. I can't use CONVERT(decimal,fieldName) in SQL because the SQL string is dynmically generated using Dynamic SQL and most of the fields are not real. When we build the Field List we just have field names, we can't check anything to add CONVERT functions to only real fields.
Is there any way to force the XML output to not be scientific for the entire document? Or another function in XSL I'm unaware of (pretty new to xsl)? Or perhaps something I can do in the XML Spreadsheet tags to force the conversion?
View 1 Replies
ADVERTISEMENT
Jun 4, 2015
I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?
View 18 Replies
View Related
Jan 29, 2005
SELECT membername, outputval
case when choice = 0 then outputval else null end as outputval
from MyDatabase
group by membername, outputval
how to format outputval:
if outputval < 40000
format outputval as:
5 - 5.78 - 6.9 - 6,778 - 4,567.8 - 12,456.78 - etc.
if outputval >= 40000
format it as a scientific.
View 1 Replies
View Related
Jul 4, 2007
Hello Nice simple T-SQL question that will no doubt prove totally uncontroversial :) Anyone know the easiest way to get from:123456789to123,456,789in T-SQL? Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way? Two things before you get all upset and start squealing about front ends:1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).2) I'm not too fussed about it just curious. Ta & tra la!
View 2 Replies
View Related
Dec 10, 2007
a number like 1,234,456.82 ?
View 13 Replies
View Related
Feb 22, 2007
Hi all i'm having an issues when i trying to do a report:
i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.
I would really appriciate your Help
Thanks
View 11 Replies
View Related
Jul 12, 2007
if
(datalength
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD])
) = 1)
then
(0 ||
cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
else
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
Goal:
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
View 3 Replies
View Related
Mar 3, 2006
Hi,
I would like to know if there is any trick to convert numbers to csv files. I don't have any control to format the numbers.
Source: Sql server
for example:
select cast( 1 as float)/ cast(201 as float)
Destination:CSV file
the output of the file is 4,9751243E-3
i don't want to have scientific notation on the number. I want the result to be 0,00497512437810945. how can i do it?
I tried to change the type of destination columns to String or numeric but nothing works. what is the influence of changing data type in the destination columns e the output to files?
I tried using convert but with the same results.
Can it be because of local settings or something else?
thanks
prb222
View 4 Replies
View Related
Jan 2, 2007
Hi!
How can I format number on chart's y axis? I wanto to show 1.000 instead of 1000.
Thank you!
View 1 Replies
View Related
Aug 14, 2014
I am trying to find all the records in our database that have the incorrect phone number format and fix them to the correct format.
CREATE TABLE MDR (
SiteName nvarchar(255),
BusinessEmailAddress nvarchar(255),
FirstName nvarchar(255),
LastName nvarchar(255),
JobTitle nvarchar(255),
PersonBusinessPhoneNumber nvarchar(255),
SiteBusinessPhoneNumber nvarchar(255))
[code]....
View 1 Replies
View Related
Nov 17, 2005
/*
Format strings for fractional Numbers and Currency values
Return Values: VARCHAR
Parameters: @n Specifies numeric expression to format.
@sFormat: Specifies one or more format codes that determine how the expression is formatted.
The following table lists the available format codes.
9.000000|00 |09
12.100000|### |12
12345.120000|### ### ###.000|12 345.120
12345.120000|### ### ###.###|12 345.12
12345.120000|$ ### ### ###.000|$ 12 345.120
12345.120000|### ### ###.### $|12 345.12 $
12345.120000|$ ###,###,###.000|$ 12,345.120
12345.120000|### ### ###.000|12 345.120
12345.120000| |12345
1.120000|### ### ###.000|1.120
12.120000|### ### ###.000|12.120
123.120000|### ### ###.000|123.120
1234.120000|### ### ###.000|1 234.120
12345.120000|### ### ###.000|12 345.120
123456.120000|### ### ###.000|123 456.120
1234567.120000|### ### ###.000|1 234 567.120
12345678.120000|### ### ###.000|12 345 678.120
123456789.120000|### ### ###.000|123 456 789.120
1234567890.120000|### ### ###.000|1234 567 890.120
12345678901.120000|### ### ###.000|12345 678 901.120
123456789012.120000|### ### ###.000|123456 789 012.120
*/
CREATE FUNCTION xNumberFormat(@n NUMERIC(38, 4), @sFormat VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN
DECLARE @sRet VARCHAR(255), @i TINYINT, @j INT, @nDec TINYINT, @sNumber VARCHAR(255), @cF CHAR(1), @cR CHAR(1), @sE VARCHAR(255), @sX VARCHAR(255)
SELECT @sE = '', @i = LEN(@sFormat)
WHILE @i > 0 AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sE = SUBSTRING(@sFormat, @i, 1) + @sE, @i = @i -1
SELECT @sFormat = LEFT(@sFormat, @i), @sX = '', @i = 1
WHILE @i < LEN(@sFormat) AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sX = @sX + SUBSTRING(@sFormat, @i, 1), @i = @i +1
SELECT @sFormat = RIGHT(@sFormat, LEN(@sFormat) - @i + 1)
IF @n = 0 AND CHARINDEX('0', @sFormat) = 0 AND @sE = '' AND @sX = '' RETURN ''
SET @nDec = CHARINDEX('.', @sFormat)
IF @nDec > 0 SET @nDec = LEN(@sFormat) - @nDec
SET @sNumber = RTRIM(LTRIM(STR(@n, 255, @nDec)))
IF @nDec > 0 SET @nDec = @nDec + 1
SET @sRet = RIGHT(@sNumber, @nDec)
IF @nDec > 0
BEGIN
SET @i = 1
WHILE RIGHT(@sRet, 1) = '0' AND SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1) = '#' SELECT @sRet = LEFT(@sRet, LEN(@sRet) - 1), @i = @i + 1
IF @sRet = '.' SET @sRet = ''
END
SELECT @i = @nDec + 1, @j = @nDec + 1
WHILE @i <= LEN(@sFormat) AND @j <= LEN(@sNumber)
BEGIN
SELECT @cF = SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1), @cR = SUBSTRING(@sNumber, LEN(@sNumber) - @j + 1, 1)
IF @cF NOT IN ('#', '0')
IF @j = LEN(@sNumber) AND @n < 0 SET @i = @i + 1 ELSE SELECT @sRet = @cF + @sRet, @i = @i + 1
ELSE
SELECT @sRet = @cR + @sRet, @i = @i + 1, @j = @j +1
END
IF @j <= LEN(@sNumber) SET @sRet = LEFT(@sNumber, LEN(@sNumber) - @j + 1) + @sRet
WHILE @i <= LEN(@sFormat) AND SUBSTRING(@sFormat, @i - @j + 1 , 1) = '0' SELECT @sRet = '0' + @sRet, @i = @i + 1
RETURN @sX + @sRet + @sE
END
View 4 Replies
View Related
Oct 11, 2007
Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Any ideas?
Thank you for your help!
cdun2
View 4 Replies
View Related
Jun 15, 2015
I have table with 3 columns: record_id (int), car (varchar(50)), amount (decimal(18,4)).
I created dimension for drillthrough action. Also I set Amount's column FormatString to '##0.00' but when I use Drillthough column Amount values is shown like:
.0000
15.7900
18.4100
Is there any possibility to change that values would be shown as follow:
0,00
15,7900
18,4100
View 4 Replies
View Related
Oct 31, 2006
Hi,
I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.
Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.
Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?
Thanks
View 1 Replies
View Related
Jun 7, 2007
Anyone know why my xls does not have number formats when exporting from a report with a matrix?
I keep having to do 'paste special multiply by 1' on the whole thing.
Ridiculous!
View 1 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jan 22, 2008
I've got some values stored in nvachar(255) field stored by mistake as scientific notation (eg 7.5013e+006 instead of 7501301) and I need to convert and update the field with normal entry, not scientific notation. Is there a way to do that?
View 6 Replies
View Related
Dec 14, 2007
Hi,
I am new to SQL 2005. I have to design schema for scientific data warehouse. Data is available in 2 or more flat data files recorded at 1 sec interval. At Least 2 of the data files have 100+ columns. I am inclined to create a table per data file type. I want to know If this is correct/optimal for me to do?
I don't think I can create normalize tables based on the headers in these Data files.
Primary Objective of this data warehouse is make it available for reporting services and Analysis Services.
Any suggestions or tutorials for me?
Thanks,
Vinod Kushwaha
View 1 Replies
View Related
Jul 23, 2005
HiWe've got some numbers stored as Reals which are returning values inscientific notation that we need rounded down to 3 digits to the rightof the decimal.ie 8.7499999E-2 needs to return 8.75.Round, cast, convert, formatnumber in the dts package all fail.Help!Thanks Moe
View 2 Replies
View Related
Dec 14, 2007
Hi,
I am new to SQL 2005. I have to design schema for scientific data warehouse. Data is available in 2 or more flat data files recorded at 1 sec interval. At Least 2 of the data files have 100+ columns. I am inclined to create a table per data file type. I want to know If this is correct/optimal for me to do?
I don't think I can create normalize tables based on the headers in these Data files.
Primary Objective of this data warehouse is make it available for reporting services and Analysis Services.
Any suggestions or tutorials for me?
Thanks,
Vinod Kushwaha
View 4 Replies
View Related
Oct 16, 2013
We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).
is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.
View 8 Replies
View Related
Jul 20, 2005
I'm trying to find a way to format a FLOAT variable into a varchar inSQL Server 2000 but using CAST/CONVERT I can only get scientificnotation i.e. 1e+006 instead of 1000000 which isn't really what Iwanted.Preferably the varchar would display the number to 2 decimal placesbut I'd settle for integers only as this conversion isn't businesscritical and is a nice to have for background information.Casting to MONEY or NUMERIC before converting to a varchar works finefor most cases but of course runs the risk of arithmetic overflow ifthe FLOAT value is too precise for MONEY/NUMERIC to handle. If anyoneknows of an easy way to test whether overflow will occur and thereforeto know not to convert it then that would be an option.I appreciate SQL Server isn't great at formatting and it would be fareasier in the client code but code this is being performed as adescription of a very simple calculation in a trigger, all stored tothe database on the server side so there's no opportunity for clientintervention.Example code:declare @testFloat floatselect @testFloat = 1000000.12select convert(varchar(100),@testFloat) -- gives 1e+006select cast(@testFloat as varchar(100)) -- gives 1e+006select convert(varchar(100),cast(@testFloat as money)) -- gives1000000.12select @testFloat = 12345678905345633453453624453453524.123select convert(varchar(100),cast(@testFloat as money)) -- givesarithmetic overflow errorselect convert(varchar(100),cast(@testFloat as numeric)) -- givesarithmetic overflow errorAny suggestions welcome...CheersDave
View 3 Replies
View Related
May 15, 2006
Hi
I'm developing a scientific appliation - to forecast the spread of disease. The algorithm has been designed such that different subpopulations reside on separate computers.
The model uses the same set of data repeatedly, and only returns a small signature that reflects the appropriateness of a particular set of parameters.
The model does however change, and I'm looking for a way to roll out this model (which only contains T-SQL, CLR assemblies and service broker code).
I have considered setting up a database on each computer which has the 'data' files which are static, and a separate database which contains the algorithm which can be amended.
Could one then detach the algorithm database making the new version that can then be rolled out (by attaching it to each computer used)?
Is this the best practice? Does anyone have a suggestion as to how to do this?
Go well
Greg
View 1 Replies
View Related
Jan 10, 2008
Hi All,
Am I missing something or is there something odd with float data types. i know that float is not the most precise definition but i came across something really odd today.
first let me define the scenario.
this is sql server 2005, standard edition build 3042.
I have a table defined as
CREATE TABLE [dbo].[ASSET](
[Property_Num] [numeric](10, 0) NOT NULL,
[Accrual_Factor_Val] [float] NULL
)
the accrual_factor_val was updated to a value of 0.00005 then the web service failed because the proc returned 5E-05!
i opened the table, and discovered this is the stored value. is this correct?
thoughts and ideas?
thanks for your input!
View 5 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Apr 25, 2008
Hi ,
I have one problem regarding format of mixed data columns in excel. I am using this query to retieve data from my excel spreadsheet
Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;IMEX=1;IHDR=yes;Database=E: est.xls;HDR=YES',
'SELECT * FROM [original$]')
to retrieve data from excel sheet.
I am able to retreive all data (numaric and text) , but for few cells I am getting wrong values.
like this
15106749310-> 1.51067e+010
70400672657-> 7.04007e+010
48110753143-> 4.81108e+010
registry setting are €œtext and 0€³. I am also using IMEX=1 in connection string.
I have numeric and text data both in same column,
eg:
70400503549
70400672657
KF1080420908
KF1260370908
KF1260880908
KF1260960908
Could you please help me to get the correct value from excel .
thanks in advance ,
View 7 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Oct 6, 2004
Coming from Sybase env. I would like to know the equivalent of sp_showplan. What it does is, it shows the showplan for the query being run by spid, which is passed as a parameter to sp_showplan.
Thanks for your help.
View 3 Replies
View Related
Dec 4, 2006
Does anyone know of a good tool that will convert a Paradox 11 Database to SQL 2005 with ease? Looking around and really unable to find anything...
Thanks in advance
View 1 Replies
View Related
Feb 26, 2004
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.
Regards,
Jacques Matthee.
View 14 Replies
View Related
Jan 6, 2007
I just read the release notes for SQL 2005 SP2 CTP and the 'Select All' on multi-select parameters is coming back EXACTLY as it was in the first release. If this is not the case then I apologise for the rant and request that someone clarify the situation but otherwise ....
I had to change loads of reports because it was removed in SP1, now I'll have to change them again for SP2.
Please Microsoft, stop doing this to us!
At least make it a check box option somewhere. The reasons for it's removal in SP1 still exist, but it is a nice feature to have, so just make it configurable.
I will probably have to delay installing SP2 at various customer sites because it will make my parameter lists look stupid.
This is a shame because there are some useful updates in it.
View 1 Replies
View Related
