Gegevens verplaatsen van Excel naar Access

In dit artikel wordt beschreven hoe u uw gegevens van Excel naar Access kunt verplaatsen en uw gegevens kunt converteren naar relationele tabellen, zodat u Microsoft-Excel en Access samen kunt gebruiken. Kortom, Access is het beste voor het vastleggen, opslaan, query's en delen van gegevens, en Excel is het beste voor het berekenen, analyseren en visualiseren van gegevens.

In twee artikelen, Access of Excel voor het beheren van uw gegevens en de tien belangrijkste redenen voor het gebruik van Access met Excel,wordt besproken welk programma het meest geschikt is voor een bepaalde taak en hoe u Excel en Access samen kunt gebruiken om een praktische oplossing te maken.

Wanneer u gegevens van Excel naar Access verplaatst, zijn er drie basisstappen voor het proces.

drie basisstappen

Opmerking: Zie Basisbeginselen voor databaseontwerp voor informatie over gegevensmodelleren en relaties in Access.

Stap 1: Gegevens importeren uit Excel access

Het importeren van gegevens is een bewerking die veel soepeler kan verlopen als u de tijd neemt om uw gegevens voor te bereiden en op te schonen. Het importeren van gegevens is alsof u naar een nieuw huis gaat. Als u uw bezit opsschoont en organiseert voordat u gaat verhuizen, is het veel gemakkelijker om uw nieuwe huis te vinden.

Uw gegevens reinigen voordat u importeert

Voordat u gegevens importeert in Access, Excel het een goed idee om:

  • Cellen die niet-atomaire gegevens bevatten (dat wil zeggen meerdere waarden in één cel) converteren naar meerdere kolommen. Een cel in een kolom 'Vaardigheden' die bijvoorbeeld meerdere vaardigheidswaarden bevat, zoals 'C# programmeren', 'VBA-programmering' en 'Webontwerp', moet worden uitgesplitsd om kolommen te scheiden die elk slechts één vaardigheidswaarde bevatten.

  • Gebruik de opdracht TRIM om voor-, achter- en meerdere ingesloten spaties te verwijderen.

  • Verwijder niet-afdrukbare tekens.

  • Spel- en interpunctiefouten zoeken en oplossen.

  • Dubbele rijen of dubbele velden verwijderen.

  • Zorg ervoor dat kolommen met gegevens geen gemengde opmaak bevatten, met name getallen die zijn opgemaakt als tekst of datums die zijn opgemaakt als getallen.

Zie de volgende help-onderwerpen Excel voor meer informatie:

Opmerking: Als uw behoeften voor gegevensreiniging complex zijn of als u niet de tijd of resources hebt om het proces zelf te automatiseren, kunt u overwegen een externe leverancier te gebruiken. Zoek voor meer informatie naar 'datareinigingssoftware' of 'gegevenskwaliteit' door uw favoriete zoekmachine in uw webbrowser.

Kies het beste gegevenstype wanneer u importeert

Tijdens de importbewerking in Access wilt u goede keuzes maken, zodat u weinig (indien van) conversiefouten ontvangt waarvoor handmatig ingrijpen nodig is. In de volgende tabel wordt samengevat hoe Excel getalindelingen en Access-gegevenstypen worden geconverteerd wanneer u gegevens uit Excel importeert naar Access, en vindt u enkele tips over de beste gegevenstypen die u kunt kiezen in de wizard Spreadsheet importeren.

Excel getalnotatie

Gegevenstype in Access

Opmerkingen

Aanbevolen procedures

Tekst

Tekst, Memo

Het gegevenstype Access Text slaat alfanumerieke gegevens op tot maximaal 255 tekens. Het gegevenstype Access Memo slaat alfanumerieke gegevens op tot 65.535 tekens.

Kies Memo om te voorkomen dat gegevens worden afgekapt.

Getal, Percentage, Breuk, Wetenschappelijk

Getal

Access heeft één gegevenstype Getal dat varieert op basis van een eigenschap Veldgrootte (Byte, Integer, Lang geheel getal, Enkel, Dubbel, Decimaal).

Kies Dubbel om fouten in gegevensconversie te voorkomen.

Datum

Datum

Access en Excel gebruiken beide hetzelfde seriële datumnummer om datums op te slaan. In Access is het datumbereik groter: van -657.434 (1 januari 100 a.d.) tot 2.958.465 (31 december 9999 A.D.).

Omdat het datumsysteem 1904 niet wordt herkend (gebruikt in Excel voor de Macintosh), moet u de datums converteren in Excel of Access om verwarring te voorkomen.

Zie Het datumsysteem, de opmaak of de tweecijferige jaaropmaak wijzigen en Gegevens importeren of koppelen aan gegevens in een Excel werkmap voor meer informatie.

Kies Datum.

Time

Tijd

In Access Excel u beide tijdwaarden opslaan met hetzelfde gegevenstype.

Kies Tijd, wat meestal de standaardinstelling is.

Valuta, Boekhouding

Valuta

In Access worden gegevens in het gegevenstype Valuta met precisie opgeslagen als 8-bytenummers tot vier decimalen en worden deze gebruikt om financiële gegevens op te slaan en afronding van waarden te voorkomen.

Kies Valuta, wat meestal de standaardwaarde is.

Booleaans

Ja/Nee

Access gebruikt -1 voor alle Ja-waarden en 0 voor alle geen waarden, terwijl Excel 1 gebruikt voor alle WAAR-waarden en 0 voor alle ONWAAR-waarden.

Kies Ja/Nee,waarmee de onderliggende waarden automatisch worden ge converteert.

Hyperlink

Hyperlink

Een hyperlink in Excel access bevat een URL of webadres dat u kunt klikken en volgen.

Kies Hyperlink,anders wordt in Access standaard het gegevenstype Tekst gebruikt.

Wanneer de gegevens in Access zijn, kunt u de gegevens Excel verwijderen. Vergeet niet eerst een back-up van de oorspronkelijke Excel te maken voordat u deze kunt verwijderen.

Zie het Help-onderwerp Gegevens importeren of koppelen aan gegevens in een Excel voor meer informatie.

Gegevens automatisch op een eenvoudige manier toe te delen

Een veelvoorkomende Excel gebruikers hebben, is het toevoegen van gegevens met dezelfde kolommen aan één groot werkblad. U hebt bijvoorbeeld een oplossing voor het bijhouden van activa die is gestart in Excel maar nu is uitgegroeid tot bestanden uit veel werkgroepen en afdelingen. Deze gegevens kunnen zich in verschillende werkbladen en werkmappen of in tekstbestanden die gegevensfeeds van andere systemen zijn. Er is geen gebruikersinterfaceopdracht of een eenvoudige manier om vergelijkbare gegevens toe te Excel.

De beste oplossing is om Access te gebruiken, waar u eenvoudig gegevens in één tabel kunt importeren en toevoegen met behulp van de wizard Spreadsheet importeren. Bovendien kunt u een groot aantal gegevens aan één tabel toe te delen. U kunt de importbewerkingen opslaan, toevoegen als geplande Microsoft-Outlook en zelfs macro's gebruiken om het proces te automatiseren.

Stap 2: Gegevens normaliseren met de wizard Tabelanalyse

Op het eerste gezicht lijkt het een lastige taak om door het proces van het normaliseren van uw gegevens te stappen. Gelukkig is het normaliseren van tabellen in Access een proces dat veel eenvoudiger is, dankzij de wizard Tabelanalyse.

de wizard Tabelanalyse

1. Geselecteerde kolommen naar een nieuwe tabel slepen en automatisch relaties maken

2. Gebruik knopopdrachten om de naam van een tabel te wijzigen, een primaire sleutel toe te voegen, van een bestaande kolom een primaire sleutel te maken en de laatste actie ongedaan te maken

U kunt deze wizard als volgt gebruiken:

  • Converteert een tabel naar een set kleinere tabellen en maakt automatisch een primaire en buitenlandse sleutelrelatie tussen de tabellen.

  • Voeg een primaire sleutel toe aan een bestaand veld dat unieke waarden bevat of maak een nieuw id-veld waarin het gegevenstype AutoNummering wordt gebruikt.

  • Maak automatisch relaties om referentiële integriteit af te dwingen met trapsgevatte updates. Trapsgevatte deletes worden niet automatisch toegevoegd om te voorkomen dat per ongeluk gegevens worden verwijderd, maar u kunt trapsgeleten later eenvoudig toevoegen.

  • Zoek in nieuwe tabellen naar redundante of dubbele gegevens (zoals dezelfde klant met twee verschillende telefoonnummers) en werk dit naar wens bij.

  • Een back-up maken van de oorspronkelijke tabel en de naam ervan wijzigen door '_OLD' toe te staan aan de naam. Vervolgens maakt u een query die de oorspronkelijke tabel reconstrueren, met de oorspronkelijke tabelnaam, zodat bestaande formulieren of rapporten op basis van de oorspronkelijke tabel met de nieuwe tabelstructuur werken.

Zie Uw gegevens normaliseren met de tabelanalyse voor meer informatie.

Stap 3: Verbinding maken toegang tot gegevens van Excel

Nadat de gegevens zijn genormaliseerd in Access en er een query of tabel is gemaakt waarin de oorspronkelijke gegevens worden gereconstrueerd, is het eenvoudig om verbinding te maken met de Access-gegevens van Excel. Uw gegevens zijn nu in Access als een externe gegevensbron en kunnen dus worden verbonden met de werkmap via een gegevensverbinding. Dit is een container met gegevens die wordt gebruikt om de externe gegevensbron te zoeken, aan te melden en te openen. Verbindingsgegevens worden opgeslagen in de werkmap en kunnen ook worden opgeslagen in een verbindingsbestand, zoals een ODC-bestand (ODC-bestandsextensie van Office Data Connection) of een bestand met de gegevensbronnaam (.dsn-extensie). Nadat u verbinding hebt met externe gegevens, kunt u uw werkmap ook automatisch vernieuwen (of bijwerken Excel) vanuit Access wanneer de gegevens worden bijgewerkt in Access.

Zie Gegevens importeren uit externe gegevensbronnen (Power Query) voormeer informatie.

Uw gegevens in Access krijgen

In deze sectie kunt u de volgende fasen van het normaliseren van uw gegevens doorlopen: Waarden in de kolommen Verkoper en Adres in hun meest atomaire stukken opdelen, verwante onderwerpen in hun eigen tabellen scheiden, deze tabellen kopiëren en kopiëren van Excel naar Access, belangrijke relaties maken tussen de nieuw gemaakte Access-tabellen en een eenvoudige query maken en uitvoeren in Access om informatie te retourneren.

Voorbeeldgegevens in niet-genormaliseerde vorm

Het volgende werkblad bevat niet-atomaire waarden in de kolom Verkoper en de kolom Adres. Beide kolommen moeten worden gesplitst in twee of meer afzonderlijke kolommen. Dit werkblad bevat ook informatie over verkopers, producten, klanten en orders. Deze informatie moet ook verder worden gesplitst, per onderwerp, in afzonderlijke tabellen.

Verkoper

Order-id

Orderdatum

Product-id

Qty

Prijs

Klantnaam

Adres

Telefoon

Li, Yale

2349

3/4/09

C-789

3

$ 7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Li, Yale

2349

3/4/09

C-795

6

$ 9,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Ellen

2350

3/4/09

A-2275

2

$ 16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

$ 5,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

B-205

1

$ 4,50

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

$ 9,75

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

$ 16,75

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

$ 7,25

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

$ 16,75

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch, Reed

2353

3/7/09

C-789

5

$ 7,00

Fourth Coffee

7007 Cornell St Redmond, WA 98199

425-555-0201

Informatie in de kleinste onderdelen: atomaire gegevens

Als u met de gegevens in dit voorbeeld werkt, kunt u de opdracht Tekst naar kolom in Excel gebruiken om de 'atomaire' delen van een cel (zoals adres, plaats, staat en postcode) te scheiden in afzonderlijke kolommen.

In de volgende tabel ziet u de nieuwe kolommen in hetzelfde werkblad nadat ze zijn gesplitst om alle waarden atomair te maken. Houd er rekening mee dat de gegevens in de kolom Verkoper zijn gesplitst in de kolommen Achternaam en Voornaam en dat de gegevens in de kolom Adres zijn gesplitst in kolommen Adres, Plaats, Provincie en Postcode. Deze gegevens zijn in 'eerste normale vorm'.

Achternaam

Voornaam

 

Straat

Plaats

Staat

Postcode

Li

Yale

2302 Harvard Ave

Bellevue

WA

98227

Adams

Ellen

1025 Columbia Circle

Haarlem

WA

98234

Hance

Jimmy

2302 Harvard Ave

Bellevue

WA

98227

Koch

Riet

7007 Cornell St Redmond

Redmond

WA

98199

Gegevens in delen in geordende onderwerpen in Excel

De verschillende tabellen met voorbeeldgegevens die volgen, bevatten dezelfde gegevens uit het Excel werkblad nadat deze zijn gesplitst in tabellen voor verkopers, producten, klanten en orders. Het tabelontwerp is nog niet definitief, maar is op de goede weg.

De tabel Verkopers bevat alleen informatie over verkooppersoneel. Houd er rekening mee dat elke record een unieke id (Verkooppersoon-id) heeft. De waarde Verkooppersoon-id wordt gebruikt in de tabel Orders om orders aan verkopers te koppelen.

Verkopers

Verkoper-id

Achternaam

Voornaam

101

Li

Yale

103

Adams

Ellen

105

Hance

Jimmy

107

Koch

Riet

De tabel Producten bevat alleen informatie over producten. Elke record heeft een unieke id (product-id). De waarde Product-id wordt gebruikt om productgegevens aan de tabel Ordergegevens te koppelen.

Producten

Product-id

Prijs

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5,25

De tabel Klanten bevat alleen informatie over klanten. Houd er rekening mee dat elke record een unieke id (klant-id) heeft. De waarde Klant-id wordt gebruikt om klantgegevens te koppelen aan de tabel Orders.

Klanten

Klant-id

Naam

Straat

Plaats

Staat

Postcode

Telefoon

1001

Contoso, Ltd.

2302 Harvard Ave

Bellevue

WA

98227

425-555-0222

1003

Adventure Works

1025 Columbia Circle

Haarlem

WA

98234

425-555-0185

1005

Fourth Coffee

7007 Cornell St

Redmond

WA

98199

425-555-0201

De tabel Orders bevat informatie over orders, verkopers, klanten en producten. Elke record heeft een unieke id (order-id). Sommige gegevens in deze tabel moeten worden gesplitst in een extra tabel met orderdetails, zodat de tabel Orders slechts vier kolommen bevat: de unieke order-id, de orderdatum, de verkoper-id en de klant-id. De tabel die hier wordt weergegeven, is nog niet gesplitst in de tabel Ordergegevens.

Bestellingen

Order-id

Orderdatum

Verkoper-id

Klant-id

Product-id

Qty

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

Orderdetails, zoals de product-id en het aantal, worden uit de tabel Orders verplaatst en opgeslagen in een tabel met de naam Ordergegevens. Houd er rekening mee dat er negen orders zijn, dus het is logisch dat er 9 records in deze tabel staan. Houd er rekening mee dat de tabel Orders een unieke id (order-id) heeft, die wordt genoemd in de tabel Ordergegevens.

Het uiteindelijke ontwerp van de tabel Orders ziet er als volgt uit:

Bestellingen

Order-id

Orderdatum

Verkoper-id

Klant-id

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

De tabel Orderdetails bevat geen kolommen waarvoor unieke waarden zijn vereist (dat wil zeggen dat er geen primaire sleutel is), dus het is niet erg dat alle kolommen 'redundante' gegevens bevatten. Twee records in deze tabel mogen echter niet volledig identiek zijn (deze regel is van toepassing op een tabel in een database). In deze tabel moeten 17 records staan, elk die betrekking hebben op een product in een afzonderlijke volgorde. In order 2349 bestaan bijvoorbeeld drie C-789-producten uit een van de twee onderdelen van de hele order.

De tabel Orderdetails ziet er daarom als volgt uit:

Details van bestelling

Order ID

Product ID

Qty

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

Gegevens uit een Excel kopiëren en Excel access

Nu de informatie over verkopers, klanten, producten, orders en orderdetails is onderverdeeld in afzonderlijke onderwerpen in Excel, kunt u die gegevens rechtstreeks kopiëren naar Access, waar deze tabellen worden.

Relaties maken tussen de Access-tabellen en een query uitvoeren

Nadat u uw gegevens naar Access hebt verplaatst, kunt u relaties tussen tabellen maken en vervolgens query's maken om informatie over verschillende onderwerpen te retourneren. U kunt bijvoorbeeld een query maken die de order-id en de namen van de verkopers retourneert voor orders die zijn ingevoerd tussen 05-09-3-08-09.

Daarnaast kunt u formulieren en rapporten maken om gegevensinvoer en verkoopanalyse te vereenvoudigen.

Meer hulp nodig?

U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community, ondersteuning vragen in de Answer-community of een nieuwe functie of verbetering voorstellen in Excel User Voice.

Meer hulp nodig?

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

×