SQL-kyselyjen suorittaminen Excel-tiedostoissa

Vaikka Excelin toiminnot voivat käsitellä useimpia Excelin automaatioskenaarioita, SQL-kyselyt voivat hakea ja muokata Excel-tietojen merkittäviä määriä tehokkaammin.

Oletetaan, että työnkulun on muokattava vain niitä Excel-rekistereitä, jotka sisältävät tietyn arvon. Jotta tämä toiminto toimisi ilman SQL-kyselyjä, tarvitset silmukoita, ehdollisia toimintoja ja useita Excel-toimintoja.

Päinvastaisesti voit ottaa tämän toiminnon käyttöön SQL-kyselyillä käyttämällä vain kahta toimintoa: Avaa SQL -yhteys -toiminto ja Suorita SQL-lauseet -toiminto.

SQL-yhteyden avaaminen Excel-tiedostoon

Ennen SQL-kyselyn suorittamista sinun täytyy avata yhteys sen Excel-tiedoston kanssa, jota haluat käyttää.

Muodosta yhteys luomalla uusi muuttuja, jonka nimi on %Excel_File_Path% ja alusta se Excel-tiedostopolulla. Voit myös ohittaa tämän vaiheen ja käyttää tiedoston pysyvää polkua myöhemmin työnkulussa.

Näyttökuva Määritä muuttuja -toiminnosta, johon täytetään Excel-tiedoston polku.

Ota nyt käyttöön Avaa SQL-yhteys -toiminto ja täytä sen ominaisuuksiin seuraava yhteysmerkkijono.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Note

Jotta esitetyn yhteysmerkkijonon käyttö onnistuisi, sinun on ladattava ja asennettava Microsoft Access Database Engine 2010 Redistributable.

Näyttökuva Avaa SQL-yhteys -toiminnosta.

Salasanalla suojatun Excel-tiedoston SQL-yhteyden avaaminen

Jos SQL-kyselyjä suoritetaan salasanalla suojatuissa Excel-tiedostoissa, tarvitaan eri toimintatapa. Avaa SQL-yhteys -toiminto ei voi muodostaa yhteyttä salasanalla suojattuihin Excel-tiedostoihin, joten suojaus on poistettava.

Käynnistä Excel-tiedosto käyttämällä Käynnistä Excel -toimintoa. Tiedosto on suojattu salasanalla, joten täytä oikea salasana Salasana-kenttään.

Näyttökuva Käynnistä Excel-toiminnosta ja salasanakentästä.

Ota seuraavaksi käyttöön asianmukaiset käyttöliittymän automatisointitoiminnot ja siirry kohtaan Tiedosto > Tiedot > Työkirjan suojaus > Salasanasuojaus. Lisätietoja käyttöliittymän automatisoinnista ja niiden toimintojen käytöstä työpöytätyönkuluissa Työpöytätyönkulkujen automatisointi

Näyttökuva käyttöliittymän toiminnoista, joita käytetään Salaa salasanalla -vaihtoehdon valitsemiseen.

Kun olet valinnut Salasanasuojaus, täytä ponnahdusikkunassa tyhjä merkkijono käyttämällä Täytä tekstikenttä ikkunassa -kenttää. Jos haluat täyttää tyhjän merkkijonon, käytä seuraavaa lauseketta: %""%.

Näyttökuva Täytä tekstikenttä ikkunassa -toiminnosta.

Paina OK-painiketta dialogissa ja ota muutokset käyttöön ottamalla käyttöön Paina ikkunan painiketta -toiminto.

Näyttökuva Paina painiketta ikkunassa -toiminnosta.

Ota lopuksi käyttöön Sulje Excel -toiminto, jos haluat tallentaa ei-suojatun työkirjan uutena Excel-tiedostona.

Näyttökuva Sulje Excel, kun Tallenna asiakirja nimellä -vaihtoehto on valittuna.

Kun tiedosto on tallennettu, avaa yhteys ohjeessa Avaa SQL-yhteys Excel-tiedostoihin esitettyjen ohjeiden mukaisesti.

Kun Excel-tiedoston käsittely on valmis, poista Excel-tiedoston suojaamaton kopio käyttämällä Poista tiedosto(ja) -toimintoa.

Näyttökuva Poista tiedosto(t) -toiminnosta.

Excel-laskentataulukon sisällön lukeminen

Vaikka Lue Excel-laskentataulukosta -toiminto voi lukea Excel-laskentataulukon sisällön, silmukoilla voi kestää kauan iteroida noudetun datan läpi.

Tehokkaampi tapa noutaa tiettyjä arvoja laskentataulukoista on käsitellä Excel-tiedostoja tietokantoina ja suorittaa niille SQL-kyselyjä. Tämä tapa on nopeampi ja tehostaa työnkulkua.

Voit hakea laskentataulukon koko sisällön käyttämällä seuraavaa SQL-kyselyä Suorita SQL-lauseet -toiminnon avulla.

SELECT * FROM [SHEET$]

Näyttökuva SELECT-kyselyllä täytettyjen SQL-lauseiden suoritusta varten.

Note

Jos haluat käyttää tätä SQL-kyselyä työnkuluissa, korvaa TAULUKKO-paikkamerkki sen laskentataulukon nimellä, jota haluat käyttää.

Jos haluat hakea rivit, jotka sisältävät tietyn arvon tietyssä sarakkeessa, käytä seuraavaa SQL-kyselyä:

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Note

Jos haluat käyttää tätä SQL-kyselyä työnkuluissa, korvaa se:

  • TAULUKKO, jonka nimi on sen laskentataulukon nimi, jota haluat käyttää
  • COLUMN NAME ja sarake, joka sisältää etsittävän arvon. Excel-laskentataulukon ensimmäisen rivin sarakkeet tunnistetaan taulukon sarakkeiden nimiksi.
  • VALUE arvoksi, jonka haluat etsiä

Poista tiedot Excel-riviltä

Vaikka Excel ei tue POISTA SQL -kyselyä, voit PÄIVITÄ-kyselyn avulla määrittää tietyn rivin kaikki solut tyhjäarvoksi.

Voit käyttää seuraavaa SQL-kyselyä:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Näyttökuva UPDATE-kyselyllä täytettyjen SQL-lauseiden suoritusta varten.

Kehittäessäsi työnkulkua, korvaa TAULUKKO-paikkamerkki sen laskentataulukon nimellä, jota haluat käyttää.

SARAKE1- ja SARAKE2-paikkamerkit edustavat kaikkien olemassa sarakkeiden nimiä. Tässä esimerkissä sarakkeita on kaksi, mutta todellisessa skenaariossa sarakkeiden määrä voi olla erilainen. Excel-laskentataulukon ensimmäisen rivin sarakkeet tunnistetaan taulukon sarakkeiden nimiksi.

Kyselyn [COLUMN1]='VALUE' osa määrittää rivin, jonka haluat päivittää. Käytä työnkulussa sarakkeen nimeä ja arvoa, joiden perusteella yhdistelmä kuvaa rivejä yksilöllisesti.

Excel-tietojen noutaminen tiettyä riviä lukuun ottamatta

Joissakin skenaarioissa kaikki Excel-laskentataulukon sisältö on ehkä haettava ilman tiettyä riviä.

Tämän tuloksen voi helposti määrittää asettamalla ei-toivotun rivin arvoiksi tyhjäarvot ja noutaa sitten kaikki arvot lukuun ottamatta tyhjäarvoja.

Jos haluat muuttaa laskentataulukon tietyn rivin arvoja, voit käyttää PÄIVITÄ SQL -kyselyä, joka on esitetty kohdassa Poista tiedot Excel-riviltä:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Näyttökuva UPDATE-kyselyllä täytettyjen SQL-lauseiden suoritusta varten.

Suorita seuraava SQL-kysely, kun haluat hakea kaikki laskentataulukon rivit, jotka eivät sisällä tyhjäarvoja:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Note

SARAKE1- ja SARAKE2-paikkamerkit edustavat kaikkien olemassa sarakkeiden nimiä. Tässä esimerkissä sarakkeita on kaksi, mutta todellisessa taulukossa sarakkeiden määrä voi olla erilainen. Excel-laskentataulukon ensimmäisen rivin kaikki sarakkeet tunnistetaan taulukon sarakkeiden nimiksi.