Ungayisebenzisa kanjani imibuzo ye-SQL ku-Microsoft Excel

Isibuyekezo sokugcina: 17/02/2025
Author: Isaka
  • Funda ukuthi ungavula kanjani uxhumano lwe-SQL kumafayela e-Excel futhi uphathe idatha.
  • Funda indlela yokusebenza ngamafayela e-Excel avikelwe ngephasiwedi.
  • Qalisa imibuzo ye-SQL ku-Excel ukuze uhlunge, ubuyekeze, futhi uhlele ulwazi.
  • Funda ukuthi i-Excel ihlangana kanjani namaseva e-SQL ukuze isebenze kahle kakhulu.

kakhulu

I-Microsoft Excel ingenye yamathuluzi asetshenziswa kakhulu emkhakheni webhizinisi nokuhlaziywa kwedatha. Kodwa-ke, abantu abaningi abangakwazi ukuthi kungenzeka ukusebenzisa imibuzo ye-SQL ngqo kuma-spreadsheet e-Excel, okusiza ukuphathwa kahle nokuhlunga idatha eningi .

Kulesi sihloko, sizohlola indlela yokwenza imibuzo ye-SQL kumafayela e-Excel , ukuvula uxhumano lwe-SQL , ukuthola ulwazi oluthile, kanye nokusebenzisa eminye yemibuzo ewusizo kakhulu ukuhlunga nokuguqula idatha ngaphandle kokuphindaphinda ngesandla phakathi kwamakhulu emigqa.

Ungavula kanjani ukuxhumana kwe-SQL ngefayela le-Excel

Ngaphambi kokuthi usebenzise imibuzo ye-SQL kufayela le-Excel, kufanele usungule uxhumano kudokhumenti. Lokhu kudinga ukumisa umucu wokuxhumana ofanele ovumela umbuzo ukuthi usebenzisane nedatha equkethwe kufayela.

Ukuze ufeze lokhu, ungasebenzisa intambo yokuxhumana elandelayo kwisofthiwe yakho noma inqubo yokuzenzakalela:

Umhlinzeki=Microsoft.ACE.OLEDB.12.0;Umthombo Wedatha=C:\indlela\file.xlsx;Izakhiwo Ezandisiwe=»Excel 12.0 Xml;HDR=YEBO»;

Lapho Umthombo Wedatha kufanele ukhombe khona indawo eqondile yefayela le-Excel kudivayisi yakho.

Qalisa imibuzo ye-SQL kufayela le-Excel elivikelwe ngephasiwedi

Uma ifayela le-Excel livikelwe ngephasiwedi , kufanele ususe isivikelo ngaphambi kokusebenzisa imibuzo ye-SQL. Ukuze wenze lokhu, landela lezi zinyathelo:

  • Vula ifayela ngokwenza noma usebenzisa isofthiwe ezishintshayo.
  • Sicela ufake iphasiwedi ngendlela efanele.
  • Iya ku Ifayela > Ulwazi > Vikela ibhuku > Bethela ngephasiwedi.
  • Susa iphasiwedi ngokufaka entsha intambo engenalutho emkhakheni ofanayo.
  • Londoloza ifayela ngaphandle kokuvikelwa futhi usebenzise uxhumano lwe-SQL njengoba kuchazwe esigabeni esidlule.
  Ungarekhoda Kanjani Imihlangano Emaqenjini E-Microsoft: Gcwalisa Umhlahlandlela, Amathiphu, Namathuluzi

Ukufunda okuqukethwe kuspredishithi nge-SQL

Ukuze uthole yonke idatha kusuka kushidi le-Excel, ungasebenzisa umbuzo olandelayo we-SQL:

SELECT * FROM [Hoja1$]

Uma ufuna ukubuyisa kuphela imigqa ehlangabezana nomgomo othile , ungasebenzisa umbuzo onesigaba esithi WHERE :

SELECT * FROM [Hoja1$] WHERE [NombreColumna] = 'Ejemplo'

Susa idatha kumugqa ku-Excel usebenzisa i-SQL

Ngokungafani nesizindalwazi sendabuko, i-Excel ayivumeli ukusetshenziswa kwe -DELETE kumibuzo ye-SQL. Kodwa-ke, umugqa ungashintshwa ngokusetha amanani weseli ku-null.

UPDATE [Hoja1$] SET [Columna1]=NULL, [Columna2]=NULL WHERE [Columna1]='Ejemplo'

Hlunga ulwazi ngokungafaki umugqa othile

Uma udinga ukuthola wonke amarekhodi kusuka kushidi le-Excel ngaphandle kwalawo anenani elithile kukholomu , ungasebenzisa umbuzo olandelayo we-SQL:

SELECT * FROM [Hoja1$] WHERE [Columna1] IS NOT NULL OR [Columna2] IS NOT NULL

Ukusebenzisa imibuzo ye-SQL kusuka ku-Excel ngamaseva axhunyiwe

Enye inketho ethuthukisiwe yokusebenzisa imibuzo ye-SQL ku-Excel ukusebenzisa amaseva axhunyiwe . Ungaxhuma i-Excel ku-SQL Server bese usebenzisa imibuzo ngqo kusuka kusizindalwazi usebenzisa izinyathelo ezilandelayo:

  • Setha i- Umthombo wedatha ye-OLE DB ku-SQL Server.
  • Engeza iseva exhunywe ku- sp_addlinkedserver inqubo egciniwe.
  • Yenza imibuzo ngokuqondile kusuka ku-SQL Server usebenzisa i-Excel njenge-a umthombo wedatha.

Isibonelo sombuzo we-SQL kuseva exhunyiwe:

SELECT * FROM OPENQUERY(ExcelServ,''SELECT * FROM [Hoja1$]'')

Ukusebenzisa ukuguquguquka kwe-SQL ngaphakathi kwe-Excel kuvumela ukuphathwa kwedatha okuphumelelayo kakhudlwana, kunciphisa isikhathi esidingekayo sokuhlunga nokuhlaziya okuyinkimbinkimbi.

  Ukuqoqa Idatha Yomphakathi Ngamafomu kanye Nokuyihlaziya ku-Excel: Umhlahlandlela Ophelele
Isaka

Isaac

Umbhali oshisekayo ngomhlaba wamabhayithi nobuchwepheshe ngokujwayelekile. Ngiyathanda ukwabelana ngolwazi lwami ngokubhala, futhi yilokho engizokwenza kule bhulogi, ngikubonise zonke izinto ezithakazelisayo kakhulu ngamagajethi, isofthiwe, ihadiwe, izitayela zobuchwepheshe, nokuningi. Inhloso yami ukukusiza ukuthi uzulazule emhlabeni wedijithali ngendlela elula nejabulisayo.