February 1, 2011 at 8:18 am
I do a export from a query into an Excel file. This works fine, however the export of numeric values are inserted as text and decimal values are inserted with a dot instead of a comma (10.99 instead of 10,99)
Example:
INSERT INTO OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\site_web\iec\inc\MyTest.xlsx;',
'SELECT [Nom],[Montant total] FROM [MyTest$]')
SELECT
T_Paiement.nom,
T_Paiement.montant
FROM
T_Paiement
Surely there must be a way to export numeric values properly. I tried to pre-format my Spreadsheet (with numeric columns) but that corrupted the file after insertion, I couldn’t open it anymore in Excel
February 1, 2011 at 8:48 am
what are the data types of the columns.
have you tried the convert function
USE AdventureWorks2008R2;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO
February 1, 2011 at 9:27 am
Yes I have however it gives the same result. a dot instead of a comma (10.59 instead of desired 10,59)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply