Delen via

Gegevens importeren uit Excel of gegevens exporteren naar Excel met SQL Server Integration Services (SSIS)

van toepassing op:SQL Server SSIS Integration Runtime in Azure Data Factory

In dit artikel worden de verbindingsgegevens beschreven die u moet opgeven en de instellingen die u moet configureren om gegevens uit Excel te importeren of gegevens te exporteren naar Excel met SSIS (SQL Server Integration Services).

De volgende secties bevatten de informatie die u nodig hebt om Excel te gebruiken met SSIS en om veelvoorkomende problemen te begrijpen en op te lossen:

Hulpprogramma's die u kunt gebruiken

U kunt gegevens importeren uit Excel of gegevens exporteren naar Excel met SSIS met behulp van een van de volgende hulpprogramma's:

De bestanden ophalen die u nodig hebt om verbinding te maken met Excel

Voordat u gegevens uit Excel kunt importeren of gegevens kunt exporteren naar Excel met SSIS, moet u mogelijk de connectiviteitsonderdelen voor Excel downloaden als ze nog niet zijn geïnstalleerd. De connectiviteitsonderdelen voor Excel zijn niet standaard geïnstalleerd.

Gebruik de tabel in De access ODBC-, OLEDB- of DAO-interfaces buiten Office Klik-en-Klaar niet gebruiken om te begrijpen of er extra onderdelen nodig zijn voor uw omgeving.

Notitie: De Office-systeemstuurprogramma's worden alleen ondersteund in bepaalde scenario's. Raadpleeg overwegingen voor automatisering aan de serverzijde van Office voor specifieke richtlijnen.

Excel opgeven als uw gegevensbron

De eerste stap is om aan te geven dat u verbinding wilt maken met Excel.

In SSIS

Maak in SSIS een Excel Connection Manager om verbinding te maken met het Excel-bron- of doelbestand. Er zijn verschillende manieren om verbindingsbeheer te maken:

  • Klik in het gebied Verbindingsbeheer met de rechtermuisknop en selecteer Nieuwe verbinding. Selecteer EXCEL en vervolgens Toevoegen in het dialoogvenster SSIS-verbindingsbeheer.

  • Selecteer Nieuwe verbinding in het SSIS-menu. Selecteer EXCEL en vervolgens Toevoegen in het dialoogvenster SSIS-verbindingsbeheer.

  • Maak het verbindingsbeheer tegelijkertijd dat u de Excel-bron of de Excel-bestemming configureert op de pagina Verbindingsbeheer van de Excel-broneditor of van de Excel-doeleditor.

In de wizard IMPORTEREN en exporteren van SQL Server

Selecteer in de wizard Importeren en exporteren op de pagina Een gegevensbron kiezen of Kies een doelpaginaMicrosoft Excel in de lijst Met gegevensbronnen .

Als Excel niet wordt weergegeven in de lijst met gegevensbronnen, controleert u of u de 32-bits wizard uitvoert. De Excel-connectiviteitsonderdelen zijn doorgaans 32-bits bestanden en zijn niet zichtbaar in de 64-bits wizard.

Excel-bestand en -bestandspad

Het eerste stukje informatie dat u moet opgeven, is het pad en de bestandsnaam voor het Excel-bestand. U geeft deze informatie op in de Editor van Excel Connection Manager in een SSIS-pakket of op de pagina Een gegevensbron kiezen of Kies een doelpagina van de wizard Importeren en exporteren.

Voer het pad en de bestandsnaam in de volgende indeling in:

  • Voor een bestand op de lokale computer ,C:\TestData.xlsx.

  • Voor een bestand op een netwerkshare , \\Sales\Data\TestData.xlsx.

Of selecteer Bladeren om het werkblad te zoeken met behulp van het dialoogvenster Openen .

Belangrijk

U kunt geen verbinding maken met een Excel-bestand dat met een wachtwoord is beveiligd.

Excel-versie

Het tweede stukje informatie dat u moet opgeven, is de versie van het Excel-bestand. U geeft deze informatie op in de Editor van Excel Connection Manager in een SSIS-pakket of op de pagina Een gegevensbron kiezen of Kies een doelpagina van de wizard Importeren en exporteren.

Selecteer de versie van Microsoft Excel die is gebruikt voor het maken van het bestand of een andere compatibele versie. Als u bijvoorbeeld problemen hebt met het installeren van de 2016-connectiviteitsonderdelen, kunt u de 2010-onderdelen installeren en Microsoft Excel 2007-2010 selecteren in deze lijst.

Mogelijk kunt u geen nieuwere Excel-versies selecteren in de lijst als u alleen oudere versies van de connectiviteitsonderdelen hebt geïnstalleerd. De lijst met Excel-versies bevat alle versies van Excel die worden ondersteund door SSIS. De aanwezigheid van items in deze lijst geeft niet aan dat de vereiste connectiviteitsonderdelen zijn geïnstalleerd. Microsoft Excel 2016 wordt bijvoorbeeld weergegeven in de lijst, zelfs als u de onderdelen van 2016-connectiviteit niet hebt geïnstalleerd.

Opmerking

Vanaf SQL Server Management Studio 21 en SQL Server 2025 ondersteunt de wizard Importeren en exporteren alleen een 64-bits omgeving. Microsoft.JET.OLEDB.4.0 werkt alleen in 32-bits omgevingen.

Als u de wizard Importeren en exporteren voor Excel-bestanden wilt gebruiken in een 64-bits omgeving, downloadt u de Microsoft Access Database Engine 2016 Redistributable om de Microsoft.ACE.OLEDB.16.0-provider te installeren. Selecteer vervolgens Microsoft Excel 2016 als excel-versie in de sql Server-wizard Importeren en exporteren, zoals de volgende schermopname:

Schermopname van de wizard Excel importeren en exporteren in SSMS met Microsoft Excel 2016 geselecteerd.

De provider Microsoft.ACE.OLEDB.16.0 ondersteunt Excel-bestanden die zijn gemaakt door Excel 97-2003 (.xsl) en Excel 2007-2010, 2016 (.xlsx).

Eerste rij heeft kolomnamen

Als u gegevens importeert uit Excel, is de volgende stap om aan te geven of de eerste rij van de gegevens kolomnamen bevat. U geeft deze informatie op in de Editor van Excel Connection Manager in een SSIS-pakket of op de pagina Een gegevensbron kiezen van de wizard Importeren en exporteren.

  • Als u deze optie uitschakelt omdat de brongegevens geen kolomnamen bevatten, gebruikt de wizard F1, F2 enzovoort als kolomkoppen.
  • Als de gegevens kolomnamen bevatten, maar u deze optie uitschakelt, worden de kolomnamen geïmporteerd als de eerste rij met gegevens.
  • Als de gegevens geen kolomnamen bevatten, maar u deze optie inschakelt, gebruikt de wizard de eerste rij brongegevens als kolomnamen. In dit geval wordt de eerste rij met brongegevens niet meer opgenomen in de gegevens zelf.

Als u gegevens exporteert uit Excel en u deze optie inschakelt, bevat de eerste rij met geëxporteerde gegevens de kolomnamen.

Werkbladen en bereiken

Er zijn drie typen Excel-objecten die u kunt gebruiken als bron of doel voor uw gegevens: een werkblad, een benoemd bereik of een niet-benoemd bereik met cellen die u opgeeft met het adres.

  • Werkblad. Als u een werkblad wilt opgeven, voegt u het $ teken toe aan het einde van de bladnaam en voegt u scheidingstekens toe rond de tekenreeks, bijvoorbeeld [Blad1$]. Of zoek naar een naam die eindigt op het $ teken in de lijst met bestaande tabellen en weergaven.

  • Benoemd bereik. Als u een benoemd bereik wilt opgeven, geeft u de bereiknaam op, bijvoorbeeld MyDataRange. Of zoek naar een naam die niet eindigt op het $ teken in de lijst met bestaande tabellen en weergaven.

  • Niet-benoemd bereik. Als u een celbereik wilt opgeven dat u niet hebt benoemd, voegt u het $-teken toe aan het einde van de bladnaam, voegt u de bereikspecificatie toe en voegt u scheidingstekens toe rond de tekenreeks, bijvoorbeeld [Blad1$A1:B4].

Als u het type Excel-object wilt selecteren of opgeven dat u wilt gebruiken als bron of bestemming voor uw gegevens, voert u een van de volgende handelingen uit:

In SSIS

Voer in SSIS op de pagina Verbindingsbeheer van de Excel-broneditor of van de Excel-doeleditor een van de volgende handelingen uit:

  • Als u een werkblad of een benoemd bereik wilt gebruiken, selecteert u Tabel of weergave als de modus Gegevenstoegang. Selecteer vervolgens in de lijst Naam van het Excel-werkblad het werkblad of benoemd bereik.

  • Als u een niet-benoemd bereik wilt gebruiken dat u opgeeft met het bijbehorende adres, selecteert u de SQL-opdracht als de modus Gegevenstoegang. Voer vervolgens in het tekstveld van de SQL-opdracht een query in zoals in het volgende voorbeeld:

    SELECT * FROM [Sheet1$A1:B5]

In de wizard IMPORTEREN en exporteren van SQL Server

Voer in de wizard Importeren en exporteren een van de volgende handelingen uit:

  • Wanneer u importeert vanuit Excel, voert u een van de volgende handelingen uit:

    • Als u een werkblad of een benoemd bereik wilt gebruiken, selecteert u op de pagina Tabelkopie of query opgevengegevens uit een of meer tabellen of weergaven. Selecteer vervolgens op de pagina Brontabellen en weergaven selecteren in de kolom Bron de bronwerkbladen en benoemde bereiken.

    • Als u een niet-benoemd bereik wilt gebruiken dat u opgeeft met het adres, selecteert u op de pagina Tabel kopiëren of query opgeveneen query schrijven om de gegevens op te geven die moeten worden overgedragen. Geef vervolgens op de pagina Een bronquery opgeven een query op die vergelijkbaar is met het volgende voorbeeld:

      SELECT * FROM [Sheet1$A1:B5]

  • Wanneer u exporteert naar Excel, voert u een van de volgende handelingen uit:

    • Als u een werkblad of een benoemd bereik wilt gebruiken, selecteert u op de pagina Brontabellen en weergaven selecteren in de kolom Doel de doelwerkbladen en benoemde bereiken.

    • Als u een niet-benoemd bereik wilt gebruiken dat u opgeeft met het bijbehorende adres, voert u op de pagina Brontabellen en weergaven selecteren in de kolom Doel het bereik in de volgende indeling in zonder scheidingstekens: Sheet1$A1:B5 De wizard voegt de scheidingstekens toe.

Nadat u de Excel-objecten hebt geselecteerd of ingevoerd die u wilt importeren of exporteren, kunt u ook de volgende dingen doen op de pagina Brontabellen en weergaven selecteren van de wizard:

  • Controleer kolomtoewijzingen tussen bron en doel door Toewijzingen bewerken te selecteren.

  • Voorbeeldgegevens bekijken om er zeker van te zijn dat dit wat u verwacht door Voorbeeld te selecteren.

Problemen met gegevenstypen

Gegevenstypen

Het Excel-stuurprogramma herkent slechts een beperkte set gegevenstypen. Alle numerieke kolommen worden bijvoorbeeld geïnterpreteerd als dubbele waarden (DT_R8) en alle tekenreekskolommen (behalve memokolommen) worden geïnterpreteerd als Unicode-tekenreeksen van 255 tekens (DT_WSTR). SSIS wijst de Excel-gegevenstypen als volgt toe:

  • Numeriek - dubbele precisie float (DT_R8)

  • Valuta - valuta (DT_CY)

  • Booleaanse waarde - Booleaanse waarde (DT_BOOL)

  • Datum/tijd - datum/tijd (DT_DATE)

  • Tekenreeks - Unicode-tekenreeks, lengte 255 (DT_WSTR)

  • Memo - Unicode-tekststroom (DT_NTEXT)

Gegevenstype en lengteconversies

SSIS converteert geen gegevenstypen impliciet. Als gevolg hiervan moet u mogelijk afgeleide kolom- of gegevensconversietransformaties gebruiken om Excel-gegevens expliciet te converteren voordat u deze in een andere bestemming dan Excel laadt of om gegevens te converteren van een andere bron dan Excel voordat u deze in een Excel-bestemming laadt.

Hier volgen enkele voorbeelden van de conversies die mogelijk vereist zijn:

  • Conversie tussen Unicode Excel-tekenreekskolommen en niet-Unicode-tekenreekskolommen met specifieke codepagina.

  • Conversie tussen excel-tekenreekskolommen met 255 tekens en tekenreekskolommen met verschillende lengten.

  • Conversie tussen numerieke Excel-kolommen met dubbele precisie en numerieke kolommen van andere typen.

Aanbeveling

Als u de wizard Importeren en exporteren gebruikt en sommige van deze conversies zijn vereist voor uw gegevens, configureert de wizard de benodigde conversies voor u. Zelfs als u een SSIS-pakket wilt gebruiken, kan het handig zijn om het eerste pakket te maken met behulp van de wizard Importeren en exporteren. Laat de wizard verbindingsmanagers, bronnen, transformaties en bestemmingen voor u maken en configureren.

Problemen met importeren

Lege rijen

Wanneer u een werkblad of een benoemd bereik opgeeft als bron, leest het stuurprogramma het aaneengesloten blok cellen dat begint met de eerste niet-lege cel in de linkerbovenhoek van het werkblad of bereik. Als gevolg hiervan hoeven uw gegevens niet te beginnen in rij 1, maar u kunt geen lege rijen in de brongegevens hebben. U kunt bijvoorbeeld geen lege rij tussen de kolomkoppen en de gegevensrijen hebben, of een titel gevolgd door lege rijen boven aan het werkblad.

Als er lege rijen boven uw gegevens staan, kunt u de gegevens niet als werkblad opvragen. In Excel moet u uw gegevensbereik selecteren en een naam toewijzen aan het bereik en vervolgens een query uitvoeren op het benoemde bereik in plaats van het werkblad.

Ontbrekende waarden

Het Excel-stuurprogramma leest een bepaald aantal rijen (standaard acht rijen) in de opgegeven bron om het gegevenstype van elke kolom te raden. Wanneer een kolom gemengde gegevenstypen bevat, met name numerieke gegevens die zijn gemengd met tekstgegevens, besluit het stuurprogramma voor het merendeel van het gegevenstype en retourneert null-waarden voor cellen die gegevens van het andere type bevatten. (In een gelijkspel wint het numerieke type.) De meeste opties voor celopmaak in het Excel-werkblad lijken niet van invloed op deze bepaling van het gegevenstype.

U kunt dit gedrag van het Excel-stuurprogramma wijzigen door de importmodus op te geven om alle waarden als tekst te importeren. Als u de importmodus wilt opgeven, voegt u toe IMEX=1 aan de waarde van uitgebreide eigenschappen in de verbindingsreeks van Excel-verbindingsbeheer in het venster Eigenschappen.

Afgekapte tekst

Wanneer het stuurprogramma bepaalt dat een Excel-kolom tekstgegevens bevat, selecteert het stuurprogramma het gegevenstype (tekenreeks of memo) op basis van de langste waarde die wordt gebruikt. Als het stuurprogramma geen waarden detecteert die langer zijn dan 255 tekens in de rijen die door het stuurprogramma worden gebruikt, wordt de kolom behandeld als een tekenreekskolom van 255 tekens in plaats van een memokolom. Daarom kunnen waarden langer dan 255 tekens worden afgekapt.

Als u gegevens uit een memokolom wilt importeren zonder afkapping, hebt u twee opties:

  • Zorg ervoor dat de memokolom in ten minste één van de gemonsterde rijen een waarde bevat die langer is dan 255 tekens

  • Verhoog het aantal rijen dat door het stuurprogramma wordt genomen om een dergelijke rij op te nemen. U kunt het aantal rijen verhogen dat wordt genomen door de waarde van TypeGuessRows onder de volgende registersleutel te verhogen:

Herdistribueerbare onderdelenversie Registersleutel
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

Problemen met exporteren

Een nieuw doelbestand maken

In SSIS

Maak een Excel-verbindingsbeheer met het pad en de bestandsnaam van het nieuwe Excel-bestand dat u wilt maken. Selecteer vervolgens in de Excel-doeleditor voor de naam van het Excel-werkbladde optie Nieuw om het doelwerkblad te maken. Op dit moment maakt SSIS het nieuwe Excel-bestand met het opgegeven werkblad.

In de wizard IMPORTEREN en exporteren van SQL Server

Selecteer Bladeren op de pagina Een bestemming kiezen. Navigeer in het dialoogvenster Openen naar de map waarin u het nieuwe Excel-bestand wilt maken, geef een naam op voor het nieuwe bestand en selecteer Vervolgens Openen.

Exporteren naar een groot genoeg bereik

Wanneer u een bereik opgeeft als doel, treedt er een fout op als het bereik minder kolommen heeft dan de brongegevens. Als het bereik dat u opgeeft echter minder rijen heeft dan de brongegevens, blijft de wizard rijen zonder fouten schrijven en wordt de bereikdefinitie uitgebreid zodat deze overeenkomt met het nieuwe aantal rijen.

Lange tekstwaarden exporteren

Voordat u tekenreeksen van meer dan 255 tekens kunt opslaan in een Excel-kolom, moet het stuurprogramma het gegevenstype van de doelkolom herkennen als memo en niet als tekenreeks.

  • Als een bestaande doeltabel al rijen met gegevens bevat, moeten de eerste paar rijen die door het stuurprogramma worden gesampleerd ten minste één exemplaar van een waarde bevatten die langer is dan 255 tekens in de memokolom.

Verwante inhoud