Premikanje podatkov iz Excela v Access

V tem članku je opisano, kako premaknete podatke iz Excela v Access in pretvorite podatke v relacijske tabele, tako da lahko uporabljate Microsoft Excel in Access skupaj. Če povzamemo, Access je najboljši za zajemanje, shranjevanje, poizvedovanje in skupno rabo podatkov, Excel pa je najboljši za izračunavanje, analiziranje in ponazoritev podatkov.

V dveh člankih, Uporaba Accessa ali Excela za upravljanje podatkov in 10 glavnih razlogov za uporabo Accessa z Excelom, razpravljata o tem, kateri program je najprimernejši za določeno opravilo in kako uporabljati Excel in Access skupaj, da ustvarite praktično rešitev.

Ko premaknete podatke iz Excela v Access, obstajajo trije osnovni koraki postopka.

1. korak: Uvoz podatkov iz Excela v Access

Uvoz podatkov je postopek, ki lahko poteka veliko bolj gladko, če si vzamete nekaj časa za pripravo in čiščenje podatkov. Uvoz podatkov je kot selitev v nov dom. Če očistite in organizirate svoje premoženje, preden se preselite, je namestitev v novem domu veliko lažja.

Čiščenje podatkov pred uvozom

Preden uvozite podatke v Access, je v Excelu priporočljivo:

• Pretvorite celice, ki vsebujejo neatomske podatke (to je več vrednosti v eni celici), v več stolpcev. Celica v stolpcu »Spretnosti«, ki vsebuje več vrednosti spretnosti, kot so »Programiranje v C#«, »Programiranje VBA« in »Spletno oblikovanje«, mora biti na primer razdeljena v ločene stolpce, od katerih vsak vsebuje le eno vrednost spretnosti.
• Z ukazom TRIM odstranite vodilne, končne in več vdelanih presledkov.
• Odstranite znake, ki se ne natisnejo.
• Poiščite in popravite pravopisne in ločilne napake.
• Odstranite podvojene vrstice ali podvojena polja.
• Prepričajte se, da stolpci s podatki ne vsebujejo mešanih oblik zapisa, zlasti števil, oblikovanih kot besedilo, ali datumov, oblikovanih kot številke.

Če želite več informacij, glejte te teme pomoči za Excel:

• Najboljših deset načinov za čiščenje podatkov
• Filter za iskanje enoličnih vrednosti ali odstranjevanje podvojenih
• Pretvarjanje števil v obliki besedila v število
• Pretvarjanje datumov, shranjenih kot besedilo, v datume

Izbira najboljše vrste podatkov pri uvozu

Med operacijo uvoza v Accessu želite narediti dobre izbire, tako da boste prejeli nekaj (če sploh) napak pri pretvorbi, ki bodo zahtevale ročno posredovanje. V spodnji tabeli je povzeto, kako se Excelove oblike zapisa števil in Accessovi podatkovni tipi pretvorijo, ko uvozite podatke iz Excela v Access, in ponuja nekaj namigov o najboljših podatkovnih tipih, ki jih lahko izberete v čarovniku za uvoz preglednic.

Excelova oblika zapisa številk	Podatkovni tip v Accessu	Pripombe	Najboljša praksa
Text (Besedilo)	Besedilo, Beležka	Podatkovni tip Access »Besedilo« shranjuje alfanumerične podatke z največ 255 znaki. Podatkovni tip Access Memo shranjuje alfanumerične podatke z največ 65.535 znaki.	Izberite Memo , da se izognete skrajšanju podatkov.
Število, odstotek, ulomek, znanstveno	število	Access ima en podatkovni tip Število, ki se razlikuje glede na lastnost Velikost polja (Bajt, Celo število, Dolgo celo število, Enojno, Dvojno, Decimalno).	Izberite Dvojno, da se izognete napakam pri pretvorbi podatkov.
Datum	Datum	Access in Excel uporabljata isto zaporedno številko datuma za shranjevanje datumov. V Accessu je časovno obdobje večje: od -657.434 (1. januar 100 n. št.) do 2.958.465 (31. december 9999). Ker Access ne prepozna datumskega sistema 1904 (ki se uporablja v Excelu za računalnike Macintosh), morate datume pretvoriti v Excelu ali Accessu, da se izognete zmedi. Če želite več informacij, glejte Spreminjanje datumskega sistema, oblike zapisa ali dvomestne interpretacije leta in Uvoz ali povezovanje podatkov v Excelovem delovnem zvezku.	Izberite Datum.
Čas	Ura	Access in Excel shranjujeta časovne vrednosti z istim podatkovnim tipom.	Izberite Čas, ki je običajno privzeta.
Valuta, računovodstvo	Valuta	V Accessu podatkovni tip »Valuta« shranjuje podatke kot 8-bajtna števila z natančnostjo na štiri decimalna mesta in se uporablja za shranjevanje finančnih podatkov in preprečevanje zaokroževanja vrednosti.	Izberite Valuta, ki je običajno privzeta.
Logičen	Da/ne	Access uporablja -1 za vse vrednosti Da in 0 za vse vrednosti Ne, medtem ko Excel uporablja 1 za vse vrednosti TRUE in 0 za vse vrednosti FALSE.	Izberite Da/Ne, s čimer samodejno pretvorite temeljne vrednosti.
Hiperpovezava	Hiperpovezava	Hiperpovezava v Excelu in Accessu vsebuje URL ali spletni naslov, ki ga lahko kliknete in mu sledite.	Izberite Hiperpovezava, sicer lahko Access privzeto uporabi podatkovni tip Besedilo.

Ko so podatki v Accessu, lahko izbrišete Excelove podatke. Ne pozabite najprej varnostno kopirati izvirnega Excelovega delovnega zvezka, preden ga izbrišete.

Če želite več informacij, glejte temo pomoči za Access Uvoz podatkov v Excelovem delovnem zvezku ali povezovanje do njih.

Samodejno dodajanje podatkov na preprost način

Pogosta težava, ki jo imajo uporabniki Excela, je dodajanje podatkov z istimi stolpci v en velik delovni list. Morda imate na primer rešitev za sledenje sredstev, ki se je začela v Excelu, zdaj pa se je razširila in vključuje datoteke iz številnih delovnih skupin in oddelkov. Ti podatki so lahko na različnih delovnih listih in delovnih zvezkih ali v besedilnih datotekah, ki so viri podatkov iz drugih sistemov. Ni ukaza uporabniškega vmesnika ali preprostega načina za dodajanje podobnih podatkov v Excelu.

Najboljša rešitev je uporaba Accessa, kjer lahko preprosto uvozite in dodate podatke v eno tabelo s čarovnikom za uvoz preglednic. Poleg tega lahko v eno tabelo dodate veliko podatkov. Postopke uvoza lahko shranite, jih dodate kot načrtovana opravila Microsoft Outlooka in celo uporabite makre za avtomatizacijo postopka.

2. korak: Normalizirajte podatke s čarovnikom za analizo tabel

Na prvi pogled se lahko zdi, da je korak skozi postopek normalizacije podatkov zastrašujoča naloga. Na srečo je normalizacija tabel v Accessu proces, ki je veliko lažji, zahvaljujoč čarovniku za analizo tabel.

1. Povlecite izbrane stolpce v novo tabelo in samodejno ustvarite relacije

2. Uporaba ukazov gumbov za preimenovanje tabele, dodajanje primarnega ključa, spreminjanje obstoječega stolpca v primarni ključ in razveljavitev zadnjega dejanja

S tem čarovnikom lahko naredite to:

• Pretvorite tabelo v nabor manjših tabel in samodejno ustvarite relacijo primarnega in tujega ključa med tabelami.
• Dodajte primarni ključ v obstoječe polje, ki vsebuje enolične vrednosti, ali ustvarite novo polje ID, ki uporablja podatkovni tip samoštevilo.
• Samodejno ustvarite relacije za uveljavljanje referenčne celovitosti s kaskadnimi posodobitvami. Kaskadne izbrisi niso dodani samodejno, da bi preprečili nenamerno brisanje podatkov, vendar lahko kaskadno brisanje dodate pozneje.
• V novih tabelah poiščite odvečne ali podvojene podatke (na primer isto stranko z dvema različnima telefonskima številkama) in jih po želji posodobite.
• Varnostno kopirajte izvirno tabelo in jo preimenujte tako, da njenemu imenu dodate »_OLD«. Nato ustvarite poizvedbo, ki rekonstruira izvirno tabelo z izvirnim imenom tabele, tako da bodo vsi obstoječi obrazci ali poročila, ki temeljijo na izvirni tabeli, delovali z novo strukturo tabele.

Če želite več informacij, glejte Normalizacija podatkov z analizatorjem tabel.

3. korak: Vzpostavljanje povezave z Accessovimi podatki iz Excela

Ko so podatki normalizirani v Accessu in je bila ustvarjena poizvedba ali tabela, ki rekonstruira izvirne podatke, je preprosto vzpostaviti povezavo z Accessovimi podatki iz Excela. Vaši podatki so zdaj v Accessu kot zunanji vir podatkov, zato jih je mogoče povezati z delovnim zvezkom prek podatkovne povezave, ki je vsebnik informacij, ki se uporablja za iskanje zunanjega vira podatkov, prijavo in dostop do njega. Informacije o povezavi so shranjene v delovnem zvezku in jih je mogoče shraniti tudi v datoteko za povezavo, na primer datoteko Officeove podatkovne povezave (ODC) (datotečna pripona .odc) ali datoteko z imenom vira podatkov (pripona .dsn). Ko vzpostavite povezavo z zunanjimi podatki, lahko tudi samodejno osvežite (ali posodobite) Excelov delovni zvezek iz Accessa, ko so podatki posodobljeni v Accessu.

Če želite več informacij, glejte Uvoz podatkov iz zunanjih virov podatkov (Power Query).

Prenos podatkov v Access

Ta razdelek vas vodi skozi te faze normalizacije podatkov: razdelitev vrednosti v stolpcih »Prodajalec« in »Naslov« na najbolj obsežne dele, ločevanje sorodnih tem v lastne tabele, kopiranje in lepljenje teh tabel iz Excela v Access, ustvarjanje ključnih relacij med novo ustvarjenimi Accessovimi tabelami ter ustvarjanje in zagon preproste poizvedbe v Accessu za vrnitev informacij.

Primeri podatkov v nenormalizirani obliki

Na tem delovnem listu so neatomske vrednosti v stolpcih »Prodajalec« in »Naslov«. Oba stolpca je treba razdeliti na dva ali več ločenih stolpcev. Ta delovni list vsebuje tudi informacije o prodajalcih, izdelkih, strankah in naročilih. Te informacije je treba tudi razdeliti po predmetih v ločene tabele.

Prodajalec	ID naročila	Datum naročila	ID izdelka	Količina	Cena	ime stranke	Naslov	Telefonska številka
Li, Yale	2349	3/4/09	C-789	3	7,00 dolarja	Kavarna Četrta kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	9,75 dolarja	Kavarna Četrta kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	16,75 dolarja	Pustolovska dela	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	5,25 dolarja	Pustolovska dela	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 dolarja	Pustolovska dela	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 dolarja	Contoso, d.o.o.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	16,75 dolarja	Pustolovska dela	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	7,25 dolarja	Pustolovska dela	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	$16.75	Kavarna Četrta kava	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	7,00 $	Kavarna Četrta kava	7007 Cornell St Redmond, WA 98199	425-555-0201

Informacije v najmanjših delih: atomski podatki

Pri delu s podatki v tem primeru lahko uporabite ukaz »Besedilo v stolpec « v Excelu, da ločite »atomske« dele celice (kot so naslov, mesto, država in poštna številka) v diskretne stolpce.

V spodnji tabeli so prikazani novi stolpci na istem delovnem listu, potem ko so bili razdeljeni, da vse vrednosti nastanejo atomske. Ne pozabite, da so bili podatki v stolpcu »Prodajalec« razdeljeni na stolpca »Priimek« in »Ime«, informacije v stolpcu »Naslov« pa so bile razdeljene na stolpce »Ulica«, »Mesto«, »Država« in »Poštna številka«. Ti podatki so v »prvi normalni obliki«.

Priimek	Ime	Ulica	Mesto	Zvezna država	Poštna številka
Li	Yale	Harvard Ave 2302	Portorož	WA	98227
Potočnik	Ellen	1025 Kolumbijski krog	Maribor	WA	98234
Hace	Janez	Harvard Ave 2302	Portorož	WA	98227
Koch	Trstno	7007 Cornell St Redmond	Redmond	WA	98199

Razbijanje podatkov v organizirane predmete v Excelu

V več tabelah z vzorčnimi podatki, ki sledijo, so prikazane iste informacije iz Excelovega delovnega lista, potem ko je bil ta razdeljen v tabele za prodajalce, izdelke, stranke in naročila. Načrt tabele še ni dokončen, vendar je na pravi poti.

V tabeli »Prodajalci« so le podatki o prodajnem osebju. Vsak zapis ima enoličen ID (ID prodajalca). Vrednost »ID prodajalca« bo uporabljena v tabeli »Naročila« za povezovanje naročil s prodajalci.

Prodajalci
ID prodajalca	Priimek	Ime
101	Li	Yale
103	Potočnik	Ellen
105	Hace	Janez
107	Koch	Trstno

V tabeli »Izdelki« so le informacije o izdelkih. Vsak zapis ima enoličen ID (ID izdelka). Vrednost ID izdelka se uporabi za povezavo informacij o izdelku s tabelo »Podrobnosti o naročilu«.

Izdelki
ID izdelka	Cena
A-2275	16.75
B-205	4.50
C-789	7.00
C-795	9.75
D-4420	7.25
F-198	5.25

V tabeli »Stranke« so le informacije o strankah. Vsak zapis ima enoličen ID (ID stranke). Vrednost ID stranke se uporabi za povezavo informacij o stranki s tabelo »Naročila«.

Stranke
ID stranke	Ime	Ulica	Mesto	Zvezna država	Poštna številka	Telefon
1001	Contoso, d.o.o.	Harvard Ave 2302	Portorož	WA	98227	425-555-0222
1003	Adventure Works	1025 Kolumbijski krog	Maribor	WA	98234	425-555-0185
1005	Kavarna Četrta kava	Ulica Cornell 7007	Redmond	WA	98199	425-555-0201

V tabeli »Naročila« so informacije o naročilih, prodajalcih, strankah in izdelkih. Vsak zapis ima enoličen ID (ID naročila). Nekatere podatke v tej tabeli morate razdeliti v dodatno tabelo s podrobnostmi o naročilu, da bo tabela »Naročila« vsebovala le štiri stolpce – enolični ID naročila, datum naročila, ID prodajalca in ID stranke. Tukaj prikazana tabela še ni bila razdeljena v tabelo s podrobnostmi o naročilu.

Naročila
ID naročila	Datum naročila	ID prodajalca	ID stranke	ID izdelka	Količina
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

Podrobnosti o naročilu, kot sta ID izdelka in količina, se premaknejo iz tabele »Naročila« in shranijo v tabelo »Podrobnosti o naročilu«. Upoštevajte, da je 9 naročil, zato je smiselno, da je v tej tabeli 9 zapisov. Upoštevajte, da ima tabela »Naročila« enoličen ID (ID naročila), na katerega se sklicuje iz tabele »Podrobnosti o naročilu«.

Končni načrt tabele »Naročila« bi moral biti podoben temu:

Naročila
ID naročila	Datum naročila	ID prodajalca	ID stranke
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

V tabeli »Podrobnosti naročila« ni stolpcev, ki zahtevajo enolične vrednosti (to pomeni, da primarnega ključa ni), zato ni dovolj, če kateri koli ali vsi stolpci vsebujejo »odvečne« podatke. Vendar pa nobena dva zapisa v tej tabeli ne smeta biti popolnoma enaka (to pravilo velja za vse tabele v zbirki podatkov). V tej tabeli mora biti 17 zapisov – vsak ustreza izdelku v posameznem naročilu. Na primer, v naročilu 2349 trije izdelki C-789 sestavljajo enega od dveh delov celotnega naročila.

Tabela »Podrobnosti naročila« bi zato morala biti podobna tej:

Podrobnosti naročila
ID naročila	ID izdelka	Količina
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

Kopiranje in lepljenje podatkov iz Excela v Access

Ko so informacije o prodajalcih, strankah, izdelkih, naročilih in podrobnosti naročila razdeljene v ločene predmete v Excelu, lahko te podatke kopirate neposredno v Access, kjer postanejo tabele.

Ustvarjanje relacij med Accessovimi tabelami in zagon poizvedbe

Ko premaknete podatke v Access, lahko ustvarite relacije med tabelami in nato ustvarite poizvedbe za pridobivanje informacij o različnih temah. Ustvarite lahko na primer poizvedbo, ki vrne ID naročila in imena prodajalcev za naročila, vnesena med 05. 3. in 8. 3. 2009.

Poleg tega lahko ustvarite obrazce in poročila za preprostejši vnos podatkov in analizo prodaje.

Potrebujete dodatno pomoč?

Kadar koli lahko zastavite vprašanje strokovnjaku v skupnosti tehničnih strokovnjakov za Excel ali pa pridobite podporo v skupnostih.