Pagina 1 din 4

Unirea mai multor tabele folosind MS Query

Scris: Vin Aug 27, 2010 3:26 pm
de gecs
Am intalnit in subiectele din forum de mai multe ori problema preluarii datelor din mai multe tabele cu aceeasi structura, dar aflate in fisiere diferite, in scopul centralizarii datelor. Pe langa varianta "muncitoreasca" de a face copy-paste cu datele din fieiserele respective intr-unul singur, mai exista si varianta unui MS Query care ar avea avantajul ca datele din fisierele externe raman la locul lor, pot fi editate, sau fisierele pot fi inlocuite cu altele, cu acelasi nume si cu tabele identice, dar cu datele actualizate.

Comanda SQL necesara unei asemenea operatii e una mai speciala, pentru ca e din categoria celor care nu permit afisarea "grafica" a query-ului in fereastra MS query si pentru compunerea ei va fi necesara folosirea unui editor de texte suplimentar (Notepad e OK), daca nu vrem s-o scriem direct de mana.

Exemplul pe care il voi descrie mai departe e pentru versiunea 2007, dar banuiesc ca ar functiona si in versiunile anterioare cu adaptarile de rigoare.

Presupunem ca avem 3 fisiere, plasate in acelasi folder, denumite Book1.xlsx, Book2.xlsx, Book3.xlsx, iar intr-un al patrulea fisier dorim sa unim datele din aceste fisiere. In cele 3 fisiere avem cate un tabel identic in Sheet1, dar datele din tabelele respective difera. Structura acestor tabele in acest exemplu e "Column 1", "Column 2", "Column 3".

In al patrulea fisier, vom incepe prin a defini o conexiune bazata pe MS Query (Data -> Get External Data -> From Other Sources -> From Microsoft Query)
img_01.jpg
Dupa ce se debifeaza optiunea "Use the Query Wizard..." se selecteaza prima pozitie din lista (<New Data Source>) -> OK.

In dialogul urmator se definesc caracteristicile conexiunii:
img_02.jpg
- se tasteaza un nume pentru conexiunea respectiva;
- se alege driver-ul corespunzator din lista - driver-ul care sa poata accesa fisiere .xlsx;
- se apasa butonul Connect... si in dialogul care urmeaza, dupa ce ne-am asigurat ca la Version e selectat "Excel 12.0" se alege unul dintre cele 3 fisiere (Book1.xlsx - in acest exemplu) apasand pe butonul Select Workbook...
img_03.jpg
img_04.jpg
Dupa 3 OK-uri ajungem inapoi in fereastra "Choose Data Source" cu conexiunea pe care tocmai am creat-o implicit selectata si nu avem decat de apasat inca o data butonul OK...
img_05.jpg
pentru a ajunge in fereastra principala a MS Query, cu dialogul Add Tables afisat
img_06.jpg
Fisierul pe care l-am indicat la crearea conexiunii (Book1.xlsx) e selectat implicit, iar tabelul care ne intereseaza e in Sheet1, asa ca se apasa butonul Add, apoi Close si ajungem in fereastra principala Microsoft Query. Urmatorul pas e sa adaugam toate coloanele din tabel in zona de date a query-ului -> dublu-click pe asteriscul din fereastra "Sheet1$".
img_07.jpg
Deschidem apoi dialogul SQL (apasand pe butonul cu acelasi nume) in care se poate vedea si edita linia de comanda, selectam tot textul de acolo si-l copiem (Ctrl+C). Deschidem Notepad si punem textul copiat acolo (Ctrl+V), iar la sfarsitul secventei dam un Enter, pe randul urmator scriem UNION, urmat de inca un Enter.
img_08.jpg
Adaugam din nou comanda SQL copiata si o editam, modificand apoi numele fisierului din Book1.xlsx in Book2.xlsx. Din nou Enter -> UNION -> Enter -> copiem din nou prima comanda si o editam, modificand numele fisierului in Book3.xlsx. Bineinteles ca daca tabelele de unit sunt in foi de calcul cu alte denumiri decat cea in care se afla primul tabel, se modifica si denumirile foilor de calcul oriunde apar in comanda SELECT aferenta fisierului respectiv. Alias-urile din cele 3 SELECT-uri pot ramane ca in comanda initiala daca toate tabelele din cele 3 fisiere sunt in foi cu acelasi nume - dace insa numele foilor de calcul in care se afla tabelele difera, se vor modifica si alias-urile in consecinta (de ex. se va inlocui in comanda SELECT pentru fisierul respectiv peste tot Sheet1 cu Sheet2).
img_09.jpg
Selectam tot continutul din Notepad -> Copy (Ctrl+C) -> deschidem fereastra SQL in MS Query -> Selectam tot continutul -> Paste (Ctrl+V) -> OK. La apasarea butonului OK din dialogul SQL unde am pus linia completa de comanda va aparea mesajul:
SQL Query can't be represented graphically. Continue anyway?
peste care trecem cu OK.

Din fereastra principala Microsoft Quey va disparea fereastra cu tabelul selectat initial, dar in zona de date vor aparea efectele comenzii SQL care uneste datele din cele 3 tabele.
img_11.jpg
Tot ce a ramas de facut e sa aducem rezultatul query-ului in fisierul .xlsx. Se inchide fereastra Microsoft Query, sau se apasa pe butonul Return Data (cel cu o imagine cu o usa si o sageata :) ). Suntem intorsi in foaia de calcul din fisierul xlsx unde am inceput definirea conexiunii si apasam pe butonul OK din dialogul Import Data pentru a crea tabelul care uneste cele 3 tabele din fisierele externe.
img_12.jpg
Salvam fisierul cu ce nume dorim, inchidem si Notepad-ul (fara salvare) si din acest moment, vom avea in acest fisier tabelul care uneste cele 3 tabele din cele 3 fisiere diferite. Daca datele din cele 3 fisiere se modifica (se adauga/sterg inregistrari, se modifica date de pe anumite coloane etc.) pentru a aduce aceste modificari in fisierul care uneste cele 3 tabele tot ce avem de facut e sa apasam butonul Refresh din tab-ul Data.

Editarea comenzii SQL pentru adaugarea clauzelor UNION se poate face si in fereastra Connection Properties (Data -> grupul Connections -> Properties -> butonul Connection Properties -> tab-ul Definition in zona Command text:)
img_10.jpg
Puteti testa fisierele din arhiva atasata daca le dezarhivati intr-un folder denumit D:\Work\Excel\Test MSQry JOIN.
MSQry JOIN.rar

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 2:46 pm
de cristi_lazarro
ceva nu e clar! daca am mai multe pagini in fisier, cum selectez pagina din care vreau sa fac conexiunea/centralizarea?
nu ar trebui sa apara o lista cu toate paginile fisierului?


in plus, la unele fisiere, nu-mi apare activ butonul de add table iar, la altele, imi spune ca nu poate accesa fisierul resp.


CENTRALIZATORUL trebuie sa fie in acelasi folder?

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 2:54 pm
de gecs
In a 6-a si a 7-a imagine din tutorial apare momentul in care se pot selecta foile de calcul din fisierele implicate in query. MS Query echivaleaza tabelele cu foile de calcul - nu poti avea mai multe tabele in aceeasi foaie de calcul. Ar fi foarte bine ca in foile de calcul unde ai tabelele pe care vrei sa le unesti sa nu existe altceva in afara acestor tabele, iar header-ele tabelelor ar fi bine sa fie pe randul 1.

Daca ai un tabel in Sheet1 si inca un tabel similar in Sheet2 si Sheet3 din acelasi fisier, in dialogul Add Tabled selectezi Sheet1$ -> apesi pe Add, selectezi apoi Sheet2$ -> Add, pe urma Sheet3$ -> Add s.a.m.d. pana ai terminat cu toate fisierele si toate tabelele pe care le vrei unite.

Cate fisiere si cate tabele ai?

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 2:57 pm
de cristi_lazarro
am 13 fisiere cu cate o tabela. nimic altceva in pagina decat niste reguli de completare a tabelelor

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 2:57 pm
de gecs
Centralizatorul nu e obligatoriu sa fie in acelasi folder, desi cred ca ar fi bine asa, dar fisierele in care ai tabelele pe care vrei sa le unesti e obligatoriu sa fie in acelasi folder.

Fisierele in care ai tabelele pe care vrei sa le unesti trebuie sa aiba aceeasi extensie - toate .xls, sau toate .xlsx, pentru ca driver-ul si versiunea de Excel sunt parte a conexiunii. Cred ca e bine sa salvezi toate fisierele (daca difera ca versiune) in aceeasi versiune in care faci centralizatorul (probabil 2007).

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 3:00 pm
de cristi_lazarro
fisierele sunt in acelasi folder, au accesi extensie. o sa mut si centralizatorul

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 3:04 pm
de gecs
Sterge si orice altceva e in foile cu tabele, in afara de tabelele cu date - iti simplifici munca in felul asta.

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 3:23 pm
de cristi_lazarro
exista vre-o limitare la nr de pagini? mie nu-mi apar! apare ceva de genul, iar eu nu am nici o pagina "ab":
Untitled.jpg
in total sunt 36 de pagini intr-un fisier. in plus, pt unele fisiere se dezactiveaza butonul "add" de la "add tables"

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 3:31 pm
de gecs
Apasa pe butonul Options... din dialogul Add Tables si bifeaza toate optiunile din fereastra care apare (Table Options) si tot in aceeasi fereastra apasa si pe butonul Refresh - vezi daca apar toate foile de calcul. E posibil ca in fisierul respectiv sa ai un nume "ab".

Re: Unirea mai multor tabele folosind MS Query

Scris: Lun Aug 30, 2010 3:37 pm
de cristi_lazarro
asta era!. penultima obtiune nu era bifata.

ramane totusi faptul ca unele fisiere nu le poate accesa:
Untitled.jpg