CODEWITHSUNDEEP
sqlexcelstored-procedurescasting
BR1973
BR1973

Reputation: 11

Converting text to numeric SQL Server Stored Procedure

I have a stored procedure that calculates quantity sold and coverts into hundred weight (Decimal data type to 2 places). This is used for a third party forecasting application that requires an excel file. Problem is when i copy the data and paste into excel, excel reads it as text and not numeric. The end user in not very efficient in excel, so i want to ensure when the data is pasted, it has a numeric value.

Thank you

SELECT  RTRIM(dbo.tblSteelItemMasterNew.PreactorPlanningFamily) AS StockCode, SUM(CAST(dbo.fncCalculateQuantity(dbo.tblSteelItemMasterNew.ProductCode, 
                   dbo.tblDailySalesTransactions.QuantityShipped, dbo.tblDailySalesTransactions.UnitOfMeasure) * 2.2046 / 100 AS Decimal(19, 2))) AS Quantity, DATEPART(m, 
                   dbo.tblDailySalesTransactions.GLDate) AS Month, DATEPART(yyyy, dbo.tblDailySalesTransactions.GLDate) AS Year
FROM      dbo.tblSteelItemMasterNew INNER JOIN
                   dbo.tblDailySalesTransactions ON dbo.tblSteelItemMasterNew.ProductCode = dbo.tblDailySalesTransactions.ProductCode INNER JOIN
                   dbo.AddressBook ON dbo.tblDailySalesTransactions.ShipToAddressNumber = dbo.AddressBook.AddressNumber INNER JOIN
                   dbo.tblItemMasterAllProducts ON dbo.tblSteelItemMasterNew.ShortItemNumber = dbo.tblItemMasterAllProducts.ShortItemNumber INNER JOIN
                   dbo.tblSteelItemMasterManufacturingProperties ON dbo.tblSteelItemMasterNew.ProductCode = dbo.tblSteelItemMasterManufacturingProperties.ProductCode
WHERE   (dbo.tblDailySalesTransactions.OrderType <> N'ST') AND (NOT (dbo.tblDailySalesTransactions.OrderType = N'CT')) AND (NOT (dbo.tblDailySalesTransactions.OrderType = N'bu')) AND 
                   (NOT (dbo.tblDailySalesTransactions.OrderType = N'ca')) AND (NOT (dbo.tblDailySalesTransactions.OrderType = N'bm')) AND (NOT (dbo.tblDailySalesTransactions.OrderType = N'ba')) AND
                    (NOT (dbo.tblDailySalesTransactions.OrderType = N'S6')) AND (dbo.tblDailySalesTransactions.OrderType <> N'SW') AND (dbo.tblItemMasterAllProducts.SalesClass < N'600') AND 
                   (dbo.tblSteelItemMasterNew.[Preactor Planning] = 1)
GROUP BY DATEPART(yyyy, dbo.tblDailySalesTransactions.GLDate), DATEPART(m, dbo.tblDailySalesTransactions.GLDate), RTRIM(dbo.tblSteelItemMasterNew.PreactorPlanningFamily)
HAVING  (DATEPART(m, dbo.tblDailySalesTransactions.GLDate) = @Month) AND (DATEPART(yyyy, dbo.tblDailySalesTransactions.GLDate) = @Year) AND 
                   (NOT (RTRIM(dbo.tblSteelItemMasterNew.PreactorPlanningFamily) IS NULL))
ORDER BY RTRIM(dbo.tblSteelItemMasterNew.PreactorPlanningFamily

Upvotes: 1

Views: 96

Answers (1)

EverCode
EverCode

Reputation: 66

I'm 99% that you can't do this. And this is not an SQL issue. I once had a similar problem and the team concluded Excel would always paste values as generic text.

The Only way this could be done is by writing an app that processes the excel and changes it. Something like:

IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.Worksheets[0].Cells;
cells["A:A"].NumberFormat = "0.00"

Else, you could prepare an excel template that copies the values you want into in a cell that is formatted as number.

Upvotes: 1

Related Questions