Taulukkolaskennan ja tietokantojen yhteiskäyttö -
Luento 11

• Luentotaltiointi
• Johdanto
• Taulujen ja kyselyjen haku tietokannasta Exceliin
  • Access-tietokannasta Exceliin
  • Tietolähteiden määrittäminen
  • ODBC-yhteys Excelistä tietokantaan
• Tietojen poimiminen WWW-sivulta Exceliin
• Tekstitiedostojen tuominen Exceliin
  • Tietojen tuominen XML-tiedostosta Exceliin
• Tietojen linkittäminen Accessiin
  • XML-tiedon tuonti Accessiin
• Tiedon uudelleenjärjestely
  • Tietojen järjestely taulukkolaskentaan
  • Tietojen järjestely tietokantaan
• Tietokannasta Wordiin
  • Mail Merge
  • Taulukko tietokannasta Wordiin

Tällä luennolla tutkitaan kuinka tietoja siirretään taulukkolaskennan ja tietokantojen välillä. Tutustutaan kuinka taulukkolaskentaan saadaan tuotua tietoa muista eri muotoisista lähteistä. Käydään läpi myös kuinka taulukkolaskennan tai tietokannan tietoja voidaan siirtää tekstinkäsittelyohjelmaan.

Johdanto

Tiedon tuonti jatkoprosessointia varten ei ole aina niin helppoa...

• Tiedon järkevyys
• Tiedon pirstaleisuus
• Tieto hankalassa muodossa tai vaikeasti löydettävissä
• Tiedon puutteellisuus

Taulujen ja kyselyjen haku tietokannasta Exceliin

Access-tietokannasta Exceliin

1. Valitse Data | Import External Data | New Database Query
2. Valitse MS Access Database ja sen jälkeen tiedostohallinnasta .mdb-päätteinen Access-tietokantatiedosto.
3. Valitse taulusta tai valmiista kyselystä halutut kentät
   • Useamman taulun tapauksessa on tehtävä viite-eheyskytkennät Microsoft Query -ohjelmassa raahaamalla viittaava kenttä viitattavan kentän päälle (esim. Henkilo_AsiakasID toisen taulun AsiakasID-kentän päälle)
4. Lisäksi voidaan tehdä vielä rajausehtoja haettavalle tiedolle (vrt. SQL:n WHERE) ja järjestää jonkin kentän mukaan (vrt. SQL:n ORDER BY)
5. Lopuksi valitaan tallennetaanko äskettäin tehty kysely johonkin ja mihin kyselyllä haetut tiedot laitetaan.

Jatkokäsittelyt kannattaa tehdä eri välilehdelle kuin varsinainen tuotu data. Kyselyä voi nimittäin päivittää jälkikäteen:

• Tiedon päivittäminen onnistuu napauttamalla oikealla johonkin kohtaan "ulkoista tietoa" ja valitsemalla Refresh Data.
• Valitsemalla Data | Import External Data | Edit Query voidaan muokata vielä uudelleen tietokannasta haettavaa tietoa tai sen rajausta.

Tietolähteiden määrittäminen

ODBC on rajapinta SQL-sovellusten ohjelmointiin. ODBC-yhteyden avulla yksi ohjelma voi ottaa yhteyttä useisiin erilaisiin tietokantojen. ODBC-yhteyksiä käytetään erityisesti Windows-ohjelmissa, jotka hyödyntävät tietokantoja.

1. Valitse Start | Settings | Control Panel | Administrative Tools | Data Sources (ODBC)
2. Yhteyksiä saa lisättyä Add-painikkeesta.
3. Sitten on valittava sopiva tietokanta-ajuri, joka tukee ODBC:tä.
4. Lopuksi tulee dialogi, joka riippuu tietokanta-ajurista. Tässä määritellään tietokantatiedosto tai palvelin, jossa tietokanta sijaitsee, ja vaadittavat tunnukset.

ODBC-yhteyttä voi testata Erkka-ohjelmalla valitsemalla Query-välilehdellä File | Connect to Database... ja kirjoittamalla Database-kohtaan ODBC-yhteyden nimi.

ODBC-yhteys Excelistä tietokantaan

• Data|Import External Data|New Database Query

  

• Valitsemalla Browse pääsee selaamaan tiedostoiksi tallentamiaan ODBC-yhteyksiä.
• ODBC-yhteyden valinnan jälkeen pääsee selaamaan tarjolla olevia tauluja ja näkymiä. Näistä voi valita haluamansa kentät tuotavaksi Exceliin

  

• Seuraavaksi päästään suodattamaan rivejä

  

  Suodatusta harvemmin tarvitsee tehdä tässä vaiheessa, koska se on kannattanut tehdä jo tietokannan puolella näkymää määriteltäessä.
• Seuraavassa vaiheessa määrätään miten tuotava data järjestetään

  

  Järjestäminenkin kannattaa tehdä jo tietokannan puolella.
• Lopuksi voi tuoda datan Exceliin, muokata dataa Microsoft Query -ohjelmalla tai luoda OLAP kuution datan pohjalta.

  

Tietojen poimiminen WWW-sivulta Exceliin

• Data|Get External Data|New Web Query

  

• Annetaan osoite josta tiedot halutaan hakea ja valitaan sivulta haluttu taulukko.

Tekstitiedostojen tuominen Exceliin

• Tekstimuodossa olevaa tietoa voi exceliin tuoda esim. leikepöydän kautta tai valinnalla Data|Get External Data|Import Data|Files of type:Text File tai Data|Get External Data|New Web Query
• Jos tekstimuotoinen tieto on jo valmiiksi excelissä niin yleensä se pitää jakaa sarakkeisiin Data|Text to Columns -toiminnalla. Sama velho käynnistyy suoraan Data|Get External Data|Import Data|Files of type:Text File -valinnan kautta.

  

• Tiedon tuominen tekstimuodossa onnistuu jos tieto on välimerkeillä eroteltua (delimited, esim. tab, pilkku (,), puolipiste (;)) tai kiinteämittaisissa kentissä (fixed width).
• Käsiteltäessä kiinteämittaisia kenttejä joudutaan määrittämään missä kohtaa kenttä vaihtuu

  

• Käsiteltäessä välimerkeillä eroteltua tietoa pitää vielä varmistaa käytetty erotinmerkki

  

• Jokaisesta kentästä voidaan kertoa käsitelläänkö se puhtaasti tekstinä, päivämääränä vaiko automaattisesti (general)

  

  Usein excel epäonnistuu automaattisessa kentän tyypin määrittämisessä ja saattaakin olla varminta tuoda kaikki kentät tekstinä ja vasta myöhemmin muotoilla tarkemmin.

Tietojen tuominen XML-tiedostosta Exceliin

Valitse Data | XML | Import. Oikealla avautuvasta palkista voidaan valita mitä osia XML-puusta tuodaan laskentataulukoille.

Tietojen linkittäminen Accessiin

• Valitse File|Get External Data|Link Tables
• Valitse haluamasi tietolähteen tyyppi Files of type -alasvetovalikosta. Accessiin voi linkittää tietoja hyvin monenlaisista lähteistä:
  • Muista Access-tietokannoista
  • dBase-tietokannoista
  • Excel-tiedostoista
  • Exchangesta
  • HTML-dokumenteista
  • Outlookista
  • Paradoxista
  • Tekstitiedostoista
  • ODBC-yhteyden kautta
• Valittuasi tietolähteen Access käynnistää tietolähteen tyypistä riippuen sopivan velhon
• Excel-tiedostoa linkitettäessä täytyy valita linkitettävä työkirja (worksheet) tai nimetty alue

  

• Joitain linkitettyjä tietoja pystyy päivittämään Accessin avulla esim. excel-tietoja mutta useimpia voi vain lukea.
• Tietojen kopiointi tapahtuu tismalleen samalla tavalla kuin linkittäminen mutta aloitetaan vain valitsemalla File|Get External Data|Import

XML-tiedon tuonti Accessiin

Valitse File | Get External Data | Import ja muodoksi XML. Toistuvat elementit muodostavat taulun kentät.

Tiedon uudelleenjärjestely

Exceliin tai tietokantaan tuotu tieto ei yleensä ole suoraan sopivassa muodossa ja sitä pitää järjestellä ennen kuin tiedoilla pystyy kunnolla laskemaan.

Kokeillaan järjestää suunnistustuloksia jatkokäsittelykelpoiseen muotoon.

Tietojen järjestely taulukkolaskentaan

• Funktiot
• Paste Special
• Replace

Tietojen järjestely tietokantaan

Tiedon "taulukkomuotoistaminen" on helpointa tehdä taulukkolaskennassa, joten siivoaminen kannattaa tehdä siellä. Taulukko on helppo tuoda Accessiin valinnalla File | Get External Data | Import/Link

Tietokannasta Wordiin

Mail Merge

Microsoft Wordillä on helppoa tehdä joukkokirjeitä tai osoitelappuja tietokannassa tai excelissä olevien tietojen pohjalta.

Taulukko tietokannasta Wordiin

Mail Merge -toiminnon lisäksi tietokannoista voidaan liittää tauluja tai kyselyjä Wordiin. Valitse View | Toolbars | Database ja palkista valinta Insert Database.

• Get Data -valinnalla voidaan määritellä tietokantayhteys tai ottaa Access-tietokanta käyttöön.
  • Kun yhteys on muodostunut, voidaan valita haluttu taulu tai kysely.
• Query Options -valinnalla voidaan rajoittaa tietueiden määrää.
• Insert Data lisää tiedon.
  • Lisäys voidaan tehdä taulukkona, jolloin saadaan sen hetkinen tilanne, tai kenttänä, jota voidaan päivittää.