Čitanje i upisivanje podataka u Excel uz pomoć T-SQL-a

Objavljeno: 30-01-2014 | Autor: Miloš Milenković | Kategorija: T-SQL, Trikovi

Ознаке:,

2

Ukoliko postoji potreba da se podaci iz nekog Excel dokumenta učitaju u bazu ili da se iz baze izvezu u Excel najčešće se koristi SQL Server Import/Export Wizard (što smo objasnili u prethodnom članku), koji u stvari predstavlja grafički interfejs za kreiranje SSIS paketa kojim se vrši prebacivanje podataka. U slučaju da postoji potreba da se postave neka dodatna podešavanja, najbolje rešenje je otvaranje i editovanje SSIS paketa kreiranog kroz pomenuti Wizard ili kreiranje novog korišćenjem BI Development Studio-a. Međutim, u nekim slučajevima ovaj proces može biti previše komplikovan i zamoran.

Jednostavno rešenje za brzo upisivanje ili iščitavanje podataka iz Excel-a je korišćenje metode OPENROWSET. Osnovna funkcija ove metode je brz i jednostavan pristup linkovanom serveru, a u ovom slučaju je linkovani server ništa drugo do instanca Excela.

Sintaksa za čitanje podataka je sledeća:

Veoma je slična i sintaksa za upis podataka u Excel:

Nekoliko reči i o parametrima OPENROWSET metode koje smo koristili:

  • ‘Microsoft.ACE.OLEDB.12.0′ je naziv provajdera koji koristimo da bismo pristupili Excel-u
  • ‘Excel 12.0 Xml’ označava da je verzija Excel-a 2007 ili novija (za starije verzije se koristi ‘Excel 8.0′)
  • ‘Database=C:\Kefalo_Folder\Kefalo_Excel.xlsx’ je putanja na kojoj se nalazi dokument kome pristupamo
  • ‘HDR=Yes’ označava da prvi red sadrži nazive kolona, a ne podatke, dok ‘HDR=No’ označava suprotno
  • ‘[Sheet1$]‘ predstavlja naziv Excel sheet-a iz koga čitamo ili u koji upisujemo podatke (na naziv koji se vidi u dokumentu treba dodati $)

Treba napomenuti da je potrebno je da na serveru bude uključena mogućnost „Ad Hoc Distributed Queries“, što se postiže komandama:

Takođe, važno je znati da provajder koji koristimo da bismo pristupili Excel-u (‘Microsoft.ACE.OLEDB.12.0′) mora odgovarati verziji SQL Servera (oba moraju biti 32-bit ili 64-bit). Provajder se najčešće instalira zajedno sa MS Office paketom, pa o tome treba voditi računa.

Na kraju, da bi se uspešno upisali ili pročitali podaci iz Excela, neophodno je da nalog pod kojim je pokrenut SQL servis na serveru (najčešće sistemski nalog) ima prava za čitanje i pisanje u folderu u kome se nalazi Excel dokument.

Komentari (2)

Ne uspevam – u cemu je problem? Greska je sledeca:

OLE DB provider „Microsoft.ACE.OLEDB.12.0″ for linked server „(null)“ returned message „Could not find installable ISAM.“.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider „Microsoft.ACE.OLEDB.12.0″ for linked server „(null)“.

„Takođe, važno je znati da provajder koji koristimo da bismo pristupili Excel-u (‘Microsoft.ACE.OLEDB.12.0′)“

Kako da znam koji je provajder koji koristimo?

Pozdrav

Pomenuta greška može da se javi iz više razloga kao što su problemi sa pravima pristupa naloga pod kojim ste logovani na sql server (http://stackoverflow.com/questions/8148774/error-quering-microsoft-ace-oledb-12-0-provider-from-sql-server), ali i kao posledica sintaksnih grešaka. Ako želite možete da nam pošaljete na email redakcija@sql-kefalo.net vaše konkretne upite, excel fajl i potrebne informacije pa da probamo vidimo u čemu je problem.

Verzija provajdera zavisi od verzije Office-a koji imate instaliran na računaru. Možete proveriti iz Excela preko opcije File->Help->About u verziji 2010 ili na sličan način u verziji 2013. Sve verzije pre 2010 su 32-bitne i ne mogu se koristiti sa 64-bitnim SQL Serverom.
http://kb.jetreports.com/article/AA-00722/0/How-to-determine-whether-you-have-32-bit-or-64-bit-Excel.html

Verziju SQL Servera proverite komandom SELECT @@Version;

Napišite komentar