Microsoft Office Access
uređuje Branislav Mihaljev, MVP

Uvoz iz Excela

Nivo:
NIVO 2 - Među ovim prilozima se nalaze preporuke koje se uglavnom odnose na bolje snalaženje i efikasniji rad: dodatni trikovi koji će biti svakom od pomoći i koji upućuju na prag napredne upotrebe programa.

Nastavljamo priču o uvozu podataka koju smo započeli u prethodnom prilogu. Pri uvozu podataka iz Excela u Access tabelu, može se pojaviti jedan problem koji se manifestuje tako što procedura prekine rad sa porukom o grešci ili, ako postoji procedura za presretanje i obradu greške, jednostavno ne prenese sve podatke. Kod nekih korisnika, u zavisnosti od procedure uvoza, može doći da nasilnog prekida rada Accessa.

Kako teče ceo postupak? Prvo ćete napraviti tabelu i definisati polja tabele. Zatim ćete napisati funkciju za uvoz podataka koristeći komandu TransferSpreadsheet. Jet mašina zatim čita jedan po jedan zapis, tj. redove iz XLS datoteke i prenosi ih u tabelu. Pri čitanju sadržaja, Jet analizira podatke u poljima (tj. kolonama u Excelu) i pokušava da "pogodi" njihov tip. Problem nastaje kada se u dva ili više polja jedne kolone nađe različit tip podatka. Na primer, brojevi kuća u ulici mogu biti samo brojevi, ali mogu imati dodatak "a", "b", "c", itd. Dakle, broj "1" će biti prepoznat kao broj, ali broj "1a" će biti prepoznat kao tekst.

Deluje logično da bi trebalo da definišete tip polja Access tabele kao tekst, jer bi takvo polje moglo da čuva obe vrednosti ("1" i "1a"), a kasnije eventualno da promenite tip polja u broj ili da podatke prebacite u radnu tabelu. Nažalost, to nije moguće, jer tipovi podataka uvek moraju da se slažu - broj može da se uveze samo u polje tipa broj, dok tekst može da se uveze samo u polje tipa tekst; ovaj problem je dokumentovan pod nazivom mixed data types. Ukoliko pokušate da uvezete tekst u brojčano polje tj. broj u tekstualno polje, Access će vratiti grešku Numeric field overflow.

Jednostavnije rešenje je da pre uvoza podataka iz XLS datoteke u Access pregledate njen sadržaj i, ukoliko primetite neslaganje vrste podataka u bilo kojoj koloni, preformatirate sadržaj ćelije tako da se ona slaže po tipu podataka sa ostalima. Preporučujemo da ćelije formatirate kao tekst kako biste podacima kasnije mogli lakše da manipulišete u Accessu. Kako biste skratili dosadan postupak pregleda podataka i pretvaranja polja u tekstualni format, upotrebite sledeći makro za Excel. Ovaj jednostavni makro će sve obeležene ćelije preformatirati u tekst.

Sub ConvertNumber2Text()
Dim cell As Object

For Each cell In Selection
  Selection.NumberFormat = "@"
  cell.Value = " " & cell.Value
Next
End Sub

Nadam se da će kolega Grbić pronaći efikasnije rešenje ili bar da neće imati primedbe na "prljavo" (ali i efikasno) rešenje. Nakon prenosa podataka u Access, možete upotrebiti funkciju LTrim da uklonite vodeći razmak koji je preventivno dodat radom makra. To možete učiniti Update upitom:

UPDATE tblTabela SET tblTabela.Polje = LTrim([Polje])

Kada već govorimo o Trim funkcijama - pomenimo da su LTrim i RTrim zasebni slučajevi komande Trim: prva uklanja vodeće razmake, druga razmake iza informacije, dok sama komanda Trim uklanja razmake ispred i iza informacije. Za uklanjanje nepotrebnih razmaka dovoljna je funkcija LTrim, jer Access sam uklanja razmake iza informacije.

Drugo rešenje zahteva izmenu određenih ključeva u bazi Registry, pa vam preporučujemo da, ukoliko niste pasolutno sigurni šta radite, koristite prvo rešenje. Parametri za Jet 4 mašinu se nalaze na u grani baze Registry HLM/Software/Microsoft/Jet/4.0/Engines/Excel/. Ključ koji definiše na koji način će biti tretiran podatak se zove ImportMixedTypes i ima osnovnu vrednost Text. Ovu vrednost možete promeniti u drugu, jedinu opcionalnu, Majority Type. Ova vrednost se odnosi na određeni broj zapisa svake kolone koji se prepoznaju i uvoze. Kako sam naziv kaže, Majority Type (npr. onaj tip koji je najzastupljeniji u koloni) određuje tip podataka cele kolone. Zapisi koji pripadaju najmanje zastupljenom tipu podataka (Minority) tj. oni koji ne odgovaraju najzastupljenijem tipu podataka kolone, tretiraju se kao Null vrednosti.

Nameće se logično pitanje: ima li smisla prilagoditi prenos podataka iz Excela u Access tako da se u toj proceduri ne javljaju greške, ali i da se zanemare neki podaci?

Navedeni ključ određuje na koji način će biti prenete ćelije koje sadrže tekst duži od 255 znakova. Dok je ovaj ključ postavljen na vrednost Text i Jet mašina ne zaključi da barata sa podacima Majority Type, svaki tekst duži od 255 znakova će biti odsečen, iako ste u Access tabeli unapred definisali polje kao tip Memo. Koliko broj redova se zapravo analizira pre donošenja zaključka o tipu podataka? Ovo određuje drugi ključ grane, TypeGuessRows, koji može imati decimalne vrednosti od 0-16. Vrednosti od 1 do 16 određuju stvaran broj kolona, dok vrednost 0 nalaže da se analiziraju svi zapisi.

Konačno, vrednost IMEX u stringu konekcije određuje da li će vrednost ImportMixedTypes biti konsultovana tokom uvoza ili izvoza. IMEX je skraćenica od IMport EXport i može imati tri vrednosti: 0 i 2 ignorišu vrednost ključa ImportMixedTypes, dok se za vrednost 1 uzima u obzir vrednost tog ključa. Iako je navedeno u MSDN članku da možete upotrebiti MAXSCANROWS kako biste zaobišli TypeGuessRows, izgleda da to nije slučaj. Naime MAXSCANROWS: 0 nema nikakvog efekta; umesto toga i dalje se konsultuje vrednost TypeGuessRows.

U zaključku tehnika za zaobilaženje ovog problema, koji ne bi smeo da postoji ili bi barem trebalo da bude bolje rešen s obzirom na to koliko dugo su Excel i Access u istom paketu, predlažemo da koristite TypeGuessRows da biste naterali Jet mašinu da odredi da li postoje "mešani podaci" u koloni, odnosno da malim trikom naterate Jet mašinu da nad određenim podacima favorizuje Majority Type. Drugi mogući scenario: od ponuđenog makroa za Excel napravite programski dodatak (Add-in) i zamolite korisnika da pre prenosa podataka iz Excel datoteke upotrebi makro nad svim podacima.