Excel Sorting Error: Event ID 1000

De câteva săptămâni mi „s-a stricat” Excelul. Toate bune și funcționale, dar banala sortare nu funcționa. Pur și simplu nu se întâmpla nimic când apăsam pe sort sau custom sort.

Am observat totuși că în zona de Status bar îmi apărea permanent după apăsare butonului sort un mesaj cu Sort ca un progress bar:

Acesta nu dispărea până la altă operațiune în cadrul fișierului. Am încercat cu mai multe variante de fișiere… dar nimic. Sortarea din PowerQuery funcționa perfect, doar cea din Excel nu.

Am scanat de viruși… nimic serios… Am verificat și golit folderul de start-up („%appdata%\Microsoft\Excel\”), nimic… Nu sunt fan al reinstalării așa că am mai cercetat ceva timp… rezolvându-mi sortările pe alt calculator… :)

Un prieten mi-a recomandat să fac o scanare cu OffCAT (Office Configuration Analyzer Tool (OffCAT) information) dar nu am identificat nimic care să-mi spună ce este defect în Excelul meu.

Întorcându-l pe toate părțile am observat că în momentul în care apăs butonul Options de la Custom Sort „crapă” cu totul Excelul.

De asemenea nici Custom List… nu putea fi accesibil.

O eroare de aplicație trebuie să aibă clar și un răspuns în Event Viewer, unde am identificat evenimentul de eroare: ID 1000 cu eroarea pe scurt de mai jos:

Faulting application name: EXCEL.EXE, version: 16.0.11029.20079, time stamp: 0x5bf4bf1a

Faulting module name: EXCEL.EXE, version: 16.0.11029.20079, time stamp: 0x5bf4bf1a

Exception code: 0xc0000005

Fault offset: 0x017fb27d

Faulting process id: 0x6dc

Faulting application start time: 0x01d4914853d71130

Faulting application path: C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE

Soluțiile de pe net nu au avut efectul scontat pentru mine.

Între timp, cum memoria mea încă nu m-a lăsat definitiv am început să îmi aduc aminte ce am făcut în ultima perioadă…

Povestea pe scurt: Am fost la un curs. Eu eram pe Number formats Romanian, cursanții pe English (US). Mi-am schimbat și eu locale de pe calculator din Ro în En. Nu îmi aduc aminte dacă era deschis Excelul sau nu în momentul schimbării. Unul din topic-urile de curs era acela de a realiza o lista Custom pentru sortarea în ordine crescătoare a lunilor calendaristice, iar Excelul în Engleză are lunile doar în format Engleză. Datele erau în Română. Așa că am creat împreună cu cursanții o listă pentru lunile din română.

După cele două zile de curs, am revenit la birou și mi-am schimbat înapoi locale pe Romanian pentru că … așa m-am obișnuit. După câteva zile am observat că nu îmi mai funcționa sortarea.

Soluția: Am dat din nou calculatorul pe English (din Control Panel/Clock and Region), am intrat în Excel și am șters acel custom list din Română.

Am dat din nou pe locale Romanian și acum funcționează perfect.

Learnings: Ștergeți toate custom sort list-urile de care nu mai aveți nevoie, mai ales dacă folosiți des transferul dintr-un format în altul.

11/12/2018

Import CSV în Excel mai mult de 1.048.576 linii

Fanii Excel știu probabil că acest instrument este foarte util pentru analize de date dar nu pentru colectarea sau introducerea directă a acestora. În majoritatea cazurilor cantitățile mari de date ajung în Excel pentru analiză prin importul din surse de date externe, pornind de la banale fișiere TXT până la servicii web specializate și baze de date.

Cel mai greu este când avem de importat mai multe fișiere într-un raport centralizat iar suma înregistrărilor depășește valoarea 1.048.676 linii (1024 la puterea a doua sau 2 la puterea 20). Tehnic Excelul nu poate gestiona într-o foaie de calcul mai multe înregistrări. Mulți utilizatori recurg la metoda importului gradual, dar acest fapt implică operațiuni manuale și suplimentare pentru centralizarea sumelor.

Prezentul articol propune metoda importului într-un model de date, nu direct într-o foaie de calcul. Un model este o formă binară de păstrare a datelor necesare prelucrărilor Excel și poate fi interpretat cu ajutorul unui Power Pivot. Aveți aici o referință foarte utilă pentru începătorii în acest domeniu.

În modelul de import prezentat în filmuleț sunt importate 3 fișiere CSV care au însumat peste 1,3 milioane de linii.

În partea de sus a CSV-urile avem un antet din 3 linii: titlul documentului, anii de referință și capul de tabel efectiv. Primele două linii vor fi eliminate în partea de import, iar a treia linie din primul fișier va fi transformată în cap de tabel pentru import. De asemenea, la finalul fiecărui CSV este o linie de totaluri care trebuie eliminată din toate CSV-urile la import.

Pentru a putea importa fișierele folosim limbajul M din Power Query. Lista funcțiilor și operațiunilor posibile este disponibilă la această adresă.

Pentru eliminarea antetelor din celelalte două fișiere se transformă coloana de date în tipul date. Având în vedere că ele nu sunt valori date Power Query va returna un mesaj de eroare. Prin intermediul mesajului de eroare putem elimina liniile respective din import.

După aplicarea pașilor de clean-up și formatare a datelor și coloanelor este foarte important modul în care salvăm modelul prin operațiunea de Cloase and Load To și alegerea opțiunii Add this data in the Data Model.

Ulterior Power Pivotul este foarte asemănător de utilizat ca oricare alt Pivot din Excel.

Sper să vă fie util!

11/06/2018

Import multiple Microsoft Word forms in Excel using PowerQuery

Disclaimer: Acest articol are doar un caracter de studiu și nu își propune să înlocuiască în nici un fel aplicațiile programatice sau paginile și aplicațiile web dedicate. Este doar ca exemplu de import a unor date din Word în Excel.

Acest articol este destinat utilizatorilor de Office care doresc să colecteze informații nestandardizate din procesele lor de afaceri. Știu, este destul de dificil să faci formulare în Word, dar cred că decât să faci șabloane în Word cu puncte-puncte pe care să le listezi și să le completeze cineva manual, este mult mai util în primă fază să faci un formular cu niște câmpuri simple în Word.

Pentru a realiza un astfel de formular trebuie să aveți activat meniul Developer din care intrați în Design mode și puteți introduce diferite tipuri de câmpuri din secțiunea: Controls. Atenție, pentru o identificare cât mai exactă a datelor care vor fi salvate în importul brut este recomandat să aveți niște convenții de notare predefinite și să folosiți eventual tag-uri. Acest articol este cu tag-uri.

Exemplu despre modul în care se introduc tag-urile:

Documentul pentru acest exemplu poate fi descărcat de aici.

Șablonul poate fi partajat în rețea în mod clasic sau într-un DFS sau într-un SharePoint, OneDrive, Google Drive, Dropbox… sau pe mail.

Fiecare user completează formularul și îl salvează într-o astfel de locație centralizată.

Pentru a putea prelua datele din toate fișierele am utilizat pentru acest exemplu un script Power Query în care am inclus funcția Decompress de la autorul KenR (How to extract data from a ZIP file using just Power Query – http://www.excelandpowerbi.com/?p=155). Fiecare document Word este în fapt un document ZIP care conține mai multe fișiere XML și nu numai. Pentru a extrage datele din Word avem nevoie de acces la fișierul document.xml pe care îl parsăm după o serie de cuvinte cheie.

Codul sursă pentru script disponibil si in fisierul Excel de exemplu este:

Rezultatul final in funcție de numărul de fișiere din folder și subfoldere poate arăta în felul următor:

Cum puteți testa:

Descărcați șablonul Word.
Completați câteva documente pe baza șablonului și salvați-le în aceeași locație.
Descărcați și Deschideți fisierul Excel și accesați Query editor după care modificați valoarea de la Source.
Apăsați în Query editor opțiunea Close and Load
Ar trebui să aveți tabelul cu date.

Sper să vă fie util!

08/05/2018