[T-SQL Excel Openrowset] Hoe schrijf ik een formule weg?

[ikweethetbeter]

De uitdaging
Je kunt in MS SQL Server met OPENROWSET in T-SQL gegevens lezen van en schrijven naar een Excel bestand.

Dat gaat bijvoorbeeld zo:
INSERT INTO OPENROWSET
( 'Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Output\Test.xlsx;',
'SELECT KolomA,KolomB,KolomC FROM [Blad1$]')
SELECT 'Test met getal en formule' AS KolomA
, 200 AS KolomB
, '=B2-25' AS KolomC;

Ik zie in kolom C een tekst verschijnen, in plaats van een formule. En het getal in kolom B is een tekst, ook heel apart.

Hoe krijg ik Excel zover dat er een formule in kolom C komt te staan?

Als ik cel C2 ga aanpassen en op ENTER druk, dan wordt het wel ineens een formule (waarbij de tekst uit B2 als INT wordt behandeld, wat ook de bedoeling is). Een cel editen en op ENTER drukken werkt goed voor een paar cellen, maar ik ga dat niet voor een paar duizend cellen doen. Wie heeft de oplossing?

Relevante software die ik gebruik
MS SQL Server 2016 (13.0.4451 SP1)
MS SSMS 2017 (17.5)

Wat ik al gevonden of geprobeerd heb
Kolommen formatteren, schema.ini gebruiken, een eerste regel met een formule in kolom C handmatig in de file zetten, ...

[RJeee]

Moet je de getallen misschien expliciet casten naar het gewenste datatype?

Betreft formule's, geen idee. Dit zal waarschijnlijk een (n)varchar zijn? Is er wellicht een bepaalde marker die excel verwacht om aan te duiden dat het een formule is?

[Morelleth]

Kan je wellicht een type van wat je "paste" meegeven?
Net als in excel zelf je bij pasten de optie hebt om aan te geven de formule te pasten en niet de opmaak, of juist de tekst ipv de formule.

Of wellicht een commando erna zetten, blad berekenen? shift+f9

Ben overigens totaal niet bekend met MS SQL Server 2016 (13.0.4451 SP1) en
MS SSMS 2017 (17.5), dus weet niet wat daarin mogelijk is.

[ Voor 35% gewijzigd door Morelleth op 22-02-2018 13:27 ]

[ikweethetbeter]

RJeee schreef op donderdag 22 februari 2018 @ 13:22:
Moet je de getallen misschien expliciet casten naar het gewenste datatype?

Dat werkt voor strings, getallen en datums, maar niet voor formules.

Betreft formule's, geen idee. Dit zal waarschijnlijk een (n)varchar zijn? Is er wellicht een bepaalde marker die excel verwacht om aan te duiden dat het een formule is?

Ik heb varchar, nvarchar, char geprobeerd, ook geen cast, dat werkt niet. F9 werkt niet. Op een cel gaan staan, deze editen, en vervolgens op enter drukken werkt wel. Maar als je 1000 rijen hebt weggeschreven, dan moet je dat 1000 keer doen. Niet te doen dus...

[ikweethetbeter]

Morelleth schreef op donderdag 22 februari 2018 @ 13:25:
Of wellicht een commando erna zetten, blad berekenen? shift+f9

Helaas, dat werkt ook niet...

[Gomez12]

OleDB ondersteunt simpelweg niets anders dan datavelden.

Oftewel je kan of je formule laten berekenen op de sql-kant zodat je alleen het antwoord doorstuurt.
Of je kan het doorzetten zoals nu en dan een macro maken die elke cel gaat editten en wegschrijven zodat excel logica het oppikt.

[ikweethetbeter]

@Gomez12
Ja, zoiets zal het zijn. Data wegschrijven in en datatab van een xlsm sheet, en bij het openen wordt de data door een stukje VBA in een presentatietab gekopieerd, waarbij je wel gewoon formules kunt hebben.

Bedankt allemaal!

[Bolukan]

Macrootje schrijven en klaar

code:

set mycell as range
for each mycell in selection.cells
  mycell.FormulaR1C1 = mycell.value
next mycell

[ikweethetbeter]

@Bolukan
Top, bedankt!