Pentru a importa date dintr-o varietate de surse puteți utiliza Get & Transform, introdusă începând cu Excel 2016 (cunoscută anterior ca Power Query).
Importarea datelor
Importul din fișier
Utilizarea eficientă a programului Excel presupune, în primul rând, organizarea eficientă a datelor. Pentru aceasta, va trebui să ținem cont de tipul de date pe care programul nostru le acceptă.
În Excel avem două tipuri de date: Numere și Text. Diferența dintre acestea este dată de valoarea asociată unei celule, când introducem un număr, și lipsa acesteia, când introducem text. În altă ordine de idei, celulele formatate ca text NU AU VALOARE ASOCIATĂ!
De aceea, „transformarea” textului în număr nu se face cu Format Cells. În Excel, un număr poate fi afișat sub mai multe forme (i se poate aplica o „mască”) în schimb, textul este doar un șir de caractere, fără o valoare asociată.
De multe ori suntem nevoiți să importăm datele pe care le vom prelucra în Excel, dintr-o altă aplicație.
Transferul datelor între aplicații diferite se poate face prin intermediul fișierelor .txt, .csv (Comma Separated Value File), sau prin scheme XML (Exchange Markup Language). Uneori trebuie să preluăm informații din baze de date, de pe un site sau de pe un server SQL.
Frecvent, operația se face cu Copy-Paste însă, în asemenea situații putem avea o serie de probleme generate de neconcordanța setărilor regionale, sau de existența unor caractere neprintabile care, în Excel, pot avea un impact negativ. Ne putem trezi în situația în care tot tabelul apare doar pe o coloană sau, și mai rău, într-o singură celulă.
Pentru a evita asemenea situații, folosim instrumentele de import pe care le găsim în Excel 2019 în Fila Data, grupul Get & Transform Data.
Să presupunem că datele noastre sunt într-un fișier text de forma:
Observăm că datele sunt separate pe „coloane” de caracterul „|” (bară verticală), cu excepția primului rând în care sunt separate de două caractere (||). În plus, textul este încadrat între apostrofuri.
Dacă folosim Copy-Paste, informația se va regăsi pe o singură coloană.
Pentru a rezolva o astfel de situație, în acest exemplu, vom folosi expertul de import de date moștenite (activat din File – Options – Data – secțiunea Show legacy data import wizards).
Astfel, din Fila Data – gruparea Get & Transform Data – facem click pe Get Data – Legacy Wizards – alegem From Text (Legacy).
Se va deschide fereastra Import Data, din care alegem fișierul text pe care dorim să-l importăm și apăsăm Import.
Se inițializează asistentul de import text care ne va ghida către realizarea unui import de date într-un format corect care nu va necesita prelucrări ulterioare.
Pasul 1:
Selectăm opțiunea potrivită de separare a textului pe coloane.
- Delimited-pentru cazul în care datele sunt separate pe coloane de un caracter de delimitare (în cazul nostru caracterul „|” )
- Fixed width-când câmpurile sunt aliniate în coloane cu spații între fiecare câmp. Pentru exemplul nostru alegem opțiunea Delimited – Next.
Pasul 2:
Aici va trebui să alegem caracterul de separare. În cazul în care separatorul este dublat (în exemplul propus, antetul conține de două ori caracterul de separare „|„) vom bifa caseta Treat consecutive delimiters as one.
Bifăm caseta Other și tastăm apostrof (delimitatorul folosit în textul nostru).
Dacă textul este prins între ghilimele sau apostrofuri, acestea se vor elimina în mod automat dacă alegem caracterul de încadrare potrivit (Text qualifier) și apăsăm bu
Pasul 3:
Stabilim formatul coloanelor rezultate. Aici va trebui să comunicăm Excel-ului semnificația pe care o au pentru noi datele din fișierul text.
- Prima coloană (care nu conține nimic) precum și coloanele care conțin delimitatorii, nu dorim să le importăm. Prin urmare, le selectăm pe rând și pentru fiecare în parte bifăm Don’t import column (Skip) (și va apărea Skip în partea de sus a coloanei).
- Dacă, în fișierul text, avem o coloană pe care noi o interpretăm ca o coloană cu date calendaristice, nu același lucru îl va face și programul Excel dacă setarile noastre regionale pentru formatul de tip dată calendaristică nu coincid cu cele din fișierul text.
Pentru coloana cu date calendaristice, selectăm coloana din imaginea fișierului text, și comunicăm Excel-ului felul în care sunt acestea așezate (nu cum am dori să fie!).
În cazul analizat, setările regionale sunt pe română (cu data în formatul zi.luna.an) și coincid cu cele din fișierul text.
- Pentru coloana cu informații numerice, selectăm coloana, acționăm butonul Advanced și alegem delimitatorul de zecimale pe care îl au datele în fișierul text. Dacă am ales un separator de zecimale, în mod obligatoriu alegem un alt separator pentru gruparea cifrelor, chiar dacă nu este trecut în fișierul text.
Nu este permis același separator și pentru mii și pentru zecimale.
După ce am stabilit aceste reguli de import, nu mai rămâne decât să acționăm butonul Finish și, în fereastra care se deschide să alegem celula care va reprezenta colțul din stânga al tabelului (celula din care va începe tabelul importat).
Dacă extragem un nou fișier text cu aceeași structură, pentru importul datelor nu mai este necesară parcurgerea pașilor menționați mai sus ci doar o actualizare a datelor care vor înlocui conținutul curent cu cel din fișierul nou.
În cazul adăugarii de noi date în fișierul text, pentru reactualizarea fișierului importat procedăm astfel:
- Selectăm tabelul importat – Click dreapta – Refresh.
- Se deschide fereastra Import Text File – Selectăm documentul .txt – Import.
Dacă dorim să păstrăm și datele inițiale, facem o copie cu opțiunea Paste Special – Value and Number Format, pentru a păstra doar datele importate nu și calea de conectare.
Să presupunem, acum, că datele noastre sunt într-un fișier text de forma:
Observăm că datele sunt separate pe „coloane” de virgulă.
Dacă folosim Copy-Paste, informația se va regăsi pe o singură coloană.
De data aceasta, pentru rezolvarea situației, acționăm asistentul de import text, astfel:
- din Fila Data – gruparea Get & Transform Data – facem click pe From Text/CSV.
- sau din Fila Data – gruparea Get & Transform Data – facem click pe Get Data – From File – alegem From Text/CSV.
Se va deschide fereastra Import Data, din care alegem fișierul text pe care dorim să-l importăm și apăsăm Import.
Selectăm opțiunea potrivită de separare a textului pe coloane din zona Delimiter – pentru cazul în care datele sunt separate pe coloane de un caracter de delimitare (virgulă, semn egal, punct și virgulă, tab, spațiu sau alte caractere, care se pot introduce dacă este aleasă opțiunea Custom). Dacă toate elementele din fiecare coloană au aceeași lungime, selectăm Fixed Width (Lățime fixă).
Zona File Origin: aici se selectează setul de caractere utilizat în fișierul text. În majoritatea cazurilor, se poate lăsa această setare la valoarea sa implicită. Dacă fișierul text a fost creat utilizând un alt set de caractere decât setul de caractere utilizat pe computer, ar trebui modificată această setare pentru a se potrivi cu acel set de caractere.
De exemplu, în cazul în care computerul este setat să utilizeze setul de caractere 1250 (Central European, Windows), dar fișierul a fost creat utilizând setul de caractere 1256 (Arabic, Windows), ar trebui setată originea fișierului la 1256.
De asemenea, în zona de previzualizare fișier este afișat textul așa cum va apărea atunci când este separat în coloane în foaia de lucru.
Dacă apăsăm săgeata de lângă butonul Load putem selecta:
- Load: fișierul text este importat automat într-o foaie de lucru nouă
- Load to: permite configurarea destinației pentru datele importate. Este deschisă fereastra Import Data, de unde putem selecta locul în care este importat fișierul text (foaie de lucru nouă sau într-o foaie de lucru existentă în care se indică locul). Tot de aici se selectează felul în care dorim să vedem datele în registrul de lucru, adică: Table, PivotTable Report, PivotChart sau Only Create Connection (este creată doar o legătură către fișier, care se poate folosi ulterior pentru importul fișierului).
Dacă apăsăm butonul Transform Data: se deschide fereastra Power Query Editor.
Această fereastră are următoarele zone principale:
Ribbon-ul (panglica): interfața cu utilizatorul, similară cu cea din Excel
Query List (Lista de interogări) – această zonă prezintă toate interogările din registrul de lucru curent. Se poate naviga la orice interogare din această zonă pentru a începe editarea acesteia.
Data Preview (Previzualizare date) – în această zonă se vede o previzualizare a datelor cu toți pașii de transformare aplicați. Putem să folosim o serie de comenzi de transformare aici (de ex. filtrare, sortare) fie făcând click dreapta pe antetul coloanelor, fie utilizând săgeata antet de coloană.
Formula Bar (Bara de formule) – în această zonă se poate vedea și edita codul M (limbajul din culisele Power Query) al pasului de transformare curent. Fiecare transformare făcută asupra datelor este înregistrată și apare ca pas în zona pașilor aplicați (Applied Steps).
Properties (Proprietăți) – în această zonă se poate denumi interogarea.
Applied Steps (Pași aplicați) – conține o listă cronologică a tuturor etapelor de transformare care au fost aplicate datelor. De aici pașii se pot parcurge și se pot vizualiza modificările în zona de previzualizare date, se pot șterge, modifica, reordona.
După ce am efectuat transformările dorite asupra datelor, din fila File → gruparea Close → click pe Close &Load → alegem Close &Load To, acțiune care deschide fereastra Import Data prezentată mai sus. Fișierul text importat este inserat în pagină.
Conversia unui număr introdus ca text în număr se face fie cu funcția Value(text) fie cu instrumentul Text to columns din fila Data → gruparea Data Tools.
Pentru aceasta, selectați zona de date care conține textul pe care doriți să-l convertiți în număr, acționați butonul Text to Columns după care urmați pașii în mod analog cu cei parcurși la importul din text.
Importarea din baza de date
Pentru a importa date dintr-o bază de date procedați astfel:
Din fila Data → gruparea Get & Transform Data → Get Data → From DataBase → Selectați baza de date (From SQL Server Database, From Oracle Database, From Microsoft Access Database, etc.)
Spre exemplu, dacă vrem să importăm din MS Access, se va deschide fereastra Import Data în care căutăm și selectăm baza de date pe care o dorim importată. Apoi apăsăm Open.
Se deschide fereastra Navigator care conține în stânga obiectele bazei de date iar in dreapta o secțiune de previzualizare a obiectului selectat.
Dacă apăsăm săgeata de lângă butonul Load putem selecta:
- Load: obiectul selectat este importat automat într-o foaie de lucru nouă
- Load to: permite configurarea destinației pentru datele importate. Este deschisă fereastra Import Data, de unde putem selecta locul în care este importat obiectul selectat (foaie de lucru nouă sau într-o foaie de lucru existentă în care se indică locul). Tot de aici se selectează felul în care dorim să vedem datele în registrul de lucru, adică: Table, PivotTable Report, PivotChart sau Only Create Connection (este creată doar o legătură către fișier, care se poate folosi ulterior pentru importul fișierului).
După apăsarea butonului OK în foaia de lucru apare obiectul importat.
Importarea dintr-o pagină Web
Din fila Data – gruparea Get External Data – selectați From Web.
Excel deschide fereastra From Web, care în partea de sus cere adresa web a paginii de pe care se vor descărca datele (de ex. https://www.cursvalutar.ro) – OK.
În fereastra Access Web Content selectăm Anonymous și apăsăm butonul Connect.
Până la stabilirea conexiunii este arătată următoarea fereastră:
După ce a fost identificat tabelul din pagina respectivă, acesta poate fi importat prin apăsarea butonului Load din fereastra de mai jos.
Pentru reactualizare, din fila Data – gruparea Queries & Connections → alegem Refresh All.
Exportul de date
Export în fișier HTML sau pagină Web
Dacă doriți să exportați un registru de lucru sau o serie de date ca pagină web procedați astfel:
- Deschideți registrul de lucru pe care îl doriți exportat. Dacă doriți să exportați o anumită serie de date, selectați seria respectivă.
- Din fila File – Save As pentru a salva registrul / selecția de date.
- Alegeți o locație iar din fereastra Save As alegeți un nume pentru fișier și din secțiunea Save as type selectați Web Page.
Din secțiunea Save bifați Entire Workbook (pentru a exporta întreg registrul de lucru) sau Selection (pentru a exporta seria de date selectate) → faceți click pe Save.
Dacă apăsați butonul Publish se deschide fereastra Publish as Web Page în care apăsați butonul Publish.
Dacă vreți să dați un nume respectivei pagini, faceți click pe butonul Change Title și tastați numele în secțiunea Page Title din pagina Enter Text.
Rezultatul final este cel de mai jos:
Export în Microsoft Access
Dacă doriți să folosiți o foaie de lucru Excel ca tabel într-o bază de date procedați astfel:
- Deschideți baza de date în care vreți să importați datele Excel
- Din fila External Data – gruparea Import & Link – New Data Source → selectați From File – Excel.
- În fereastra Get External Data (Preluare date externe), faceți click pe Browse pentru a găsi fișierul de date sursă.
- Faceți click pe opțiunea dorită din secțiunea Specify how and where you want to store the data in the current database (Specificați cum și unde se stochează datele în baza de date curentă).
Puteți crea un tabel nou care utilizează datele importate sau puteți să creați un tabel care păstrează o legătură către sursa de date. Astfel,
- dacă alegeți varianta 1, adică “Import the Source Data into a new table in the current database”, se deschide un wizard și la sfârșit este creat un nou tabel care conține câmpurile din Excel.
Cheia primară o stabiliți dvs. sau programul (în cadrul unui pas din wizard). - dacă alegeți varianta 2, adică “Append a copy of the records to the table” și alegeți un anumit tabel din Access, atunci înregistrările vor fi adăugate acestui tabel.
- dacă alegeți varianta 3, adică “Link to the data source by created a linked table” , este creat un tabel care păstrează un link către sursa de date Excel.
