hur man exporterar Data från SQL Server till Excel

hur man exporterar Data från SQL Server till Excel

i en tidigare artikel, SQL och Excel: varför du behöver båda visade vi att för många företag är användningen av både SQL Server och Excel ett optimalt tillvägagångssätt för datahantering. För organisationer som använder SQL Server och Excel i stor utsträckning finns det ofta ett behov av att exportera data från SQL Server till Excel. De vanligaste orsakerna till att få data till Excel är att:

• producera datavisualiseringar som diagram och grafer för användning i rapporter
• aggregera eller analysera data i pivotdiagram
• samla data från flera källor för vidare transformation
• gör data tillgängliga för icke-databasanvändare för att visa eller bearbeta

i den här artikeln ska vi titta på hur du använder data i pivotdiagram exportera data från SQL Server till Excel. Den första metoden använder SQL Server Import and Export Wizard, som kan användas för att exportera data till ett antal destinationstyper, inte bara Excel. Den andra metoden använder inbyggd funktionalitet i Excel. Den tredje metoden använder SQL Spreads-tillägget för Excel, vilket ger funktionalitet utöver exportprocessen. Det finns för-och nackdelar med varje metod, som vi sammanfattar i slutet av artikeln.

Metod (1): Exportera med SQL Server Import och Export Wizard

SQL Server Import och Export Wizard kan startas inifrån SQL Server Management Studio eller som en fristående program.

från SQL Server Management Studio, klicka på den databas som du vill exportera data från. Vi använder demo-databasen i vårt exempel, så klicka på det och högerklicka och välj uppgifter > exportera Data.

välkomstsidan visas, och du kan gå vidare och klicka på ’Nästa’.

Välj datakälla på nästa skärm. Om du vill exportera från SQL Server väljer du ’SQL Server Native Client’.

när du har valt ’SQL Server Native Client’ kommer informationen om datakällan att fyllas i av guiden.

klicka på ’Nästa’ för att gå till nästa skärm, där du ska välja destination för exporten, som för oss är Excel.

när du väljer Excel som målval måste du ange platsen för Excel-filen. När du sedan klickar på ’Nästa’ kan du se följande meddelande:

åtgärden kunde inte slutföras.
ytterligare INFORMATION:
den ’Microsoft.ESS.OLEDB.16.0 ’ leverantören är inte registrerad på den lokala maskinen. (Systemet.Data)

anledningen till att detta meddelande visas är att när du startar SQL Server Import and Export Wizard från SSMS startas en 32-bitarsversion av guiden (eftersom SSMS är 32-bitars) och ditt operativsystem är 64-bitars. Det finns mer information om detta här.

för att komma runt detta måste du stänga SSMS och gå till Windows Start-menyn och söka efter 64-bitarsversionen av SQL Server Import and Export Wizard från Start-menyn. Observera att den här versionen endast kommer att installeras på din dator om du har installerat SQL Server.

när den fristående versionen av guiden har startat kan du gå igenom stegen som diskuterats hittills, vilket var att ange källan och destinationen för datakopian. Vi måste nu definiera vad vi vill kopiera och (valfritt) konfigurera kopieringsoperationen.

när du har klickat på ’Nästa’ på skärmen ’Välj en Destination’ anger du om du vill exportera en eller flera fullständiga tabeller eller vyer eller en delmängd data via en SQL-fråga. I vårt fall exporterar vi från ett enda bord, så vi kan lämna standardalternativet valt.

därefter kommer vi att redigera kolumnmappningarna genom att klicka på knappen ’Redigera mappningar…’. På skärmen Kolumnmappningar kan du göra ändringar i namnet och datatypen för kolumnerna som skapas i Excel genom kopieringsåtgärden. I vårt exempel kommer vi att ändra datatypen för kolumnen’ order_date ’ från varchar till DateTime. Vi lämnar de andra som standardinställningen av guiden. Klicka På ’Nästa’. Nästa skärm i guiden sammanfattar att källan är en SQL-tabell som heter försäljning och destinationen kommer att vara till ett ark som heter ’försäljning’ i Excel-arbetsboken. Du kan ändra namnet på arket efter behov.

när du är klar klickar du på ’Ok’ för att stänga dialogrutan och sedan ’nästa’.

om någon av de mappningar som vi angav kanske inte lyckas i exportprocessen visar SQL Server Import and Export Wizard sidan för kartläggning av Granskningsdatatyp. Den här sidan markerar de konverteringar som guiden behöver utföra för att hantera de mappningar som du har angett. Det indikerar också vad guiden ska göra om det finns ett fel under konverteringen. Det kan antingen ignorera felet eller orsaka att exporten misslyckas. I vårt exempel har flera kolumner flaggats: de där det finns en konvertering från nvarchar(max) till dess Excel-ekvivalent, lång Text och även omvandlingen från VarChar till DateTime för kolumnen ’order_date’. Standardinställningen är att guiden misslyckas om det finns ett fel, så vi lämnar det alternativet på plats.

Obs: Denna SQL-import-och exportguiden använder SSIS under huven; med andra ord, varje gång du kör guiden skapar du ett SSIS-paket. Det är därför du får möjlighet att spara den för framtida bruk.Klicka På ’Nästa’. Guiden ger dig nu chansen att spara guiden konfiguration som ett SSIS-paket utöver standardalternativet att köra kopian omedelbart.

vi kommer bara att lämna standardalternativet markerat och klicka sedan på knappen ’Nästa’.

guiden visar nu en sammanfattning av stegen som vi har konfigurerat.

klicka på ’Slutför’, och guiden visar nu den slutliga skärmen, vilket är resultatet av körningen. I vårt fall var utförandet framgångsrikt, och vi kan se att 5484 poster kopierades till ’Query’ – arket i vår Excel-arbetsbok.

vi kan kontrollera att data har exporterats från SQL Server till Excel genom att öppna filen som vi angav och kontrollera ’försäljning’ ark.

nu när data finns i Excel kan vi skapa några visualiseringar eller aggregeringar, eller vilken annan behandling vi vill utföra. Vi kan till exempel skapa ett pivotdiagram för att visa summan per objekt per region.

Metod (2): Exportera med inbyggd funktionalitet i Excel

även om själva metoden är ungefär densamma har Microsoft gjort förbättringar under de senaste utgåvorna för hur du får data från andra källor till Excel. Huvudändringen var tillägget av Power Query – dataimportguiderna och verktygen som en inbyggd komponent i Excel 2016. Dessa nås från Power Query-redigeraren, även om den inte visas som sådan i Excel – bandet-istället är den märkt som Get & Transform Data group på fliken data i Excel-bandet.

stegen som beskrivs nedan gäller för Excel 2016 och därefter.

för att komma igång med exporten från SQL Server till Excel, klicka på Data > hämta Data > från Databas > från SQL Server-databasen.

du uppmanas nästa gång att ange databasservern för att ansluta till och eventuellt ange en databas. Om du inte anger en databas kan du välja bland de tillgängliga på nästa skärm.

Klicka På ’Ok’. Du uppmanas då att ange autentiseringsuppgifter. Standard är Windows, så du kan gå vidare och klicka på ’Anslut’.

nästa skärm låter dig navigera genom databaser och tabeller på databasservern som du angav. Klicka på ’demo’ – databasen för att expandera den och klicka sedan på tabellen ’försäljning’. Data för den valda tabellen visas i det högra fönstret. Vi kunde nu klicka på’ Ladda ’ – knappen för att få data till Excel omedelbart. Alternativt kan vi klicka på ’transformera Data’ för att utföra transformationer på data (t.ex. ändra datatyper, städa upp vissa data, utföra beräkningar). I det här exemplet kommer vi bara att ladda data som det är, så fortsätt och klicka på ’Ladda’.

data exporteras nu från tabellen i SQL Server och laddas in i ett nytt ark i Excel.

det är allt vi behöver göra för att utföra en enkel export av en fullständig tabell från SQL Server till Excel. Bortsett från den enkla procedur som vi har beskrivit här, Power Query data import guider och verktyg ger sätt att ’forma’ data från externa källor. Du kan till exempel ta bort en kolumn, ändra en datatyp, slå samman tabeller för att möta dina specifika behov.

Metod (3): Exportera med SQL Spreads-tillägg för Excel

om du inte redan har SQL Spreads-tillägget för Excel installerat kan du ladda ner en kopia här.

när SQL-spridningar är installerade ser du att den har lagts till som en ny ribbon-flik; gå hit och klicka på Design Mode-knappen.

i SQL Spreads Designer-panelen till höger klickar du på knappen Redigera för att öppna dialogrutan SQL Server connection.

ange namnet på din SQL Server i fältet SQL Server name. Du måste också välja en autentiseringsmetod: Windows-inloggning (Windows-autentisering) eller SQL Server-autentisering (med ett namn och lösenord som ställts in i SQL Server). Windows-autentisering är det säkrare av de två alternativen.

klicka på OK. SQL-spridningar försöker ansluta till databasen. Om anslutningen lyckas visas dina databaser i SQL Spreads Designer-panelen.

vi kan nu expandera demo-databasen och välja försäljningstabellen.

när du klickar på tabellen exporteras data omedelbart från SQL Server och kopieras till ett ark som heter ’försäljning’ i Excel.

det är det! Vi har lyckats exportera data från SQL Server till Excel med bara ett par klick.

det finns flera fler alternativ tillgängliga för oss i SQL Spreads Designer. Till exempel kan vi:

• filtrera data som exporteras från SQL Server
• ange om vissa kolumner ska visas och/eller skrivskyddad
• använd ett uppslagsvärde för att visa text istället för ett ID för en kolumn
• sortera data
• gör uppdateringar i Excel och spara ändringar i SQL Server

vi kommer att titta på några av dessa alternativ i ett framtida blogginlägg.

sammanfattning

att kunna exportera data från SQL Server till Excel är ett vanligt krav i de flesta företag. Vi har tittat på tre metoder för att utföra denna dagliga uppgift. Den första använder SQL Server Import and Export Wizard, som är inbyggd funktionalitet i SQL Server. Den andra metoden använder de inbyggda ’Get & Transform’ – verktygen i Excel. Den tredje använder SQL Spreads-tillägget för Excel.

så vilken metod ska du använda?

om du normalt arbetar med databaser, och SSMS är ditt andra hem, kommer du förmodligen att känna att SQL Server Import och Export Wizard är det bästa alternativet för dig. Om du är en Excel-användare är det vettigt att arbeta inifrån Excel själv för att exportera data från SQL Server. Båda de två Excel-alternativen som vi har tittat på är robusta och enkla att använda, men SQL-spridningar har en tydlig fördel eftersom det tillåter användare att göra uppdateringar av data direkt i Excel och sedan spara data tillbaka till SQL Server. Detta är en stor fördel för de organisationer som gillar det bästa av två världar: enkel användning och förtrogenhet med Excel, med kraften och dataintegriteten i samband med SQL Server.

prova SQL-spridningar för att se hur snabbt och enkelt det är att exportera data från SQL Server till Excel; ladda ner en gratis provversion idag.