Import de fichiers Excel avec SSIS

Ce type de source est loin d’être mon préféré et cela en raison du typage implicite des données effectué par le provider :

  • par défaut les colonnes texte sont typées en nvarchar(255) ou mémo (pour les chaines de plus de 255 caractères) et les numériques en réel. Pour convertir vos données dans le format désiré, vous n’avez plus qu’à utiliser un Data Conversion, je vous déconseille vivement de la faire via l’éditeur avancée mais bon (certains sont plus joueur que d’autres…)
  • de plus, quel que soit le provider (JET4.0 ou ACE12.0), le typage des données est effectué à l’aide d’un échantillonnage, par défaut sur les 8 premières lignes. Ce paramètre peut être modifié dans la base de registre

pour le Jet4.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

pour le ACE 12.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

  • si vous avez une colonne contenant des valeurs textuelles et numériques il vous faudra alors aussi gérer les types mixtes. Pour cela, vous n’avez qu’à modifier la chaîne de connexion en ajoutant aux propriétés étendues la valeur IMEX=1, exemple :

pour le Jet4.0 : Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties= »Excel 8.0;HDR=Yes;IMEX=1« ;

pour le ACE 12.0 : Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyExcel.xlsx;Extended Properties= »Excel 12.0 xml;HDR=Yes;IMEX=1« 

Sachez toutefois que :

  • même si la valeur du TypeGuessRows est à 0, seules les 16 384 premières lignes sont scannées. Dommage si vous avez des chaines  longues de plus de 255 caractères ou bien des valeurs de types mixtes sur les lignes suivantes (cela m’est encore arrivé il y a peu), vous n’avez plus qu’à pleurer…
  • si le fichier Excel est en cours édition, il est verrouillé ce qui fera planté votre package SSIS
  • que le connecteur 64 bits pour Excel fonctionne, il suffit d’installer le ACE.OLEDEB.12.0 version 64 bits sur le serveur SSIS. Notez toutefois qu’il n’est pas possible d’installer les 2 versions du provider (32 et 64 bits) sur la même machine (mais qui de nos jours continue à installer Office sur les serveurs MS BI ?)

Mis à part ces considérations, les fichiers Excel peuvent s’avérer utiles du fait qu’un même classeur Excel puisse contenir plusieurs onglets (très pratique pour des tables de paramétrage « end-user »), et qu’un même onglet puisse contenir plusieurs tables. Rentrons dans le vif du sujet et voyons comment intégrer différentes tables contenues dans un seul et même onglet.

Prenons un fichier Excel tout bête, composé d’un onglet contenant 3 référentiels

Excel Sample

Par défaut, le connecteur Excel ne permet de sélectionner qu’un fichier Excel, un onglet et d’indiquer si la première ligne est une ligne d’entête. Dans mon exemple, sur un seul et même onglet, je veux récupérer :

  • la table devise contenue dans les cellules A et B de la ligne 5 à 12
  • la table taux de change contenue dans les cellules A à C de la ligne 16 à 23
  • et la table Pays Devise contenue dans les cellules E à G à partir de la ligne 5

Vous aurez noté au passage que j’ai par habitude d’ignorer la ligne d’entête; car en règle générale, les utilisateurs aime bien s’approprier le fichier et modifier les noms des colonnes (tant qu’ils ne touchent pas à l’ordre tout va bien…).

Commençons par créer la connexion au fichier Excel, en spécifiant que la première ligne n’est pas une ligne d’entête :

Excel Connexion

Si vous avez des colonnes de type mixte, éditez la chaîne de connexion est ajouter la propriété IMEX=1

ExcelIMEX

Il ne reste plus qu’à extraire les données du fichier Excel pour les insérer en base. Pour cela, il suffit de créer un Data Flow Task avec 3 sources Excel pointant sur le même onglet. Par défaut, lorsque vous créer une source Excel, vous ne sélectionner que l’onglet sur lequel se trouve les données sans spécifier de plage de valeurs et le provider se débrouille tout seul. Si vous n’avez pas d’entête de colonnes, ces dernières sont identifiées par F + n° de colonnes (ex : F1, F2, F3 ,etc.). Afin de pointer sur une plage de cellules particulières, vous avez deux solutions :

1. Créer votre source Excel en la faisant pointer sur l’onglet désiré puis modifier la propriété OpenRowset pour spécifier la plage de cellule de la façon suivante ExcelSheet$FirstColumnStartLine:LastColumnEndLine. Le n° de la ligne de fin est optionnel, s’il n’est pas spécifié, l’ensemble des lignes contenant des données seront traitées.

ExcelSource - OpenRowset

2. Créer votre source Excel en spécifiant une commande SQL du type SELECT * FROM [ExcelSheet$FirstColumnStartLine:LastColumnEndLine]

 ExcelSourceSQLCommande

Il ne vous reste, ensuite plus qu’à donner un nom fonctionnel à vos colonnes de sortie, puis à transformer le typage des colonnes avec un Data Conversion. Quelque chose du type :

DFT - Excel Source Natif

La dernière possibilité est de scripter votre source Excel. Cela revient au même à l’exception du fait que vous pourrez typer les données de sortie à votre guise en C#, je vous donne un exemple de script qui devrait faire l’affaire :

public override void CreateNewOutputRows()
 {
 OleDbConnection cnx = null;
 OleDbCommand cmd = null;
 OleDbDataAdapter da = null;
 DataTable dt = null;

 try
 {
 cnx = new OleDbConnection(this.Variables.cnxExcelFile.ToString());
 cmd = new OleDbCommand("SELECT * FROM [Sheet1$A16:C23]", cnx);
 da = new OleDbDataAdapter(cmd);
 dt = new DataTable();
 da.Fill(dt);
 for (int indexRow=0;indexRow<dt.Rows.Count;indexRow++)
 {
 this.Output0Buffer.AddRow();
 Output0Buffer.CurrencyCode = dt.Rows[indexRow][0].ToString();
 Output0Buffer.FixingRate = Convert.ToDecimal(dt.Rows[indexRow][1].ToString());
 Output0Buffer.RateDate = Convert.ToDateTime(dt.Rows[indexRow][2].ToString());
 }
 }
 finally
 {
 cnx.Close();
 cnx.Dispose();
 }
 }

Comme d’hab, retrouvez les sources sur SkyDrive.

Je terminerai juste cet article par un petit bench afin de vous démontrer ma rancœur avec ce format de fichier. Je créé un fichier Excel de 50 colonnes et d’environ 400 000 lignes, ce fichier est aussi enregistré en CSV. Je me retrouve alors avec un fichier Excel de 60 Mo et un fichier Csv de 78 Mo. A première vue, on dirait que le fichier Excel sera plus rapide à charger.

Excel Benchmark

Le résultat est sans appel et plus les fichiers Excel sont volumineux plus l’écart est frappant.

Pour conclure :

6 réflexions sur “Import de fichiers Excel avec SSIS

    • C’est bon çà🙂
      J’aime bien c’est propre.
      Merci David.

      J’en profite au passage pour indiquer que le composant Excel Source monte tout le fichier en mémoire, et que j’ai eu à cette occasion un très joli message d’erreur System.OutOfMemoryException sur un fichier de 150Mo. Idem en scriptant, la fonction OleDbDataAdapter.Fill a le même comportement. Du coup, obligé d’ouvrir le fichier Excel, à la mano, et de le splitter en deux😦

  1. Pingback: Import de fichiers Excel avec SSIS | Bienvenue à BM Formation
  2. merci Fred pour ce article qui m’apparaît tré intéressant par comptre sur la même piste j’ai un pakage ssis qui permet de faire l’importation d’un Fichier plat le problème c’est que mon Fichier source possède Chaque jour sa propre nom
    ma question c’est comment je peux gérer le changement périodique du nom de ma Fichier source surtout que le composant pointe sur un seul Fichier
    merci

    • Salut Mehdoini,
      1. Il te suffit de mettre en place un For Each Loop Container.
      – Au niveau de l’onglet Enumeration, tu sélectionnes l’énumérateur de type Foreach File Enumerator et tu paramètres l’emplacement dans lequel se trouve ton fichier (propriété Folder) ainsi que son nom (propriété Files) en utilisant le caractère * comme valeur joker.
      N.B : pense à variabiliser les propriétés Folder et Files à l’aide des expressions.
      – il reste ensuite à récupérer le nom complet du fichier dans une variable afin de rendre ta connexion dynamique. Cela se paramètre au niveau de l’onglet Variable Mappings. Tu sélectionnes la variables et tu la mappe sur l’index 0.
      2. Paramétrage de ta connexion au fichier plat
      – il faut tout d’abord modifier la propriété Delay Validation de ta connexion et la passer à »true » de façon à ce que ta connexion ne soit valider par SSIS qu’à son utilisation et non pas à l’ouverture du package.
      – rendre dynamique la connexion à ton fichier par Expression. La chaîne de connexion doit être initialisée dynamiquement par la variable récupérant le nom du fichier à intégrer.
      3. Traiter le fichier
      – il te reste à charger en base ton fichier à l’aide d’un Data Flow Task
      – puis de le transférer dans un autre dossier (rejected au done en fonction du résultat du traitement) ou bien tout simplement de le supprimer.
      Au plaisir.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s