- 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.

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.
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.
Inkomba
- 1 Ungavula kanjani ukuxhumana kwe-SQL ngefayela le-Excel
- 2 Qalisa imibuzo ye-SQL kufayela le-Excel elivikelwe ngephasiwedi
- 3 Ukufunda okuqukethwe kuspredishithi nge-SQL
- 4 Susa idatha kumugqa ku-Excel usebenzisa i-SQL
- 5 Hlunga ulwazi ngokungafaki umugqa othile
- 6 Ukusebenzisa imibuzo ye-SQL kusuka ku-Excel ngamaseva axhunyiwe