Comment faire pour importer des données à partir d'Excel vers SQL Server

Traductions disponibles Traductions disponibles
Numéro d'article: 321686 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Cet article pas à pas montre comment importer des données à partir de feuilles de calcul Microsoft Excel dans les bases de données Microsoft SQL Server à l'aide de diverses méthodes.

Description de la Technique

Les exemples de cet article importer des données Excel à l'aide de :
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Serveurs SQL Server liés
  • Requêtes SQL Server distribuées
  • Objets de données ActiveX (ADO) et le fournisseur Microsoft OLE DB pour SQL Server
  • ADO et le fournisseur Microsoft OLE DB pour Jet 4.0

Configuration requise

La liste suivante met en évidence le matériel recommandé, les logiciels, les infrastructures réseau et les services packs requis :
  • Instance disponible de Microsoft SQL Server 7.0 ou Microsoft SQL Server 2000 ou Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 pour les exemples ADO qui utilisent Visual Basic
Certaines parties de cet article supposent que vous êtes familiarisé avec les rubriques suivantes :
  • Services de Transformation de données
  • Les serveurs liés et requêtes distribuées
  • Développement ADO dans Visual Basic

Exemples

Importer comparé à ajouter

Les exemples d'instructions SQL utilisés dans cet article illustrent les requêtes Create Table qui importer des données Excel dans une nouvelle table SQL Server à l'aide de SELECT...DANS...À partir de la syntaxe. Vous pouvez convertir ces instructions pour ajouter des requêtes à l'aide de INSERT INTO...SÉLECTIONNEZ...FROM tout en continuent de référencer les objets source et de destination comme dans ces exemples de code.

Utiliser DTS ou SSIS

Vous pouvez utiliser l'Assistant Importation de transformation de données (DTS, Data Transformation Services) de SQL Server ou l'importation de SQL Server et l'Assistant Exportation pour importer des données Excel dans des tables SQL Server. Lorsque vous avancerez dans l'Assistant et sélectionner les tables source Excel, rappelez-vous que les noms d'objets Excel sont terminant par un signe dollar ($) représentent des feuilles de calcul (par exemple, Sheet1$), et que les noms d'objets sans le signe dollar représentent Excel des plages nommées.

Utiliser un serveur lié

Pour simplifier les requêtes, vous pouvez configurer un classeur Excel sous la forme d'un serveur lié dans SQL Server.Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
306397 Comment faire : Utiliser Excel avec SQL Server des serveurs liés et des requêtes distribuées
Le code suivant importe les données de la feuille de calcul clients sur le serveur lié Excel « EXCELLINK » dans une nouvelle table SQL Server nommée XLImport1 :
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
				
Vous pouvez également exécuter la requête sur la source en mode passthrough en utilisant OPENQUERY comme suit :
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
				

Utiliser des requêtes distribuées

Si vous ne souhaitez pas configurer de connexion persistante vers le classeur Excel sous la forme d'un serveur lié, vous pouvez importer des données dans un but spécifique en utilisant la fonction OPENROWSET ou la OPENDATASOURCE. Les exemples de code suivants également importer les données à partir de la feuille de calcul clients Excel dans de nouvelles tables de SQL Server :
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
				

Utiliser ADO et SQLOLEDB

Lorsque vous êtes connecté à SQL Server dans une application ADO en utilisant Microsoft OLE DB pour SQL Server (SQLOLEDB), vous pouvez utiliser la même syntaxe « requêtes distribuées » à partir de la À l'aide de requêtes distribuées section pour importer des données Excel dans SQL Server.

L'exemple de code Visual Basic 6.0 suivant requiert que vous ajoutiez une référence de projet aux objets de données ActiveX (ADO). Cet exemple de code montre également comment utiliser OPENDATASOURCE et OPENROWSET sur une connexion SQLOLEDB.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
				

Utiliser ADO et le fournisseur Jet

L'exemple de la section précédente utilise ADO avec le fournisseur SQLOLEDB pour se connecter à la destination de votre importation Excel-vers-SQL. Vous pouvez également utiliser le fournisseur OLE DB pour Jet 4.0 pour vous connecter à la source Excel.

Le moteur de base de données Jet peut référencer des bases de données externes dans des instructions SQL à l'aide d'une syntaxe particulière ayant trois formats différents :
  • [Chemin d'accès complet à la base de données Microsoft Access].[Nom de la table]
  • [Nom de l'ISAM;Chaîne de connexion ISAM].[Nom de la table]
  • [ODBC ;Chaîne de connexion ODBC].[Nom de la table]
Cette section utilise le troisième format pour établir une connexion ODBC à la base de données SQL Server de destination. Vous pouvez utiliser un nom de Source de données (DSN) ODBC ou une chaîne de connexion sans DSN :
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
L'exemple de code Visual Basic 6.0 suivant requiert que vous ajoutiez une référence de projet aux objets ADO. Cet exemple de code montre comment importer des données Excel à SQL Server via une connexion ADO à l'aide du fournisseur Jet 4.0.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
				
Vous pouvez également utiliser cette syntaxe, qui prend en charge le fournisseur Jet, pour importer des données Excel dans d'autres bases de données Microsoft Access, les bases de données à accès séquentiel indexé (ISAM) method ("bureau") ou les bases de données ODBC.

Résolution des problèmes

  • N'oubliez pas que les noms d'objets Excel sont terminant par un signe dollar ($) représentent des feuilles de calcul (par exemple, Sheet1$) et que noms d'objets bruts représentent des plages nommées Excel.
  • Dans certaines circonstances, notamment lorsque vous désignez les données source Excel en utilisant le nom de la table plutôt qu'une requête SELECT, les colonnes de la table de SQL Server de destination sont réorganisés dans l'ordre alphabétique.Pour plus d'informations sur ce problème avec le fournisseur Jet, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    299484 PRB : Ne colonnes sont triés par ordre alphabétique lorsque vous utilisez ADOX pour extraire les colonnes de Table Access
  • Lorsque le fournisseur Jet détermine qu'une colonne Excel contient des données numériques ou texte mixte, le fournisseur Jet sélectionne le type de données "majoritaire" et renvoie les valeurs non correspondantes en tant que valeurs NULL.Pour plus d'informations sur la façon de contourner ce problème, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
    194124 PRB : Les valeurs Excel retournées comme NULL en utilisant DAO OpenRecordset

Références

Pour plus d'informations sur l'utilisation d'Excel comme source de données, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft :
257819 Comment faire : Utiliser ADO avec des données Excel à partir de Visual Basic ou de VBA
Pour plus d'informations sur la façon de transférer des données dans Excel, cliquez sur les numéros ci-dessous pour afficher les articles correspondants dans la Base de connaissances Microsoft :
295646 Comment faire : Transfert de données à partir de la Source de données ADO vers Excel avec ADO
247412 INFO : Méthodes pour transférer des données vers Excel à partir de Visual Basic
246335 Comment faire : Transfert de données à partir d'un jeu d'enregistrements ADO vers Excel avec Automation
319951 Comment : Transférer des données vers Excel à l'aide de SQL Server Data Transformation Services
306125 Comment : Importer des données à partir de SQL Server dans Microsoft Excel

Propriétés

Numéro d'article: 321686 - Dernière mise à jour: samedi 8 février 2014 - Version: 5.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft Excel 2000 Standard
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2000 Édition 64 bits
  • Microsoft SQL Server 7.0 Standard
  • Microsoft Excel 2002
  • Microsoft Excel 97 Standard
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Mots-clés : 
kbhowtomaster kbjet kbmt KB321686 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu d'une traduction automatique réalisée par un logiciel Microsoft et non par un traducteur professionnel. Cette traduction automatique a pu aussi être révisée par la communauté Microsoft grâce à la technologie Community Translation Framework (CTF). Pour en savoir plus sur cette technologie, veuillez consulter la page http://support.microsoft.com/gp/machine-translation-corrections/fr. Microsoft vous propose en effet des articles traduits par des professionnels, des articles issus de traductions automatiques et des articles issus de traductions automatiques révisées par la communauté Microsoft, de manière à ce que vous ayez accès à tous les articles de notre Base de connaissances dans votre langue. Il est important de noter que les articles issus de la traduction automatique, y compris ceux révisés par la communauté Microsoft, peuvent contenir des erreurs de vocabulaire, de syntaxe ou de grammaire. Microsoft ne pourra être tenu responsable des imprécisions, erreurs, ainsi que de tout dommage résultant d?une traduction incorrecte du contenu ou de son utilisation par les clients.
La version anglaise de cet article est la suivante: 321686
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com