Prevlačenje-učitavanje-importovanje podataka iz datoteka formata TXT u Excel može se obaviti na dva načina. Upotrebom Open (izabrati tip datoteke TXT) i upotrebom PowerQuery funkcionalnosti koja je ugrađena u verzijama 2019-2021-365. U prethodnim verzijama 2010, 2013 i 2016 ova funkcionalnost je bila opciona i dodatno se instalirala na postojeću instalaciju Excel-a.
U verzijama: 2003, 2007, 2010, 2013, 2016, 2019, 2021, 365, za Mac i za web
Prevlačenje upotrebom OPEN
Veličina TXT datoteke je određena operativnim sistemom i za FAT16B može biti 2GB, za FAT32 – 4GB, HFS – 2GB, itd. Što omogućava da snimite tabelu od 10 kolona (svaka kolona po 10 bajtova) i skoro 20 miliona redova, naravno ako imate program koji može generisati ili Notepad koji može ispisati toliku datoteku.
Tu svakako nismo zaboravili ni delimiter (oznaka za razdvajanje kolona) koja može biti: Tabulator (znak TAB – ASCII kod karaktera 009) , tačka-zarez, zarez, razmak, ili korisnički definisan znak. Isto tako ne zaboravimo da je u excelu maksimalno dozvoljena tabela od 1.048.576 redova i 16.384 kolone.
To je ujedno i ograničenje za ovu vrstu učitavanja podataka za daljnju obradu.
Znači da sve obrade više od milion redova radimo upotrebom PowerQuery i PowerPivot funkcionalnostima.
Čarobnjak za prevlačenje podataka
Možete da otvorite tekstualnu datoteku koju ste kreirali u drugom programu kao Excel radnu svesku pomoću komande Open. Otvaranje tekstualne datoteke u Excel-u ne menja format datoteke — ovo možete videti u naslovnoj traci Excel-a, gde ime datoteke zadržava ekstenziju tekstualne datoteke.
- Idite na File> Open i potražite lokaciju koja sadrži tekstualnu datoteku.
Izaberite TXT datoteku na padajućoj listi tipa datoteke u dijalogu Open.
- Nakon klika na Open dugme pokrenuće se čarobnjak za pomoć u tri koraka.
Prvi korak
- Od vas očekuje da odredite da li ćete za razdvajanje vrednosti po kolonama koristiti posebni znak za razdvajanje (delimiter) kolona ili ćete brojem znakova odrediti širinu za sve redove.
- Čarobnjak preporučuje kodni raspored teksta (UTF-8) kao i mogućnost štikliranja da li je u prvom redu zaglavlje tabele (My data has headers.)
- Kada završite izbor opcija kliknite dalje (Next>).
Drugi korak
- Traži da odredite znak za razdvajanje kao što je Tabulator (Tab), tačkazarez/polukolona (Semicolon), zarez (Comma), razmak (Space) ili neki drugi znak koji sami odredite (Other).
- U slučaju da imate uzastopne znakove razdvajanja, npr, duplirani, triplirani, štiklirajte i samostalni kvadrat za tretiranje uzastope znakove razdvajanja kao jedan (Treat consecutive delimiters as one).
- I na kraju odredite navodnike za tekstove (Text qualifier).
- Kada završite izbor opcija kliknite dalje (Next>).
Treći korak
- Od vas se sada očekuje da za svaku kolonu iz TXT datoteke odredite tip podataka.
- Podrazumevana vrednost je Opšti format (General), može biti i tekst, (Text), datum (Date) određene pravopisne forme (DMY, dan, mesec i godina), ili da neku kolonu jednostavno i ne prevučete-importujete (Do not import column (skip)).
- Ovde jednostavno možete sve ostaviti u postojećem opštom formatu.
- Kada završite izbor opcija kliknite završetak (Finish).
Prevlačenje upotrebom PowerQuery
U slučaju da radimo sa više od milion redova u tabeli (snimljenih u TXT formatu) jedini način da to obavimo u Excel-u je upotreba PowerQuery funkcionalnosti. PowerQuery je dodatak u verzijama 2010 do 2016 i mora se naknadno instalirati, a u verziji 2019, 2021 i 365 on je nezaobilazna funkcija na tab-u Data.
Kada otvorite tab Data prvi alat na traci vam je Get Data. U prvoj opciji From File odaberite From Text/CSV, kao na sledećoj slici.
U dijalogu za izbor datoteke (Files) odaberite folder i datoteku TXT formata koju želite da učitate. Nakon informativnog prozora za konekciju na datoteku otvara se sadržaj kao na sledećoj slici:
Kada učitavamo podatke iz TXT formata i neznamo koji znak razdvaja podatke po kolonama, pametnije je da svaki red učitamo u jednu kolonu, a samu obradu tj. transformaciju obavimo u PowerQuery-ju. Jednostavno tako što ćemo za Delimiter (srednja kolona) odabrazi znak za koji znamo da ne postoji u TXT datoteci. U konkretnom slučaju je to Colon.
PowerQuery Editor
Neophodna transformacija podataka iz TXT datoteke obaviće se klikom na Transform Data, koji poziva PowerQuery Editor.
Da bi tu jednu kolonu razdvojili na više kolona upotrebićemo alate na tabu Add Column. Prvi alat Column From Examples-From Selection kao na sledećoj slici omogućava nam jednostavan način formiranja nove kolone navođenjem primera.
Primer odvajanja broja računa uplatioca- se može videti na sledećoj slici. Najčešće jedan primer nije dovoljan, tako da je potrebno i u onom redu gde nudi drugačije rešenje korisnik ispravi sadržaj kao u ovom primeru u 9. redu.
Tako isto možemo iz tekstualnog formata datuma izdvojiti datum i kasnije ga transformisati u ispravni datumski format, što ćemo videti u sledećim koracima.
Isto tako ako želimo izdvojiti tekst u posebnu kolonu navođenjem delove teksta između dva znaka za razdvajanje izabrađemo opciju Text Between Delimiters u Extract meniju istog taba.
Extract opcija je na istoj traci sa alatima.
Sama veličina delimitera nije ograničena, i može biti bilo koji niz koji mi definišemo u dijalogu na sledećoj slici.
Takav intuitivni postupak možemo ponavljati koliko god nam je potrebno, na osnovu sadržaja u jednoj ili više selektovanih kolona.
Razdvajanje brojeva i znakova po kolonama
U slučaju da želimo razdvojiti sve brojeve i tekstove u posebne kolone možemo upotrebiti opciju Split Column – By Digit to Non-Digit kao što je prikazano na sledećoj slici.
Kako izgleda rezultat tog razdvajanja možemo videti odmah po kliku na akciju.
Svakako da sada imamo i problem spajanja celih brojeva iz jedne kolone sa decimalama u drugoj koloni upotrebom alata Merge Column u delu alata Text Column.
Obratite pažnju na početak svake kolone postoji statistički pregled podataka u koloni, da li su svi podaci validirani, ima li grešaka i praznih ćelija. Pored tih podataka postoji histogram unikatnih vrednosti i broja ponavljanja u celoj koloni.
Kada želimo transformisati datume zapisane iz tekstualnog formata u datumski format, prvo iskoristimo sada već poznatu transformaciju na osnovu našeg uzorka.
Potom na početku zaglavlja kolone imamo simbol tipa podataka sa padajućim izborom.
Jednostavno izaberite Date za definisanje datumskog polja tj. kolone.
Zatvaranje PowerQuery Editora i prevlačenje podataka
Kada smo konačno izvršili sve potrebne transformacije i uklonili nepotrebne kolone možemo zatvoriti PowerQuery Editor u tabu Home. Klikom na Close & Load gde imamo dve mogućnosti.
Prva mogućnost Close & Load zatvara editor i prevlači podatke u tabelu Excel radnog prostora što je najčešći slučaj.
Druga mogućnost Close & Load To nam pored zatvaranja editora nudi da prevučemo podatke u tabelu Excel radni prostor, direktno u Pivot tabelu, Pivot dijagram i samo da uspostavi konekciju sa podacima sa eventualnim dodavanjem u Data model za daljnju obradu u PowerPivot dodatnoj funkcionalnosti.
Izborom podrazumevanog izbora Table dobijamo konačni sledeći oblik u Excel-u.
