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.
[:en]Dragging-loading-importing data from TXT format files to Excel can be done in two ways. Using Open (select TXT file type) and using the PowerQuery functionality built into versions 2019-2021-365. In previous versions 2010, 2013, and 2016, this functionality was optional and was additionally installed on top of an existing Excel installation.
In versions : 2003, 2007, 2010, 2013, 2016, 2019, 2021, 365, for Mac and for the web
Drag using OPEN
The size of the TXT file is determined by the operating system and can be 2GB for FAT16B, 4GB for FAT32, 2GB for HFS, etc. Which allows you to record a table of 10 columns (each column of 10 bytes) and almost 20 million rows, of course if you have a program that can generate or Notepad that can print such a file.
We certainly didn’t forget the delimiter, which can be: Tab (TAB character – ASCII character code 009), semicolon, comma, space, or user-defined character. Also, let’s not forget that the maximum allowed table in Excel is 1,048,576 rows and 16,384 columns.
This is also a limitation for this type of data upload for further processing.
It means that we do all the processing of more than a million rows using PowerQuery and PowerPivot functionalities.
Drag data wizard
You can open a text file that you created in another program as an Excel workbook using the Open command. Opening a text file in Excel does not change the file format — you can see this in the Excel title bar, where the file name retains the text file extension.
- Go to File > Open and browse to the location that contains the text file.
Select a TXT file from the File Type drop-down list in the Open dialog .
- After clicking the Open button, a three-step help wizard will be launched.
The first step
- It expects you to determine whether you will use a special column delimiter to separate the values by column, or whether you will use the number of characters to determine the width for all rows.
- The wizard recommends the code layout of the text (UTF-8) as well as the option to check if the table header is in the first row (My data has headers.)
- When you have finished selecting options, click Next> .
Second step
- It asks you to specify a separator character such as Tab ( Tab ), Semicolon ( Semicolon ), Comma ( Comma ), Space ( Space ) or some other character you specify ( Other ).
- In case you have consecutive delimiters, for example, duplicated, tripled, check the independent box to treat consecutive delimiters as one ( Treat consecutive delimiters as one ).
- And finally set the quotation marks for texts ( Text qualifier ).
- When you have finished selecting options, click Next> .
The third step
- You are now expected to specify a data type for each column from the TXT file.
- The default value is General format ( General ), it can also be text , date ( Date ) in a certain orthographic form ( DMY , day, month and year), or simply do not drag and import a column ( Do not import column (skip) ).
- Here you can simply leave everything in the existing general format.
- When you have finished selecting the options, click Finish .
Drag using PowerQuery
In case we work with more than a million rows in the table (saved in TXT format), the only way to do it in Excel is to use the PowerQuery functionality. PowerQuery is an add-on in versions 2010 to 2016 and must be installed afterwards, and in versions 2019, 2021 and 365 it is an indispensable function on the Data tab.
When you open the Data tab , the first tool on the ribbon is Get Data . In the first From File option , select From Text/CSV , as in the following image.
In the file selection dialog (Files), select the folder and the TXT file you want to upload. After the information window for connecting to the file, the content opens as in the following image:
When we load data from TXT format and we do not know which character separates the data by column, it is smarter to load each row into one column, and perform the processing itself, i.e. the transformation, in PowerQuery. Simply by choosing for the Delimiter (middle column) a character that we know does not exist in the TXT file. In the specific case it is Colon .
PowerQuery Editor
The necessary transformation of the data from the TXT file will be done by clicking on Transform Data , which calls the PowerQuery Editor .
In order to separate that one column into several columns, we will use the tools on the Add Column tab . The first tool Column From Examples-From Selection as in the following picture allows us a simple way of forming a new column by specifying an example.
An example of separating the payer’s account number can be seen in the following picture. Most often, one example is not enough, so it is necessary for the user to correct the content in the line where he offers a different solution, as in this example in the 9th line.
We can also extract the date from the text date format and later transform it into the correct date format, which we will see in the next steps.
Likewise, if we want to extract text into a separate column by specifying parts of the text between two delimiters, select the option Text Between Delimiters in the Extract menu of the same tab.
The Extract option is on the same toolbar.
The size of the delimiter itself is not limited, and it can be any string that we define in the dialog in the following image.
We can repeat such an intuitive procedure as often as we need, based on the content in one or more selected columns.
Separation of numbers and characters by columns
In case we want to separate all numbers and texts into separate columns, we can use the option Split Column – By Digit to Non-Digit as shown in the following image.
We can see what the result of that separation looks like immediately after clicking on the action.
Of course, now we have the problem of merging whole numbers from one column with decimals in another column using the Merge Column tool in the Text Column tool section .
Pay attention to the beginning of each column, there is a statistical overview of the data in the column, whether all the data is validated, whether there are errors and empty cells. In addition to these data, there is a histogram of unique values and the number of repetitions in the entire column.
When we want to transform dates written from text format to date format, we first use the already known transformation based on our sample.
Then at the beginning of the column header we have a data type symbol with a dropdown.
Simply select Date to define a date field ie. columns.
Closing PowerQuery Editor and dragging data
When we have finally performed all the necessary transformations and removed unnecessary columns, we can close the PowerQuery Editor in the Home tab . By clicking on Close & Load where we have two options.
The first option, Close & Load, closes the editor and drags the data into the Excel workspace table, which is the most common case.
Another possibility Close & Load This , in addition to closing the editor, offers us to drag the data into the Excel workspace table, directly into the Pivot table, Pivot diagram and only to establish a connection with the data with eventual addition to the Data model for further processing in PowerPivot additional functionality.
By choosing the default option Table , we get the final following form in Excel.
[:]