Adatok exportálása az SQL Server-ből az Excel-be

Adatok exportálása az SQL Server-ből az Excel-be

egy előző cikkben, SQL és Excel: miért van szüksége mindkettőre, megmutattuk, hogy sok vállalkozás számára az SQL Server és az Excel használata optimális megközelítés az adatkezeléshez. Az SQL Server-t és az Excel-t széles körben használó szervezetek esetében gyakran szükség van az adatok exportálására az SQL Server-ből az Excel-be. Az adatok Excelbe juttatásának leggyakoribb okai a következők:

• adatvizualizációk, például diagramok és grafikonok készítése jelentésekben való használatra
• összesített vagy elemzett adatok pivot diagramokban
• több forrásból származó adatok összevetése további átalakítás céljából
• az adatok elérhetővé tétele az adatbázison kívüli felhasználók számára megtekintés vagy feldolgozás céljából

ebben a cikkben a következőket fogjuk megvizsgálni adatok exportálása az SQL Server-ből az Excel-be. Az első módszer az SQL Server Importálás és exportálás varázslót használja, amely nemcsak az Excel, hanem számos céltípusra is exportálhat adatokat. A második módszer natív funkcionalitást használ az Excelben. A harmadik módszer az SQL Spreads bővítményt használja az Excelhez, amely az exportálási folyamaton kívüli funkciókat biztosít. Az egyes módszereknek vannak előnyei és hátrányai, amelyeket a cikk végén összefoglalunk.

módszer (1): Exportálás SQL Server Importálás és exportálás varázslóval

az SQL Server Importálás és exportálás varázsló elindítható az SQL Server Management Studio alkalmazásból vagy önálló alkalmazásként.

az SQL Server Management Studio alkalmazásban kattintson arra az adatbázisra, amelyből adatokat kíván exportálni. A példánkban a demo adatbázist használjuk, ezért kattintson erre, majd kattintson a jobb gombbal, majd válassza a feladatok > Adatok exportálása lehetőséget.

megjelenik az üdvözlő oldal, és továbbléphet, és kattintson a ‘Tovább’gombra.

a következő képernyőn válassza ki az adatforrást. Az SQL Server Alkalmazásból történő exportáláshoz válassza az’SQL Server Native Client’ lehetőséget.

miután kiválasztotta az ‘SQL Server Native Client’ lehetőséget, a varázsló kitölti az adatforrás adatait.

kattintson a ‘Tovább’ gombra a következő képernyőre lépéshez, ahol kiválaszthatja az Exportálás rendeltetési helyét, amely számunkra az Excel.

amikor az Excel lehetőséget választja rendeltetési helyként, akkor meg kell adnia az Excel fájl helyét. Amikor a ‘Tovább’ gombra kattint, a következő üzenet jelenhet meg:

a művelet nem hajtható végre.
További információ:
a ‘ Microsoft.Ász.OLEDB.16.0 ‘ a szolgáltató nincs regisztrálva a helyi gépen. (Rendszer.Adatok)

az üzenet azért jelenik meg, mert amikor elindítja az SQL Server Importálás és exportálás varázslót SSMS-ből, a varázsló 32 bites verziója indul el (mivel az SSMS 32 bites), az operációs rendszer pedig 64 bites. Erről további információ itt található.

ennek megkerüléséhez be kell zárnia az SSMS-t, és a Windows Start menüjébe kell lépnie, és meg kell keresnie az SQL Server Importálás és exportálás varázsló 64 bites verzióját a Start menüből. Ne feledje, hogy ez a verzió csak akkor lesz telepítve a számítógépre, ha telepítette az SQL Server alkalmazást.

miután a varázsló önálló verziója elindult, az eddig tárgyalt lépéseken keresztül megadhatja az adatmásolás forrását és célját. Most meg kell határoznunk, hogy mit akarunk másolni, és (opcionálisan) konfigurálnunk kell a másolási műveletet.

miután a ‘Úticél kiválasztása’ képernyőn a ‘Tovább’ gombra kattintott, megadja, hogy egy vagy több teljes táblát vagy nézetet, vagy adathalmazt kíván-e exportálni egy SQL lekérdezésen keresztül. Esetünkben egyetlen táblázatból exportálunk,így elhagyhatjuk az alapértelmezett opciót.

ezután szerkesztjük az oszlop-leképezéseket a ‘leképezések szerkesztése…’ gombra kattintva. Az Oszlopleképezések képernyőn módosíthatja az Excelben a másolási művelet által létrehozott oszlopok nevét és adattípusát. Példánkban megváltoztatjuk az ‘order_date’ oszlop adattípusát Varcharról DateTime – re. A többieket hagyjuk a varázsló alapértelmezett beállításaként. Kattintson A ‘Tovább’ Gombra. A varázsló következő képernyője összefoglalja, hogy a forrás egy sales nevű SQL-tábla, a cél pedig az Excel munkafüzet ‘sales’ nevű lapja lesz. Szükség szerint megváltoztathatja a lap nevét.

ha kész, kattintson az ‘Ok’ gombra a párbeszédablak bezárásához, majd a ‘Tovább’gombra.

ha az általunk megadott leképezések bármelyike sikertelen lehet az exportálási folyamatban, az SQL Server Importálás és exportálás varázsló megjeleníti az Adattípus-leképezés áttekintése lapot. Ez az oldal kiemeli azokat a konverziókat, amelyeket a varázslónak végre kell hajtania a megadott leképezések kezeléséhez. Azt is jelzi, hogy a varázsló mit fog tenni, ha egy hiba van az átalakítás során. Vagy figyelmen kívül hagyhatja a hibát, vagy az Exportálás meghiúsulhat. Példánkban több oszlopot jelöltek meg: azokat, ahol az Nvarchar(max) – ről az Excel megfelelőjére, a hosszú szövegre, valamint a VarChar-ról a DateTime-re történő konverziót az ‘order_date’ oszlopra. Az alapértelmezett beállítás az, hogy a varázsló sikertelen, ha hiba van, ezért ezt a lehetőséget a helyén hagyjuk.

Megjegyzés: Ez az SQL Import and Export varázsló SSIS-t használ a motorháztető alatt; más szóval, minden alkalommal, amikor futtatja a varázslót, SSIS-csomagot hoz létre. Ez az oka annak, hogy lehetőséget kap arra, hogy későbbi felhasználásra mentse.Kattintson A ‘Tovább’ Gombra. A varázsló most lehetőséget kínál arra, hogy a varázsló konfigurációját SSIS csomagként mentse a másolat azonnali futtatásának alapértelmezett opciója mellett.

csak hagyjuk az alapértelmezett opciót bejelölve, majd kattintsunk a ‘Tovább’ gombra.

a varázsló most megjeleníti a konfigurált lépések összefoglalását.

kattintson a ‘Befejezés’ gombra, és a varázsló most megjeleníti a végső képernyőt, amely a végrehajtás eredménye. Esetünkben a végrehajtás sikeres volt, és láthatjuk, hogy 5484 rekordot másoltak az Excel munkafüzetünk ‘lekérdezési’ lapjára.

ellenőrizhetjük, hogy az adatokat exportálták-e az SQL Server-ből az Excelbe, ha megnyitjuk a megadott fájlt, és ellenőrizzük az ‘értékesítési’ lapot.

most, hogy az adatok Excelben vannak, létrehozhatunk néhány vizualizációt vagy összesítést, vagy bármilyen más feldolgozást, amelyet végre akarunk hajtani. Például létrehozhatunk egy pivot diagramot, amely régiónként megjeleníti az elemenkénti összeget.

módszer (2): Exportálás natív funkcionalitás használatával az Excelben

bár maga a módszer nagyjából ugyanaz, a Microsoft az utóbbi néhány kiadásban javításokat hajtott végre abban, ahogyan más forrásokból adatokat kap az Excelbe. A fő változás a Power Query adatimportáló varázslók és eszközök hozzáadása volt az Excel 2016 natív összetevőjeként. Ezeket a Power Query szerkesztőből érheti el, bár nem jelenik meg az Excel szalagon – ehelyett a Get & transform Data group címkével rendelkezik az Excel szalag Adatok lapján.

az alábbi lépések érvényesek az Excel 2016 és azon túl.

az SQL Server-ből az Excel-be történő exportálás megkezdéséhez kattintson a Data > Get Data > from Database > From SQL Server Database elemre.

ezután a rendszer kéri, hogy adja meg azt az adatbázis-kiszolgálót, amelyhez csatlakozni kíván, és adott esetben adjon meg egy adatbázist. Ha nem ad meg adatbázist, a következő képernyőn választhat a rendelkezésre álló adatbázisok közül.

Kattintson Az ‘Ok’Gombra. Ezután a rendszer kéri a hitelesítési adatok megadását. Az alapértelmezett a Windows, így folytathatja a ‘Connect’gombot.

a következő képernyőn navigálhat a megadott adatbázis-kiszolgálón lévő adatbázisok és táblák között. Kattintson a’ demo ‘adatbázisra a bővítéshez, majd kattintson az’ értékesítés ‘ táblára. A kiválasztott táblázat adatai a jobb oldali ablakban jelennek meg. Most rákattinthatunk a’ betöltés ‘ gombra, hogy azonnal megkapjuk az adatokat az Excelbe. Alternatív megoldásként rákattinthatunk az ‘adatok átalakítása’ gombra az adatok átalakításának végrehajtásához(pl. Ebben a példában csak úgy töltjük be az adatokat, ahogy vannak, ezért kattintson a ‘betöltés’gombra.

az adatokat most az SQL Server táblázatából exportálják, és az Excel új lapjába töltik be.

ez minden, amit meg kell tennie, hogy végre egy egyszerű export egy teljes táblázatot SQL Server Excel. Az itt leírt egyszerű eljáráson kívül a Power Query adatimport varázslók és eszközök lehetőséget nyújtanak az adatok külső forrásokból történő alakítására. Például eltávolíthat egy oszlopot, módosíthat egy adattípust, egyesítheti a táblákat az Ön egyedi igényeinek megfelelően.

módszer (3): Exportálás az SQL Spreads bővítmény használatával Excelhez

ha még nincs telepítve az SQL Spreads bővítmény Excelhez, itt tölthet le egy példányt.

az SQL spreadek telepítése után látni fogja, hogy új szalag fülként került hozzáadásra; menjen ide, és kattintson a tervezési mód gombra.

a jobb oldali SQL Spreads Designer panelen kattintson a Szerkesztés gombra az SQL Server kapcsolat párbeszédpanel megnyitásához.

írja be az SQL Server nevét az SQL Server name mezőbe. Ki kell választania egy hitelesítési módszert is: Windows-bejelentkezés (Windows-hitelesítés) vagy SQL Server-hitelesítés (az SQL Server-ben beállított név és jelszó használatával). A Windows hitelesítés a két lehetőség közül a biztonságosabb.

kattintson az OK gombra. Az SQL spreadek megpróbálnak csatlakozni az adatbázishoz. Ha a kapcsolat sikeres, az adatbázisok megjelennek az SQL Spreads Designer panelen.

most kibővíthetjük a demo adatbázist, és kiválaszthatjuk az értékesítési táblázatot.

amikor rákattint a táblára, az adatokat azonnal exportálja az SQL Server-ből, és átmásolja az Excel ‘értékesítés’ nevű lapjára.

ez az! Néhány kattintással sikerült exportálni az adatokat az SQL Server-ből az Excel – be.

számos további lehetőség áll rendelkezésünkre az SQL Spreads tervezőben. Például tudjuk:

• az SQL Server kiszolgálóról exportált adatok szűrése
• állítsa be, hogy bizonyos oszlopok megjelenjenek-e és/vagy csak olvashatók-e
• keresési érték használatával szöveget jelenítsen meg egy oszlop azonosítója helyett
• adatok rendezése
• végezzen frissítéseket az Excelben, és mentse a módosításokat az SQL Server rendszerbe

ezeket a lehetőségeket egy jövőbeli blogbejegyzésben megvizsgáljuk.

Összegzés

az adatok exportálása az SQL Server-ből az Excel-be a legtöbb vállalkozásban általános követelmény. Három módszert vizsgáltunk ennek a napi feladatnak a végrehajtására. Az első az SQL Server Importálás és exportálás varázslóját használja, amely az SQL Server natív funkciója. A második módszer a natív ‘Get & Transform’ eszközöket használja az Excelben. A harmadik az SQL Spreads bővítményt használja az Excel számára.

tehát melyik módszert kell használni?

ha általában adatbázisokkal dolgozik, és az SSMS a második otthona, valószínűleg úgy fogja érezni, hogy az SQL Server Importálás és exportálás varázsló a legjobb megoldás az Ön számára. Ha Ön Excel-felhasználó, akkor érdemes az Excel-en belül dolgozni az adatok exportálásához az SQL Server-ből. Az általunk vizsgált két Excel-opció egyaránt robusztus és könnyen használható, de az SQL spread-eknek külön előnye van, mivel lehetővé teszik a felhasználók számára, hogy közvetlenül az Excelben frissítsék az adatokat, majd az adatokat az SQL Server-be mentsék. Ez óriási előnyt jelent azoknak a szervezeteknek, amelyek mindkét világ legjobbjait kedvelik: az Excel egyszerű használata és ismerete, az SQL Server teljesítményével és adatintegritásával.

próbálja ki az SQL spreadeket, hogy lássa, milyen gyors és egyszerű az adatok exportálása az SQL Server-ből az Excel-be; töltsön le egy ingyenes próbaverziót még ma.