VBA : Importer un tableau d'Excel vers SQL

Question

J'essaie d'importer un tableau de données depuis Excel vers SQL Server 2012, en utilisant VBA.
De préférence avec l'aide d'un UDF.

Le tableau Excel ressemble à quelque chose comme ceci.

TableID    2012  2011  2010  2009
row 1       11     12   13    14
row 2       21     22   23    24
row 3       31     32   33    34
etc..

(J'ai placé les chiffres dans les cellules pour désigner leur position. Par exemple 11 = ligne 1, colonne 1)

La table de la base de données ressemble à quelque chose comme ceci.

Header:        id   |  year  |  row1  |  row2  |  row3  |  etc..
ExampData:  TableId    2012      11       21       31       ..
ExampData:  TableId    2011      12       22       32       ..

(Cela n'inclut pas la colonne de la clé primaire, qui peut être soit une combinaison de l'id et de l'année, soit un NEWID() )

---

Je sais comment importer des colonnes spécifiques, par exemple je pourrais exécuter le code suivant :

INSERT INTO example_table (id, year, row1, row2, row3) VALUES ('001', '2012', '11', '21', '31')

Cela fonctionne très bien pour les colonnes individuelles, mais comment faire pour que cela fonctionne pour l'ensemble du tableau (plusieurs colonnes et lignes).

Answer 1

J'ai donc réussi à le faire fonctionner. J'utilise Excel 2010 et SQL Sever 2012.
Cela suppose que les titres des lignes dans Excel sont les mêmes que les titres des colonnes dans le sql.
Cela suppose également une disposition des tables similaire à celle de la question posée.

---

Pour importer des données d'Excel dans le serveur SQL, en utilisant UDF, nous pouvons faire ce qui suit.
Créer un Public Function en excel :

Public Function import_data(TableID, vHeader As Variant, vRow As Variant, vData As Variant)
End Function

Et utilisez votre méthode de connexion préférée pour vous connecter à la base de données.
Ensuite, pour le INSERT utilisent les éléments suivants :

'Declare the  variables
Dim vpush As String
Dim headerCount As Long
Dim rowTitles As String
Dim rowDatas As String
Dim i As Long

`Count the amount of columns 
headerCount = Application.CountA(vHeader)

`Create insert statement
For i = 1 To headerCount
   rowTitles = VBA.Join(WorksheetFunction.Transpose(vRow), ", ")
   rowDatas = VBA.Join(WorksheetFunction.Transpose(Application.Index(vData, , i)), "', '")
   vpush = "INSERT INTO example_table (id, " & rowTitles & ", year) VALUES ('" & TableID & "', '" & rowDatas & "', '" & vHeader(i) & "')"
Next i

N'oubliez pas de .Execute (vpush) .

Ainsi, chaque fois que vous voudrez importer un tableau, vous ferez ce qui suit dans une feuille de calcul Excel :

1. Dans un type de cellule =import_data( et appuyez sur CTRL + SHIFT + A
2. Sélectionnez TableId
3. Sélectionnez la ligne d'en-tête (on suppose qu'il s'agit toujours d'années ici).
4. Sélectionnez la colonne avec tous les titres de ligne (qui seront les titres de rubrique dans votre sql)
5. Sélectionner toutes les données du tableau

Ça devrait le faire.
(P.S. Si ce n'est pas la manière la plus efficace, ou si vous voyez des améliorations n'hésitez pas à modifier ou ajouter)

Answer 2

Si vous utilisez SQL Server 2008 ou une version ultérieure, vous pouvez insérer plusieurs lignes dans votre instruction INSERT :

INSERT INTO example_table (id, year, row1, row2, row3)
VALUES ('001', '2012', '11', '21', '31'),
   ('002', '2012', '12', '22', '32'),
   ('003', '2012', '13', '23', '33')

En fonction de la taille de votre feuille de calcul, vous pouvez le faire en un seul grand INSERT, ou en plusieurs petits INSERT.