[Power Automate]

[Croga]

Mijn vraag

Ik heb een bergje CSV files in folders die ik graag over wil zetten naar een Azure SQL database. In feite een eenvoudig ETL proces.

Op dit moment gebruik ik daar Power Automate Desktop voor. Daar heb ik nou eenmaal ervaring mee (DataFactory of Synapse Analytics voelt een beetje als overkill :-D )

Na een verbinding met de SQL server gemaakt te hebben (met 'Open SQL connection', die gaat goed) en de data opgehaald te hebben (foldernaam, foldernaam, bestandsnaam, kolom 1 en kolom 2) gebruik ik een 'Execute SQL statement' om een INSERT op de database te doen. Op de een of andere manier gaat die niet goed.

Eerst liep hij fout op het timestamp veld. Als ik de string '2020-12-31 18:00:00' probeer te inserten geeft hij een foutmelding op 18. Dus heb ik tijd en datum uit elkaar getrokken waarna hij een foutmelding op de : geeft. Die vervangen door - en nu geeft hij een foutmelding op iets van kolommen. Alle velden in SQL zijn NVARCHAR dus op data type kan het niet mis gaan.
De SQL die ik gebruik in Power Automate:

code:

INSERT INTO <tabel naam> (run, device, capability, value, eventDate, eventTime)
VALUES (
    %sqlRun%,
    %sqlDevice%,
    %sqlCapability%,
    %sqlvalue%,
    %sqlDate%,
    %Replaced%)

Ik heb hem ook geprobeerd zonder vermelding van de kolomnamen maar dat maakt geen verschil.

Wie o wie kan mij helpen?

[dixet]

Net de belangrijkste informatie laat je helaas weg: wat is de foutmelding die je krijgt?
Is het een syntax error? Een datatype conversie error?

Ik gok het laatste. Je gebruikt netjes internationale notatie voor de datum/tijd, dus dat zou sql server moeten snappen. Wat voor datatype hebben de kolommen? Als je '2020-12-31 18:00:00' in een date kolom probeert te stoppen zal het fout gaan. Met een datetime zou het goed moeten gaan

[Croga]

dixet schreef op zaterdag 12 maart 2022 @ 09:37:
Net de belangrijkste informatie laat je helaas weg: wat is de foutmelding die je krijgt?
Is het een syntax error? Een datatype conversie error?

Ik gok het laatste. Je gebruikt netjes internationale notatie voor de datum/tijd, dus dat zou sql server moeten snappen. Wat voor datatype hebben de kolommen? Als je '2020-12-31 18:00:00' in een date kolom probeert te stoppen zal het fout gaan. Met een datetime zou het goed moeten gaan

Goed punt....

Origineel had ik een DATETIME kolom, toen dat fout ging heb ik alles NVARCHAR gemaakt in de hoop in ieder geval gewoon tekst in de kolommen te krijgen.

Ik heb voor de zekerheid nu weer een DATETIME kolom van gemaakt. De tabel ziet er nu zo uit:

code:

CREATE TABLE [Homey].[logs](
    [run] [nvarchar](50) NULL,
    [device] [nvarchar](100) NULL,
    [capability] [nvarchar](100) NULL,
    [value] [nvarchar](100) NULL,
    [Timestamp] [datetime] NULL
)

het SQL script in Power Automate:

code:

INSERT INTO Homey.logs (run, device, capability, value, eventDate, eventTime)
VALUES (
    %sqlRun%,
    %sqlDevice%,
    %sqlCapability%,
    %sqlvalue%,
    %sqlTimestamp%)

Waarbij:

sqlRun = "Initial"
sqlDevice = "Apps"
sqlCapability = "cloud.shelly-cpuusage"
sqlValue = "null"
sqlTimestamp = "2020-12-31 18:00:00"

De foutmelding die er uit komt:
"Error in SQL statement Incorrect syntax near '18'."

Ik krijg die fout ook gewoon echt niet geplaatst. Hij slaat naar mijn idee nergens op dus blijkbaar zit er bij mij ergens een serieuze denkfout :-D

[Onbekend]

Maar je hebt de kolommen eventDate en eventTime.
Maar als waarde geef je een samengevoegde datum-tijd mee?

[Croga]

Onbekend schreef op zaterdag 12 maart 2022 @ 09:54:
Maar je hebt de kolommen eventDate en eventTime.
Maar als waarde geef je een samengevoegde datum-tijd mee?

Ik ben bang dat je twee pogingen door elkaar haalt. In de originele post zie je dat ze uit elkaar getrokken zijn en als pure tekst er in gaan, in mijn reactie van zojuist zie je dat het één DATETIME kolom is waar één waarde naartoe gaat.

(na de post hier onder: Nee, ik haal 2 pogingen door elkaar, excuses :-D)

[ Voor 7% gewijzigd door Croga op 12-03-2022 11:48 ]

[Lustucru]

Croga schreef op zaterdag 12 maart 2022 @ 10:11:
[...]

Ik ben bang dat je twee pogingen door elkaar haalt.

Hij of jij? Je insert komt niet bepaald overeen met je tabel.

[Croga]

Lustucru schreef op zaterdag 12 maart 2022 @ 11:43:
[...]

Hij of jij? Je insert komt niet bepaald overeen met je tabel.

Crap, je hebt gelijk! Wel de velden aangepast, niet de insert regel....
Dat maakt de foutmelding overigens nog vager.... Als ik insert into 6 velden en dan maar 5 waarden mee geef verwacht ik een andere fout....

Ik heb het nu geprobeerd met de juiste 5 velden én zonder velden mee te geven. De foutmelding blijft hetzelfde.

[ Voor 32% gewijzigd door Croga op 12-03-2022 11:48 ]

[Lustucru]

En btw, imho zijn value en timestamp reserved keywords. Dat is ook vragen om syntaxproblemen. En voor zover ik me kan herinneren wil T-SQL een datum-tijd tussen quotes, en die ontbreken nu in sqlTimestamp = "2020-12-31 18:00:00"

[ Voor 46% gewijzigd door Lustucru op 12-03-2022 11:55 ]

[YRH]

Worden je waardes vanuit de csv wel tussen aanhalingstekens gezet? Anders gaat het inderdaad fout met inserten als het geen getallen zijn.

[Anoniem: 36835]

Gooi die insert statement met een setje letterlijke waarden eens in sql management studio. Zo kun je beter debuggen en zijn de foutmeldingen ook beter te begrijpen.

[ Voor 4% gewijzigd door Anoniem: 36835 op 12-03-2022 11:55 ]

[Croga]

Lustucru schreef op zaterdag 12 maart 2022 @ 11:49:
En btw, imho zijn value en timestamp reserved keywords. Dat is ook vragen om syntaxproblemen.

Ook hier weer: Je hebt gelijk. Als rechtgeaarde DevOps man moet ik dit natuurlijk gewoon vanaf het eerste moment productie-rijp maken. De tabel is nu aangepast:

code:

CREATE TABLE [Homey].[logs](
    [logRun] [nvarchar](50) NULL,
    [logDevice] [nvarchar](100) NULL,
    [logCapability] [nvarchar](100) NULL,
    [logValue] [nvarchar](100) NULL,
    [logTimestamp] [datetime] NULL
)

YRH schreef op zaterdag 12 maart 2022 @ 11:51:
Worden je waardes vanuit de csv wel tussen aanhalingstekens gezet? Anders gaat het inderdaad fout met inserten als het geen getallen zijn.

Power Automate gooit de strings gewoon netjes tussen single quotes, voor zover ik in de interface kan zien. Maar wellicht dat het daar fout gaat, ik zal eens kijken of ik dit kan forceren.

[Croga]

Anoniem: 36835 schreef op zaterdag 12 maart 2022 @ 11:55:
Gooi die insert statement met een setje letterlijke waarden eens in sql management studio. Zo kun je beter debuggen en zijn de foutmeldingen ook beter te begrijpen.

Dat is het hem dus. Ik heb hem in Azure Data Studio gegooit en als ik de statement er in gooi zoals ik denk dat Power Automate hem genereerd dan loopt ie zonder problemen. Er gebeurd dus iets in Power Automate wat ik niet verwacht.

[Croga]

YRH schreef op zaterdag 12 maart 2022 @ 11:51:
Worden je waardes vanuit de csv wel tussen aanhalingstekens gezet? Anders gaat het inderdaad fout met inserten als het geen getallen zijn.

Croga schreef op zaterdag 12 maart 2022 @ 11:55:
[...]

Power Automate gooit de strings gewoon netjes tussen single quotes, voor zover ik in de interface kan zien. Maar wellicht dat het daar fout gaat, ik zal eens kijken of ik dit kan forceren.

Yup.... Dat was hem! Blijkbaar voegt Power Automate niet netjes de quotes toe op de manier die ik zou verwachten.

Nog sterker: Als ik 2 single quotes toevoeg (wat je zou verwachten als escape-optie in een string) gaat het nog fouter.

De code die ik in Power Automate heb moeten gebruiken is:

code:

INSERT INTO Homey.logs (run, device, capability, value, eventDate, eventTime)
VALUES (
    '%sqlRun%',
    '%sqlDevice%',
    '%sqlCapability%',
    '%sqlvalue%',
    '%sqlTimestamp%')

Allemaal heel hartelijk dank voor het mee denken! Ik kan weer even verder.
(en heb me ondertussen gerealiseerd dat ik hier echt een andere oplossing voor moet regelen..... Power Automate Desktop is traaaaaaag )

[dixet]

Croga schreef op zaterdag 12 maart 2022 @ 12:01:
[...]


[...]
Allemaal heel hartelijk dank voor het mee denken! Ik kan weer even verder.
(en heb me ondertussen gerealiseerd dat ik hier echt een andere oplossing voor moet regelen..... Power Automate Desktop is traaaaaaag )

Fijn dat het werkt. Als alternatief misschien eens kijken naar bcp (bulk copy), standaard onderdeel van de sql server suite. Dé manier om (grote) tekstbestanden te importeren in je database

En simpel te integreren in powershell of andere scripting talen.

[sig69]

Sql Server heeft ook gewoon een bulk insert optie: https://docs.microsoft.co...sql?view=sql-server-ver15

[Croga]

dixet schreef op zaterdag 12 maart 2022 @ 12:44:
[...]

Fijn dat het werkt. Als alternatief misschien eens kijken naar bcp (bulk copy), standaard onderdeel van de sql server suite. Dé manier om (grote) tekstbestanden te importeren in je database

En simpel te integreren in powershell of andere scripting talen.

Het hele probleem met alle andere opties is dat de veldwaarden niet eenvoudig uit de CSV te halen zijn.

Ik moet dus eerst de Run en het Device uit de foldernamen en de Capability uit de filenaam halen. Daarna zijn Timestamp en Value uit het bestand te halen.

Ik moet gewoon zorgen dat ik de bestanden ergens in Azure kan krijgen dan kan ik er DataFactory op los laten, scheelt behoorlijk in runtime en automatisering. Maar voor het moment kan ik in ieder geval met PowerBI aan de slag. Ofwel: Het was rood, het is groen, nu refactoring