Dela via

Importera data från Excel eller exportera data till Excel med SQL Server Integration Services (SSIS)

gäller för:SQL Server SSIS Integration Runtime i Azure Data Factory

Den här artikeln beskriver anslutningsinformationen som du måste ange och de inställningar som du måste konfigurera för att importera data från Excel eller exportera data till Excel med SQL Server Integration Services (SSIS).

Följande avsnitt innehåller den information du behöver för att använda Excel med SSIS och för att förstå och felsöka vanliga problem:

Verktyg som du kan använda

Du kan importera data från Excel eller exportera data till Excel med SSIS med något av följande verktyg:

Hämta de filer som du behöver för att ansluta till Excel

Innan du kan importera data från Excel eller exportera data till Excel med SSIS kan du behöva ladda ned anslutningskomponenterna för Excel om de inte redan är installerade. Anslutningskomponenterna för Excel installeras inte som standard.

Använd tabellen i Det går inte att använda Access ODBC-, OLEDB- eller DAO-gränssnitten utanför Office Klicka-och-kör för att förstå om ytterligare komponenter behövs för din miljö.

Not: Office-systemdrivrutiner stöds endast i vissa scenarier. Mer information finns i Överväganden för automatisering av Office på serversidan .

Ange Excel som datakälla

Det första steget är att ange att du vill ansluta till Excel.

I SSIS

I SSIS skapar du en Excel Connection Manager för att ansluta till Excel-källan eller målfilen. Det finns flera sätt att skapa anslutningshanteraren:

  • I området Anslutningshanterare högerklickar du och väljer Ny anslutning. I dialogrutan Lägg till SSIS Connection Manager väljer du EXCEL och sedan Lägg till.

  • På SSIS-menyn väljer du Ny anslutning. I dialogrutan Lägg till SSIS Connection Manager väljer du EXCEL och sedan Lägg till.

  • Skapa anslutningshanteraren samtidigt som du konfigurerar Excel-källan eller Excel-målet på sidan Anslutningshanterare i Excel-källredigeraren eller i Excel-målredigeraren.

I guiden Importera och exportera SQL Server

I guiden Importera och exportera går du till sidan Välj en datakälla eller Välj ett mål och väljer Microsoft Excel i listan Datakälla .

Om du inte ser Excel i listan över datakällor kontrollerar du att du kör 32-bitarsguiden. Excel-anslutningskomponenterna är vanligtvis 32-bitarsfiler och visas inte i 64-bitarsguiden.

Excel-fil och filsökväg

Den första informationen som ska anges är sökvägen och filnamnet för Excel-filen. Du anger den här informationen i Excel Connection Manager-redigeraren i ett SSIS-paket, eller på sidan Välj en datakälla eller Välj ett mål i guiden Importera och exportera.

Ange sökvägen och filnamnet i följande format:

  • För en fil på den lokala datorn C:\TestData.xlsx.

  • För en fil på en nätverksresurs \Sales\Data\TestData.xlsx\ .

Eller välj Bläddra för att hitta kalkylbladet med hjälp av dialogrutan Öppna .

Viktigt!

Du kan inte ansluta till en lösenordsskyddad Excel-fil.

Excel-version

Den andra informationen som ska anges är versionen av Excel-filen. Du anger den här informationen i Excel Connection Manager-redigeraren i ett SSIS-paket, eller på sidan Välj en datakälla eller Välj ett mål i guiden Importera och exportera.

Välj den version av Microsoft Excel som användes för att skapa filen eller en annan kompatibel version. Om du till exempel hade problem med att installera 2016-anslutningskomponenterna kan du installera 2010-komponenterna och välja Microsoft Excel 2007-2010 i den här listan.

Du kanske inte kan välja nyare Excel-versioner i listan om du bara har äldre versioner av anslutningskomponenterna installerade. Listan över Excel-versioner innehåller alla versioner av Excel som stöds av SSIS. Förekomsten av objekt i den här listan anger inte att de nödvändiga anslutningskomponenterna är installerade. Till exempel visas Microsoft Excel 2016 i listan även om du inte har installerat 2016-anslutningskomponenterna.

Anmärkning

Från och med SQL Server Management Studio 21 och SQL Server 2025 stöder guiden Importera och exportera endast en 64-bitars miljö. Microsoft.JET.OLEDB.4.0 fungerar bara i 32-bitarsmiljöer.

Om du vill använda guiden Importera och exportera för Excel-filer i en 64-bitars miljö laddar du ned Microsoft Access Database Engine 2016 Redistributable för att installera Microsoft.ACE.OLEDB.16.0-providern. Välj sedan Microsoft Excel 2016 som Excel-version i guiden Importera och exportera SQL Server, till exempel följande skärmbild:

Skärmbild av guiden Importera och exportera Excel i SSMS med Microsoft Excel 2016 markerat.

Microsoft.ACE.OLEDB.16.0-providern stöder Excel-filer som skapats av Excel 97-2003 (.xsl) och Excel 2007-2010, 2016 (.xlsx).

Första raden har kolumnnamn

Om du importerar data från Excel är nästa steg att ange om den första raden i data innehåller kolumnnamn. Du anger den här informationen i Excel Connection Manager-redigeraren i ett SSIS-paket eller på sidan Välj en datakälla i guiden Importera och exportera.

  • Om du inaktiverar det här alternativet eftersom källdata inte innehåller kolumnnamn använder guiden F1, F2 och så vidare som kolumnrubriker.
  • Om data innehåller kolumnnamn, men du inaktiverar det här alternativet, importerar guiden kolumnnamnen som den första dataraden.
  • Om data inte innehåller kolumnnamn, men du aktiverar det här alternativet, använder guiden den första raden med källdata som kolumnnamn. I det här fallet ingår inte längre den första raden med källdata i själva data.

Om du exporterar data från Excel och aktiverar det här alternativet innehåller den första raden med exporterade data kolumnnamnen.

Kalkylblad och intervall

Det finns tre typer av Excel-objekt som du kan använda som källa eller mål för dina data: ett kalkylblad, ett namngivet område eller ett namnlöst cellområde som du anger med dess adress.

  • Kalkylblad. Om du vill ange ett kalkylblad lägger du till $ tecknet i slutet av bladnamnet och lägger till avgränsare runt strängen , till exempel [Sheet1$]. Eller leta efter ett namn som slutar med $ tecknet i listan över befintliga tabeller och vyer.

  • Namngivet område. Ange ett namngivet område genom att ange intervallnamnet , till exempel MyDataRange. Eller leta efter ett namn som inte slutar med $ tecknet i listan över befintliga tabeller och vyer.

  • Namnlöst intervall. Om du vill ange ett cellområde som du inte har namngett lägger du till $-tecknet i slutet av bladnamnet, lägger till intervallspecifikationen och lägger till avgränsare runt strängen , till exempel [Sheet1$A1:B4].

Om du vill välja eller ange vilken typ av Excel-objekt som du vill använda som källa eller mål för dina data gör du något av följande:

I SSIS

I SSIS gör du något av följande på sidan Anslutningshanteraren i Excel-källredigeraren eller i Excel-målredigeraren:

  • Om du vill använda ett kalkylblad eller ett namngivet område väljer du Tabell eller vy som dataåtkomstläge. I listan Namn på Excel-bladet väljer du sedan kalkylbladet eller det namngivna området.

  • Om du vill använda ett namnlöst intervall som du anger med dess adress väljer du SQL-kommandot som dataåtkomstläge. I fältet SQL-kommandotext anger du sedan en fråga som i följande exempel:

    SELECT * FROM [Sheet1$A1:B5]

I guiden Importera och exportera SQL Server

Gör något av följande i guiden Importera och exportera:

  • När du importerar från Excel gör du något av följande:

    • Om du vill använda ett kalkylblad eller ett namngivet område går du till sidan Ange tabellkopiering eller fråga och väljer Kopiera data från en eller flera tabeller eller vyer. På sidan Välj källtabeller och vyer i kolumnen Källa väljer du sedan källkalkylbladen och namngivna intervall.

    • Om du vill använda ett namnlöst intervall som du anger med dess adress går du till sidan Ange tabellkopiering eller fråga och väljer Skriv en fråga för att ange vilka data som ska överföras. På sidan Ange en källfråga anger du sedan en fråga som liknar följande exempel:

      SELECT * FROM [Sheet1$A1:B5]

  • När du exporterar till Excel gör du något av följande:

    • Om du vill använda ett kalkylblad eller ett namngivet område går du till sidan Välj källtabeller och vyer i kolumnen Mål och väljer målkalkylbladen och namngivna intervall.

    • Om du vill använda ett namnlöst intervall som du anger med dess adress går du till sidan Välj källtabeller och vyer i kolumnen Mål och anger intervallet i följande format utan avgränsare: Sheet1$A1:B5. Guiden lägger till avgränsarna.

När du har valt eller angett De Excel-objekt som ska importeras eller exporteras kan du också göra följande på sidan Välj källtabeller och vyer i guiden:

  • Granska kolumnmappningar mellan källa och mål genom att välja Redigera mappningar.

  • Förhandsgranska exempeldata för att se till att det är vad du förväntar dig genom att välja Förhandsversion.

Problem med datatyper

Datatyper

Excel-drivrutinen känner bara igen en begränsad uppsättning datatyper. Till exempel tolkas alla numeriska kolumner som dubblar (DT_R8) och alla strängkolumner (förutom PM-kolumner) tolkas som Unicode-strängar med 255 tecken (DT_WSTR). SSIS mappar Excel-datatyperna enligt följande:

  • Numerisk – flyttal med dubbel precision (DT_R8)

  • Valuta – valuta (DT_CY)

  • Boolesk – boolesk (DT_BOOL)

  • Datum/tid – datetime (DT_DATE)

  • Sträng – Unicode-sträng, längd 255 (DT_WSTR)

  • Pm – Unicode-textström (DT_NTEXT)

Datatyp och längdkonverteringar

SSIS konverterar inte implicit datatyper. Därför kan du behöva använda transformeringar för härledd kolumn eller datakonvertering för att konvertera Excel-data explicit innan de läses in till ett annat mål än Excel, eller för att konvertera data från en annan källa än Excel innan de läses in till ett Excel-mål.

Här följer några exempel på de konverteringar som kan krävas:

  • Konvertering mellan Unicode Excel-strängkolumner och icke-Unicode-strängkolumner med specifik kodsida.

  • Konvertering mellan Excel-strängkolumner med 255 tecken och strängkolumner med olika längd.

  • Konvertering mellan numeriska excelkolumner med dubbel precision och numeriska kolumner av andra typer.

Tips/Råd

Om du använder guiden Importera och exportera och dina data kräver några av dessa konverteringar konfigurerar guiden nödvändiga konverteringar åt dig. Även om du vill använda ett SSIS-paket kan det därför vara användbart att skapa det första paketet med hjälp av guiden Importera och exportera. Låt guiden skapa och konfigurera anslutningshanterare, källor, transformeringar och mål åt dig.

Problem med import

Tomma rader

När du anger ett kalkylblad eller ett namngivet område som källa läser drivrutinen det sammanhängande cellblocket som börjar med den första cellen som inte är tom i det övre vänstra hörnet i kalkylbladet eller området. Det innebär att dina data inte behöver starta på rad 1, men du kan inte ha tomma rader i källdata. Du kan till exempel inte ha en tom rad mellan kolumnrubrikerna och dataraderna, eller en rubrik följt av tomma rader överst i kalkylbladet.

Om det finns tomma rader ovanför dina data kan du inte köra frågor mot data som ett kalkylblad. I Excel måste du välja ditt dataområde och tilldela ett namn till intervallet och sedan fråga det namngivna området i stället för kalkylbladet.

Saknade värden

Excel-drivrutinen läser ett visst antal rader (som standard åtta rader) i den angivna källan för att gissa datatypen för varje kolumn. När en kolumn verkar innehålla blandade datatyper, särskilt numeriska data som blandas med textdata, bestämmer sig drivrutinen för majoritetens datatyp och returnerar null-värden för celler som innehåller data av den andra typen. (Vid oavgjort vinner den numeriska typen.) De flesta alternativ för cellformatering i Excel-kalkylbladet verkar inte påverka den här datatypsbestämningen.

Du kan ändra det här beteendet för Excel-drivrutinen genom att ange Importläge för att importera alla värden som text. Om du vill ange importläge lägger du IMEX=1 till värdet för Utökade egenskaper i anslutningssträngen för Excel-anslutningshanteraren i fönstret Egenskaper.

Trunkerad text

När drivrutinen fastställer att en Excel-kolumn innehåller textdata väljer drivrutinen datatypen (sträng eller PM) baserat på det längsta värde som den tar exempel på. Om drivrutinen inte identifierar värden som är längre än 255 tecken i raderna som den tar exempel på behandlar den kolumnen som en strängkolumn med 255 tecken i stället för en PM-kolumn. Därför kan värden som är längre än 255 tecken trunkeras.

Om du vill importera data från en PM-kolumn utan trunkering har du två alternativ:

  • Kontrollera att pm-kolumnen i minst en av de samplade raderna innehåller ett värde som är längre än 255 tecken

  • Öka antalet rader som samplas av drivrutinen så att en sådan rad inkluderas. Du kan öka antalet rader som samplas genom att öka värdet för TypeGuessRows under följande registernyckel:

Version av omdistribuerbara komponenter Registernyckel
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Problem med export

Skapa en ny målfil

I SSIS

Skapa en Excel Connection Manager med sökvägen och filnamnet för den nya Excel-fil som du vill skapa. I Excel-målredigeraren för Excel-bladet väljer du Sedan Nytt för att skapa målkalkylbladet. Nu skapar SSIS den nya Excel-filen med det angivna kalkylbladet.

I guiden Importera och exportera SQL Server

På sidan Välj ett mål väljer du Bläddra. I dialogrutan Öppna navigerar du till mappen där du vill att den nya Excel-filen ska skapas, anger ett namn för den nya filen och väljer sedan Öppna.

Exportera till ett tillräckligt stort intervall

När du anger ett intervall som mål uppstår ett fel om intervallet har färre kolumner än källdata. Men om det intervall som du anger har färre rader än källdata fortsätter guiden att skriva rader utan fel och utökar intervalldefinitionen så att den matchar det nya antalet rader.

Exportera långa textvärden

Innan du kan spara strängar som är längre än 255 tecken i en Excel-kolumn måste drivrutinen identifiera datatypen för målkolumnen som PM och inte sträng.

  • Om en befintlig måltabell redan innehåller rader med data måste de första raderna som samplas av drivrutinen innehålla minst en instans av ett värde som är längre än 255 tecken i pm-kolumnen.

Relaterat innehåll