Datu pārvietošana no Excel uz Access

Šajā rakstā uzzināsit, kā pārvietot datus no Excel uz Access un konvertēt datus par relāciju tabulām, lai varētu izmantot Microsoft Excel un programmu Access kopā. Īsi raksturojot, Access ir vispiemērotākā datu tveršanai, glabāšanai, vaicājumiem un koplietošanai, bet Excel ir vislabākā datu aprēķināšanai, analīzei un vizualizēšanai.

Divos rakstos " Access vai Excel izmantošana datu pārvaldībai " un 10 galvenie iemesli, kāpēc Access izmantot ar programmu Excel, tiek apspriests, kura programma ir vispiemērotākā konkrētam uzdevumam un kā kopā izmantot programmu Excel un Access, lai radītu praktisku risinājumu.

Pārvietojot datus no Excel uz Access, procesam jāveic trīs pamata darbības.

1. darbība. Datu importēšana no Excel programmā Access

Datu importēšana ir darbība, kas var noritēt daudz vieglāk, ja veltīsit laiku datu sagatavošanai un tīrīšanai. Datu importēšana ir kā pārcelšanās uz jaunu dzīvesvietu. Ja pirms pārcelšanās iztīrāt un sakārtojat savas mantas, iekārtošanās jaunajā mājā ir daudz vieglāka.

Notīriet datus pirms importēšanas

Pirms datu importēšanas programmā Access programmā Excel vajag veikt šādas darbības:

• Konvertējiet šūnas, kurās ir neatomiski dati (tas ir, vairākas vērtības vienā šūnā), par vairākām kolonnām. Piemēram, šūna kolonnā "Prasmes", kurā ir vairākas prasmju vērtības, piemēram, "C# programmēšana", "VBA programmēšana" un "Tīmekļa noformējums", ir jāsadala atsevišķās kolonnās, kurās katrā ir tikai viena prasmes vērtība.
• Izmantojiet komandu TRIM, lai noņemtu sākuma, beigu un vairākas iegultās atstarpes.
• Noņemt nedrukājamās rakstzīmes.
• Atrodiet un labojiet pareizrakstības un pieturzīmju kļūdas.
• Noņemiet dublētās rindas vai lauku dublikātus.
• Pārliecinieties, vai datu kolonnās nav jaukta formatējuma, it īpaši skaitļi, kas formatēti kā teksts, vai datumi, kas formatēti kā skaitļi.

Papildinformāciju skatiet šajās Excel palīdzības tēmās:

• Desmit labākie veidi, kā attīrīt jūsu datus
• Unikālo vērtību filtrēšana un vērtību dublikātu noņemšana
• Skaitļu, kas saglabāti kā teksts, pārvēršana par skaitļiem
• Datumu, kas saglabāti kā teksts, konvertēšana par datumiem

Labākā datu tipa izvēle importēšanas laikā

Importēšanas operācijas laikā programmā Access ir jāizdara labs risinājums, lai saņemtu nelielu pārvēršanas kļūdu (ja tādas vispār) kam būs nepieciešama manuāla iejaukšanās. Šajā tabulā ir apkopots tas, kā tiek konvertēti Excel skaitļu formāti un Access datu tipi, importējot datus no Excel uz Access, un sniegti daži padomi par labāko datu tipu izvēli izklājlapas importēšanas vednī.

Excel skaitļu formāts	Access datu tips	Komentāri	Paraugprakse
Teksts	Teksts, atgādne	Access teksta datu tips glabā burtciparu datus, kuru garums nepārsniedz 255 rakstzīmes. Access Memo datu tips glabā burtciparu datus, kuru garums nepārsniedz 65 535 rakstzīmes.	Izvēlieties Memo , lai izvairītos no datu apciršanas.
Skaitlis, Procenti, Daļskaitlis, Zinātnisks	Skaitlis	Programmā Access ir viens datu tips Skaitlis, kas mainās atkarībā no lauka lieluma rekvizīta (baits, vesels skaitlis, garš vesels skaitlis, viens, dubults, decimālskaitlis).	Izvēlieties Dubult, lai izvairītos no datu konvertēšanas kļūdām.
Datums	Datums	Access un Excel datumu glabāšanai izmanto vienu un to pašu datumu sērijas numuru. Programmā Access datumu diapazons ir lielāks: no -657 434 (100. gada 1. janvāris) līdz 2 958 465 (9999. gada 31. decembris). Tā kā Access neatpazīst 1904 datumu sistēmu (tiek izmantota programmā Excel Macintosh datoriem), datumi ir jākonvertē programmā Excel vai Access, lai izvairītos no pārpratumiem. Papildinformāciju skatiet sadaļā Datumu sistēmas, formāta vai divciparu gada interpretācijas maiņa un Datu importēšana vai saistīšana ar datiem Excel darbgrāmatā.	Izvēlieties Datums.
Laiks	Laiks	Gan programma Access, gan Excel glabā laika vērtības, izmantojot vienu un to pašu datu tipu.	Izvēlieties laiku, kas parasti ir noklusējuma iestatījums.
Valūta, grāmatvedība	Valūta	Programmā Access datu tips Valūta glabā datus kā 8 baitu skaitļus ar precizitāti līdz četrām decimāldaļas vietām, un tas tiek izmantots, lai glabātu finanšu datus un novērstu vērtību noapaļošanu.	Izvēlieties Valūta, kas parasti ir noklusējuma iestatījums.
Būla izteiksme	Jā/nē	Programma Access izmanto -1 visām vērtībām Yes un 0 visām vērtībām No, savukārt Excel izmanto 1 visām vērtībām TRUE un 0 visām vērtībām FALSE.	Izvēlieties Yes/No, kas automātiski konvertē pamatā esošās vērtības.
Hipersaite	Hiper​​​saite	Hipersaite programmā Excel un Access satur URL vai tīmekļa adresi, uz kuras varat noklikšķināt un sekot.	Izvēlieties Hipersaite, pretējā gadījumā programma Access pēc noklusējuma var izmantot datu tipu Teksts.

Kad dati ir programmā Access, varat izdzēst Excel datus. Neaizmirstiet vispirms dublēt oriģinālo Excel darbgrāmatu, pirms to izdzēst.

Papildinformāciju skatiet Access palīdzības tēmā Datu importēšana vai saistīšana ar datiem Excel darbgrāmatā.

Automātiska datu pievienošana vienkāršā veidā

Bieža problēma, ar ko Excel lietotāji saskaras, ir datu ar vienādām kolonnām pievienošana vienā lielā darblapā. Piemēram, jums var būt līdzekļu izsekošanas risinājums, kas sākotnēji tika izveidots programmā Excel, bet tagad ir izvērsies, iekļaujot failus no daudzām darbgrupām un nodaļām. Šie dati var būt dažādās darblapās un darbgrāmatās vai teksta failos, kas ir datu plūsmas no citām sistēmām. Programmā Excel nav pieejama lietotāja interfeisa komanda vai vienkāršs veids, kā pievienot līdzīgus datus.

Vislabākais risinājums ir izmantot programmu Access, kur varat viegli importēt un pievienot datus vienā tabulā, izmantojot Izklājlapas importēšanas vedni. Turklāt vienā tabulā var pievienot lielu datu apjomu. Varat saglabāt importēšanas darbības, pievienot tās kā ieplānotus Microsoft Outlook uzdevumus un pat izmantot makro, lai automatizētu procesu.

2. darbība. Datu normalizēšana, izmantojot tabulu analizatora vedni

Pirmajā brīdī datu normalizēšana var šķist biedējošs uzdevums. Par laimi, tabulu normalizēšana programmā Access ir process, kas ir daudz vienkāršāks, pateicoties Tabulu analizatora vednim.

1. Velciet atlasītās kolonnas uz jaunu tabulu un automātiski izveidojiet relācijas

2. Izmantojiet pogu komandas, lai pārdēvētu tabulu, pievienotu primāro atslēgu, padarītu esošu kolonnu par primāro atslēgu un atsauktu pēdējo darbību

Izmantojot šo vedni, rīkojieties šādi:

• Pārvērtiet tabulu par mazāku tabulu kopu un automātiski izveidojiet primārās un ārējās atslēgas relāciju starp tabulām.
• Pievienojiet primāro atslēgu esošam laukam, kurā ir unikālas vērtības, vai izveidojiet jaunu ID lauku, kurā tiek izmantots datu tips AutoNumber.
• Automātiski izveidojiet relācijas, lai ieviestu attiecinošo integritāti ar kaskadētiem atjauninājumiem. Kaskadētā dzēšana netiek pievienota automātiski, lai novērstu nejaušu datu izdzēšanu, taču vēlāk varat viegli pievienot kaskadēto dzēšanu.
• Meklējiet jaunās tabulās liekus vai dublētus datus (piemēram, tas pats klients ar diviem dažādiem tālruņa numuriem) un pēc vajadzības atjauniniet to.
• Dublējiet sākotnējo tabulu un pārdēvējiet to, tās nosaukumam pievienojot "_OLD". Pēc tam izveidojiet vaicājumu, kas rekonstruē sākotnējo tabulu ar sākotnējo tabulas nosaukumu, lai visas esošās formas vai atskaites, kuru pamatā ir sākotnējā tabula, darbotos ar jauno tabulas struktūru.

Papildinformāciju skatiet sadaļā Datu normalizēšana, izmantojot tabulu analīzi.

3. darbība. Izveidojiet savienojumu ar Access datiem programmā Excel

Pēc datu normalizēšanas programmā Access un vaicājuma vai tabulas izveidošanas, kas rekonstruē sākotnējos datus, ir vienkārši jāizveido savienojums ar Access datiem no programmas Excel. Jūsu dati tagad atrodas programmā Access kā ārējs datu avots, un tāpēc tos var savienot ar darbgrāmatu, izmantojot datu savienojumu, kas ir informācijas konteiners, kas tiek izmantots, lai atrastu ārējo datu avotu, pieteiktos un piekļūtu tam. Savienojuma informācija tiek glabāta darbgrāmatā, un to var saglabāt arī savienojuma failā, piemēram, Office datu savienojuma (ODC) failā (.odc faila nosaukuma paplašinājums) vai datu avota nosaukuma failā (.dsn paplašinājums). Pēc savienojuma izveides ar ārējiem datiem varat arī automātiski atsvaidzināt (vai atjaunināt) Excel darbgrāmatu programmā Access, kad dati tiek atjaunināti programmā Access.

Papildinformāciju skatiet sadaļā Datu importēšana no ārējiem datu avotiem (Power Query).

Datu iegūšana programmā Access

Šajā sadaļā aprakstīti šādi datu normalizēšanas posmi: Kolonnu Pārdevējs un Adrese vērtību sadalīšana to atomiskākajās daļās, saistīto tēmu sadalīšana atsevišķās tabulās, šo tabulu kopēšana un ielīmēšana no programmas Excel programmā Access, atslēgu relāciju izveide starp jaunizveidotajām Access tabulām un vienkārša vaicājuma izveide un izpilde programmā Access, lai atgrieztu informāciju.

Example data in non-normalized form

Nākamajā darblapā kolonnās Pārdevējs un Adrese ir neatomiskās vērtības. Abas kolonnas ir jāsadala divās vai vairākās atsevišķās kolonnās. Šī darblapa ietver arī informāciju par pārdevējiem, produktiem, klientiem un pasūtījumiem. Šī informācija arī pēc tēmas būtu jāsadala atsevišķās tabulās.

Pārdevējs	Order ID	Pasūtījuma datums	Produkta ID	Daudz.	Cena	Klienta nosaukums	Adrese	Tālrunis
Li, Jēla	2349	3/4/09	C-789	3	$7.00	Kafejnīca “Viktorija”	7007 Cornell St, Redmond, WA 98199	425-555-0201
Li, Jēla	2349	3/4/09	C-795	6	$ 9.75	Kafejnīca “Viktorija”	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, Džims	2351	3/4/09	C-795	6	$ 9.75	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Džims	2352	3/5/09	A-2275	2	$ 16.75	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Džims	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	Kafejnīca “Viktorija”	7007 Cornell St, Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	$7.00	Kafejnīca “Viktorija”	7007 Cornell St, Redmond, WA 98199	425-555-0201

Informācija mazākajās daļās: atomu dati

Strādājot ar datiem šajā piemērā, varat izmantot komandu Teksts kolonnā programmā Excel, lai šūnas "atomiskās" daļas (piemēram, adrese, pilsēta, novads un pasta indekss) sadalītu atsevišķās kolonnās.

Šajā tabulā redzamas jaunās kolonnas tajā pašā darblapā pēc to sadalīšanas, lai visas vērtības padarītu atomiskas. Ņemiet vērā, ka informācija kolonnā Pārdevējs ir sadalīta kolonnās Uzvārds un Vārds, bet informācija kolonnā Adrese ir sadalīta kolonnās Adrese, Pilsēta, Novads un Pasta indekss. Šie dati ir "pirmajā normālformā".

Uzvārds	Vārds	Adrese	Pilsēta	Štats	Pasta indekss
Li	Jēla	2302 Harvard Ave	Liepāja	WA	98227
Mieriņa	Elena	1025 Kolumbijas loks	Olaine	WA	98234
Baltiņš	Jānis	2302 Harvard Ave	Liepāja	WA	98227
Koch (Koch)	Niedres	7007 Cornell St, Redmond	Rīga	WA	98199

Datu sadalīšana organizētos objektos programmā Excel

Vairākās tabulās ar parauga datiem tālāk redzama tā pati informācija no Excel darblapas pēc tam, kad tā ir sadalīta tabulās pārdevējiem, produktiem, klientiem un pasūtījumiem. Tabulas noformējums nav galīgs, bet tas ir uz pareizā ceļa.

Tabulā Pārdevēji ir tikai informācija par tirdzniecības personālu. Ņemiet vērā, ka katram ierakstam ir unikāls ID (Pārdevēja ID). Vērtība Pārdevēja ID tiks izmantota tabulā Pasūtījumi, lai savienotu pasūtījumus ar pārdevējiem.

Pārdevēji
Pārdevēja ID	Uzvārds	Vārds
101	Li	Jēla
103	Mieriņa	Elena
105	Baltiņš	Jānis
107	Koch (Koch)	Niedres

Tabulā Produkti ir tikai informācija par produktiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (Produkta ID). Produkta ID vērtība tiks izmantota, lai saistītu informāciju par produktu ar tabulu Pasūtījuma informācija.

Produkti
Produkta ID	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

Tabulā Klienti ir tikai informācija par klientiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (klienta ID). Klienta ID vērtība tiks izmantota, lai saistītu klienta informāciju ar tabulu Pasūtījumi.

Customers
Klienta ID	Nosaukums	Adrese	Pilsēta	Štats	Pasta indekss	Tālrunis
1001	Contoso, Ltd.	2302 Harvard Ave	Liepāja	WA	98227	425-555-0222
1003	Adventure Works	1025 Kolumbijas loks	Olaine	WA	98234	425-555-0185
1005	Kafejnīca “Viktorija”	Kornela iela 7007	Rīga	WA	98199	425-555-0201

Tabulā pasūtījumi ir informācija par pasūtījumiem, pārdevējiem, klientiem un produktiem. Ņemiet vērā, ka katram ierakstam ir unikāls ID (pasūtījuma ID). Daļa informācijas šajā tabulā ir jāsadala papildu tabulā, kurā ir pasūtījumu informācija, lai tabulā Pasūtījumi būtu tikai četras kolonnas — unikālais pasūtījuma ID, pasūtījuma datums, pārdevēja ID un klienta ID. Šeit redzamā tabula vēl nav sadalīta tabulā Pasūtījumu informācija.

Pasūtījumi
Order ID	Pasūtījuma datums	Pārdevēja ID	Klienta ID	Produkta ID	Daudz.
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

Pasūtījumu informācija, piemēram, produkta ID un daudzums, tiek pārvietota no tabulas Pasūtījumi un saglabāta tabulā ar nosaukumu Pasūtījuma informācija. Ņemiet vērā, ka ir 9 pasūtījumi, tāpēc ir loģiski, ka šajā tabulā ir 9 ieraksti. Ņemiet vērā, ka tabulai Pasūtījumi ir unikāls ID (Pasūtījuma ID), uz kuru ir atsauce tabulā Pasūtījumu informācija.

Gala noformējumam ir jāizskatās šādi:

Pasūtījumi
Order ID	Pasūtījuma datums	Pārdevēja ID	Klienta 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

Tabulā Detalizēta informācija par pasūtījumu nav kolonnu, kurām nepieciešamas unikālas vērtības (tas ir, nav primārās atslēgas), tāpēc vienā kolonnā vai visās kolonnās var iekļaut "liekus" datus. Tomēr divi ieraksti šajā tabulā nedrīkst būt pilnīgi identiski (šis noteikums attiecas uz jebkuru tabulu datu bāzē). Šajā tabulā jābūt 17 ierakstiem — katrs no tiem atbilst produktam atsevišķā pasūtījumā. Piemēram, pasūtījumā 2349 trīs C-789 produkti veido vienu no divām visa pasūtījuma daļām.

Tāpēc tabulai Pasūtījuma dati jāizskatās šādi:

Pasūtījuma dati
Order ID	Produkta ID	Daudz.
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

Datu kopēšana un ielīmēšana no Excel programmā Access

Tagad, kad informācija par pārdevējiem, klientiem, produktiem, pasūtījumiem un pasūtījuma dati programmā Excel ir sadalīti atsevišķās tēmās, varat kopēt šos datus tieši programmā Access, kur tie tiks pārvērsti par tabulām.

Relāciju izveide starp Access tabulām un vaicājuma izpilde

Kad dati ir pārvietoti uz programmu Access, varat izveidot relācijas starp tabulām un pēc tam izveidot vaicājumus, lai atgrieztu informāciju par dažādām tēmām. Piemēram, var izveidot vaicājumu, kas atgriež pasūtījuma ID un pārdevēju vārdus pasūtījumiem, kas ievadīti laikā no 05.05.09. līdz 08.03.09.

Turklāt varat izveidot formas un atskaites, lai atvieglotu datu ievadi un pārdošanas analīzi.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistam Excel tehnoloģiju kopienā vai saņemt atbalstu kopienās.