SQL

1. Introduktion #

SQL kan bruges i mange RPA-sammenhænge, både til at hente data, og eventuelt logge data fra kørsler. Herunder findes ressourcer til at studere det yderligere.

Navn	Note	Link
Kursus	Gratis kursus med opgaver til SQL	SQL Tutorial (w3schools.com)
Sandbox miljø	Et øve værktøj med et miljø man kan teste i	SQL Tryit Editor v1.6 (w3schools.com)
SQL Keywords	Keywords	SQL Keywords Reference (w3schools.com)
SQL Constraints	Constraints	https://www.w3schools.com/sql/sql_constraints.asp
SQL Wildcards	Wildcards	https://www.w3schools.com/sql/sql_wildcards.asp
SQL Data Types	Data Types	https://www.w3schools.com/sql/sql_datatypes.asp

2. ACEOLEDB – Brug SQL-statements til Excel og CSV-filer #

I større datamængder i Excel med 10000+ rækker, kan det være nemmere at bruge SQL-statements til at tilgå et Excel-ark som en database, og bruge SQL Query til at hive data ud. Det kan også være et godt alternativt, hvis man skal hive specifikke data ud, i stedet for at løbe hver enkelt række igennem slavisk.

Navn	Note	Link
Open SQL connection to an Excel file	Gennemgang af hvordan man kan bruge denne funktionalitet i Power Automate Desktop.	https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel
Access Database Engine	Link til download, vælg samme bit-version som installerede office-pakke bruger.	https://www.microsoft.com/en-us/download/details.aspx?id=54920
32-bit / 64-bit blandet installationer	I tilfælde hvor man bruger 32-bit office og eksempelvis Power Automate (kører 64-bit) er man nødt til at gøre følgende, for at kunne bruge løsningen. Noter at man efterfølgende kan tilføje kilderne fra ODBC 32-bit, og tilføjer både 32/64 bit.	https://learn.microsoft.com/en-us/troubleshoot/power-platform/power-automate/desktop-flows/cannot-connect-access-database-engine-ole-db?source=recommendations
Office Click2Run	Troubleshooting og eventuel løsning på problem.	https://learn.microsoft.com/en-us/office/troubleshoot/access/cannot-use-odbc-or-oledb
schema.ini	Introduktion til schema.ini filer, og hvordan disse kan sættes op	https://learn.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-ver15
Extended properties HDR og IMEX	Benytter man både HDR og IMEX i sin Extended Propterties, kan det skabe problemer. Det er forklaret i linket, med eventuel løsning.	https://stackoverflow.com/questions/10102149/what-is-imex-within-oledb-connection-strings

2.1 Installation af ODBC-kilder #

Såfremt man ikke har en Microsoft Access installeret eller har denne mulighed, kan man hente en Access Database Engine installation i stedet. Denne laves som en quiet install via CMD prompt.

Efterfølgende kan man verificere installationen via ODBC Data Sources eller alternativt via Powershell cmd:
(New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION

Hvis ikke de findes på listen, kan man prøve at sætte dem manuelt op. Find dem under Add… og tilføj.

Da der er flere kilder til de samme filtyper, så vær opmærksom på at du vælger den hvor der under File står ACEODC.DLL

2.2 Excel filer #

Ved Excel-filer forbinder man til denne som database, og hver enkelt ark i filen bliver anset som en tabel, som man kan hive data fra. Et eksempel på en ConnectionString kan se således ud – Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\TEMP\Test.xlsx;Extended Properties=”Excel 12.0 xml;HDR=YES;”;

ConnectionString	Indhold	Note
Provider	Microsoft.ACE.OLEDB.16.0;	Versionen af ACEOBC man bruger, hvor 16 er den nyeste. Man kan også bruge 12 og 14 i nogle tilfælde.
Data Source	C:\TEMP\Test.xlsx;	Fuld fil sti til Excel-arket
Extended Properties	“Excel 12.0 xml;HDR=YES”;	De properties som skal hjælpe med indlæsningen af arket, se nedenfor for forklaring
EP – Filetype	.xls filer – “Excel 8.0” .xlsb filer – “Excel 12.0” .xlsm filer – “Excel 12.0 Macro” .xlsx filer – “Excel 12.0 Xml”	Alt efter hvilken Excel-fil type som skal indlæses, skal man vælge den korrekte type. Denne skal altid være udfyldt.
EP – Column Names	“HDR=YES” “HDR=NO”	Afgør om første række indeholder kolonneoverskrifter og ikke data. Hvis NO vælges, bliver kolonnerne autonummeret fra venstre som F1 – F2 – F3 osv.*
EP – IMEX	“IMEX-1”	Får driveren til at læse kolonner med blandede data typer (int, string, datetime, mf.) som tekst altid. Noter at denne property kan give problemer med write tilbage til arket.
SQL query eksempel	SELECT * FROM [Ark1$] WHERE [ADRESSERINGSNAVN_1] = “Esbjerg Kommune”	Noter at $ i [Ark1$] betyder excel-ark, og ikke kolonnenavn.

* Eksempel med HDR=NO

Koden i sin enkelthed vil se således ud, og efter Open SQL Connection, kan man lave almindelige queries via Execute SQL Statement alt efter behov.

Billedet af fra det udgåede Softomotive ProcessRobot, som senere er blevet opkøbt til Power Automate Desktop.

2.3 CSV og txt filer #

Ved CSV og txt filer forbinder man til mappen som database, og hver enkelt fil i denne bliver anset som en tabel. Ønsker man at læse CSV filer skal man bruge et schema.ini fil i samme mappe som CSV filerne. Connection string vil se således ud – Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\TEST\;Extended Properties=”Text”;

ConnectionString	Indhold	Note
Provider	Microsoft.ACE.OLEDB.16.0;	Versionen af ACEOBC man bruger, hvor 16 er den nyeste. Man kan også bruge 12 og 14 i nogle tilfælde.
Data Source	C:\TEMP\;	Sti til mappen hvor CSV/txt filerne er
Extended Properties	“Text”;	Denne vil altid være Text kun, da det er schema.ini filen som fortæller driveren, hvordan den enkelte CSV fil skal indlæses.
schema.ini	[Regionsopdelt Postnummer.csv] ColNameHeader=True Format=Delimited(;) Col1=”AMTS_NR” Integer width 4 Col2=”ADRESSERINGSNAVN” text Col3=”KOMMUNE_NR” Integer width 3 Col4=”ADRESSERINGSNAVN_1″ text Col5=”POSTNR” Integer width 4 Col6=”BYNAVN” text	Filnavn og extension Første linje indeholder kolonneoverskrifter CSV delimiter ; Col1=”Kolonnenavn” data type width character-length Noter at Kolonnenavn skal matche det som står i filen.
SQL query eksempel	SELECT * FROM [Regionsopdelt Postnummer.csv] WHERE [ADRESSERINGSNAVN_1] = “Esbjerg Kommune”	Her referes specifikt til den csv-fil (tabel) som skal læses fra

Som vist her ligger schema.ini filen sammen med CSV filerne som bruges.

For hver CSV fil, som skal kunne indlæses, skal der udfyldes en sektion i schema.ini filen.

I Power Automate Desktop kan slutresultatet se således ud eksempelvis.

3. Øvelse med RPA #

Det kan være svært at øve RPA i samarbejde med SQL, hvis man ikke har adgang til at lave en database samt tabeller selv. I samspil med W3schools sandbox kan man øve sig i SQL queries, og trække resultatet ud i en datatable med RPA til yderligere øvelse. Dette kan eksempelvis være til loops eller dataudtræk der skal indsættes i Excel / Word.

I Power Automate Desktop gøres dette ved at attache på Chrome fanen, og herefter køre en Extract data from webpage.