importation de excel vers ACCESSRésolu

Question

Bonjour.

Je dois effectuer une importation d'un fichier Excel vers Access, mais j'ai un petit soucis.
Dans une colonne, les premières données sont numériques, et certaines suivantes sont alphanumériques.
Access identifie cette colonne en  numérique, ce qui fait qu'elle rejette tous les autres enregistrement qui ne le sont pas.
Les fichiers qui ont le ^m format doivent être compiler dans Access (mis bout-à-bout).

J'avais plusieurs idée pour essayer de contourner le problème d'importation, à savoir que mes données texte ne soit plus rejetter lorsque les premières données de la colonnes sont numériques. Je vous les soumets, pour savoir ce que vous en penser (certaines sont assez saugrenues ...):
- rajouter en "cacher" une première ligne avec du texte dans chaque colonne. Vous imaginez bien la tête qu'aura ma base de données après la compilation, mais je peux choisir un "code" que je supprimerai dans Access.
-faire une macro sous excel qui transforme tous les enregistrements en texte. Lors de l'importation, je lance cette maccro depuis Access.
-recupérer chaque ligne, les mettre une à une dans un tableau, comme ici
[url]http://www.vbfrance.com/codes/IMPORTER-FEUILLE-EXCEL-VERS-TABLE-ACCESS-EXISTANTE_41568.aspx/url
Mais hormis le faites que c'est lourd, que le code ne marche pas chez moi, j'ai peur de me retrouver face au ^m problème.

Savez-vous si il existe une fonction du genre DoCmd.TransferSpreadsheet qui peut transférer toutes les données en format texte?

P.S.
J'ai essayé de lier les tables => ^m erreurs
J'ai essayé d'importer en créant une table => ^m erreurs
J'ai essayé d'importer sur une table deja existante, avec les colonnes en format "texte", => ^m erreurs.

Si qqn a une idée pour contourner ce problème en évitant de transformer le fichier excel, ca serait super.
Sinon, je pense que je choisirai la première ligne à insérer.

Voila, merci bcp.

bonjourc · Accepted Answer

Function importation_fichiers(nom As String) As String Dim appExcel As Excel.application 'Application Excel Dim rows As Excel.application Dim wbExcel As Excel.Workbook 'Classeur Excel Dim wsExcel As Excel.Worksheet 'Feuille Excel Dim titre As Boolean Dim mot As String Dim emplacement As String Dim sql As String DisplayAlerts = False z = GetSetting(AppName:="PSA", Section:="projet1_" & nom, Key:="nombre") 'On vérifie si la structure de la table répond bien aux contraintes de l'importation 'Si plusieurs fichiers sont importés en même temps (selection multiple), on les vérifie tous. For x = 1 To z 'si la vérification n'est pas validé, alors une message d'erreur apparait pour indiquer les colonnes qui ne correspondent 'pas, et l'importation ne continue pas. Les contraintes n'étant pas respectées, cela entrainerait une erreur d'Access 'et une demande de débogage pour l'utilisateur. If ExistTable2("IMPORT_" & nom & "_" & x) = True Then If verification(nom) = False Then Exit Function End If End If Next x 'on va ici supprimer le contenu des tables ou l'ont souhaite importer les données. 'si l'on importe une table, on vérifie d'abord l'existence de la table Access avant de la supprimer. 'En effet, si l'on crée un nouveau module d'importation, le fichier ou l'ont souhaite importer les données n'existe pas encore 'et il peut y avoir une selection multiple, ce qui implique plusieurs cas: '-on importe autant de fichiers que le mois dernier, alors on boucle sur ces fichiers, et on supprime les données '-on importe plus de fichiers que le mois dernier, alors il convient de vérifier l'existence des tables avant de les supprimer '-on importe moins de table que le mois dernier, on efface les tables ou l'ont veut importer les données, et ce sont elles ' qui seront compilées si besoin. For x = 1 To z 'on vérifie l'existence de la table avec le module ExistTable2 If ExistTable2("IMPORT_" & nom & "_" & x) = True Then 'on supprime les données de la table, mais on garde la table et sa structure, car certaines tables sont directement lié 'à des requêtes, et les supprimer supprimerai de faites les liaisons. sql = "Delete * From IMPORT_" & nom & "_" & x DoCmd.RunSQL sql End If Next x 'une fois que l'on à vérifier que les structures des tables à importer sont bonnes, et que les tables où l'ont va importer les 'données sont vides; une fois que tout est pret en somme, on va commencer l'importation proprement dites. 'Une problématique posé lors de l'importation est que Access ne reconnait pas Excel comme une base de données (au même titre 'qu'un fichier texte ou une autre table .mdb par exemple). Il est impossible de définir le format du contenu des colonnes. 'Même si l'on peut définir le type de données dans la tale qui va recevoir les données, le traitement d'access 'lors de l'importation n'y répond pas. Pour définir le type de format (texte, numérique, ...) Access se base 'sur les 8 premières données de chaque colonne. Ainsi, si les 8 premières données sont numériques, Access reconnait la colonne 'comme numérique. Ce qui implique que si il rencontre par la suite, dans cette colonne, des données texte, il va les 'rejetter dans une table d'erreur. 'Pour parer ce problème, l'importation se déroule ainsi: '-j'ouvre les fichiers Excel et j'y insère une ligne. '-si il y a un titre, (que la colonne existe) alors je met "a supprimer" dans la ligne insérer. '-lorsque j'arrive à une colonne qui n'a plus de titre, je conclue que je suis arrivé à la deernière colonne '-j'enregistre alors le fichier, je l'importe (et comme il y a "a supprimer" dans la première ligne de chaque colonne, ' Access reconnait toutes les colonnes comme étant du texte, il n'y a donc plus de rejets pour cause de format non valide. '-puis je le réouvre, je supprimer la ligne précédement crée, et j'enregistre le fichier tel qu'il était initilement For x = 1 To z 'on recupère l'adresse du fichier emplacement = GetSetting(AppName:="PSA", Section:="projet1_" & nom, Key:=x) 'on ouvre l'objet Excel Set appExcel = CreateObject("Excel.Application") 'Ouverture d'un fichier Excel Set wbExcel = appExcel.Workbooks.Open(emplacement) 'wsExcel correspond à la première feuille du fichier onglet = GetSetting(AppName:="PSA", Section:="projet1_" & nom, Key:="onglet") 'on recupère le nom de l'onglet. Si celui-ci est vide (ca ne doit pas arriver si l'on respecte le process), 'alors il choisit le premier onglet. If onglet = "" Then onglet = "1" End If 'on ouvre le-dit onglet Set wsExcel = wbExcel.Worksheets(onglet) 'on masque les traitements pour l'utilisateur appExcel.Visible = False 'on insert une ligne dans me fichier Excel selectionné. wsExcel.rows("2:2").Insert 'on met la variable "titre" à true. Elle est chargée de repérer si la colonne a un titre. 'Lorsqu'elle detecte une colonne qui n'a pas de titre, elle devient "false" et on sort de la boucle. 'on suppose alors que la table de données est finie. titre = True i = 1 'tant que Access reconnait un titre, titre=true. lorsqu'il ne reconnait plus de titre, on considère que l'on 'est arrivé à la fin des titres, et l'on sort de la boucle. Do While titre = True 'Cette variable sert à parcourir les colonnes. On passe par l'ASCII pour pouvoir incrémenter. '(on pourra balayer la ligne 1. (A1; B1; C1; ...)) 'si le fichier Excel a des colonnes après "AZ", alors le programme se met en débug 'Les fichiers actuels ne dépassent pas la colonne "AZ", mais il faudrait en tenir compte si l'on veut importer 'un nvx fichier qui a plus de 52 colonnes. y = i + 64 If i > 26 Then y = i + 64 - 26 mot = "A" & Chr(y) 'si il y a un entete de colonne pour les colonnes de "AA" à "AZ" If wsExcel.Range(mot & "1").Value <> "" Then 'on insère "à supprimer" wsExcel.Range(mot & "2").Value = "supprimer" 'sinon, titre = false, et on sort de la boucle. Else: titre = False End If Else mot = Chr(y) 'si il y a un entete de colonne pour les colonnes de "A" à "Z" If wsExcel.Range(mot & "1").Value <> "" Then 'on insère "à supprimer" wsExcel.Range(mot & "2").Value = "supprimer" 'sinon, titre = false, et on sort de la boucle. Else: titre = False End If End If 'on incrémente la vriable de facon à balayer la colonne suivante. i = i + 1 Loop 'on ferme et on enregistre wbExcel.Close SaveChanges:=True 'on quitte l'application d'Excel appExcel.quit 'on importe le fichier choisit dans la table voulue DoCmd.TransferSpreadsheet acImport, 8, "IMPORT_" & nom & "_" & x, emplacement, True, onglet_emplacement 'Ouverture de l'application Set appExcel = CreateObject("Excel.Application") 'Ouverture d'un fichier Excel Set wbExcel = appExcel.Workbooks.Open(emplacement) 'wsExcel correspond à l'onglet choisi Set wsExcel = wbExcel.Worksheets(onglet) appExcel.Visible = False 'on supprime la ligne que l'on vient de créer wsExcel.rows("2:2").Delete wbExcel.Close SaveChanges:=True appExcel.quit Next x MsgBox ("Les fichiers ont été importés.") End Function Voici le module qui sert à vérifier la structure de la table de données. Iminons que l’on veuille tous les mois importer le fichier feuille.xls dans la table access « table ». Cette feuille est utilisée par des utilisateurs différents, et certains sont tentés par moment de rajouter des colonnes ou de changé les noms. Ce qui en résultera que l’importation sera planté. Comme je vais trifouiller dans le fichier pour que l’importation n’est pas de rejet, il faut que je m’assure par avance que l’importation se déroulera correctement. La méthode utilisée ici est d’importer la feuille dans une table temporaire qui n’existe pas. Ainsi, Access va créer les colonnes en fonction de celles trouvé dans la feuille. Ensuite, on compare la table « tempo » avec la table « table ». Si les structures cont semblables, alors on supprime la table « tempo, et on lance le module d’importation. Le principe est d’importer la feuille Exel que l’on veut importer 'Ce module vérifie le bon format des fichiers 'dans la 1ère partie on regarde si tous les entetes qui se trouve dans le fichier à importer se trouve dans le fichier 'ou l'on veut importer les données 'dans le 2nd partie, on regarde si tous les entetes qui se trouve dans le table ou l'on veut impotrer les données 'se trouve dans la table que l'ont va importer Function verification(nom As String) As Boolean Dim db As DAO.Database Dim tdb As DAO.TableDef Dim fld As DAO.Field Dim db2 As DAO.Database Dim tdb2 As DAO.TableDef Dim fld2 As DAO.Field Dim aa As String 'nom_colonne_tempo est la variable ou sont stockées les colonnes du fichier tempo, qui est le fichier à importer 'nom__colonne_import est la variable ou sont stockées les colonnes du fichier, qui est la table où l'on importe Dim nom_colonne_import, nom_colonne_tempo As String Dim exact As Boolean 'on regarde si la table "tempo" existe avant de la supprimer. On supprime la table, pour que lors de l'importation, 'Access n'importe pas les donénes dans un format attendu. Ainsi, quelque soit le format de la table, les entetes 'seront importés, et ensuite pourront être comparés à ceux attendus If ExistTable2("tempo") = True Then DoCmd.DeleteObject acTable, "tempo" End If verification = True 'on recupère l'adresse et l'onglet du fichier à importer emplacement = GetSetting(AppName:="PSA", Section:="projet1_" & nom, Key:=x) onglet = GetSetting(AppName:="PSA", Section:="projet1_" & nom, Key:="onglet") 'on prend les mêmes règles que celle utilisées lors de l'importation, ainsi, si un problème se produit ici, on 'ne lance pas le module d'importation (débogage), et si aucune errreur ne se produit, il ne s'en produira 'aucune lors de l'importation If onglet = "1" Then onglet_emplacement = "" Else onglet_emplacement = onglet & "!" End If 'On importe dans la table tempo DoCmd.TransferSpreadsheet acImport, 8, "tempo", emplacement, True, onglet_emplacement Set db = CurrentDb '1ère PARTIE 'on recupère les tables qui sont dans la base de données For Each tdb In db.TableDefs 'on trie sur la table qui nous interesse ("tempo") If tdb.Name = "tempo" Then 'on balaie chaque colonne de cette table (qui représente la table que nous voulons importer) For Each fld In tdb.Fields 'et on stocke le nom de colonne de la teable à importer dans la variable nom_colonne_import = fld.Name 'on place le booléan sur faux, et il deviendra vrai si on retrouve une entete du même nom dans la table exact = False 'on prend une 2ème table dans la base de donénes For Each tdb2 In db.TableDefs 'on filtre sur la table ou l'on va importer les données If tdb2.Name = "IMPORT_" & nom & "_" & x Then 'on balaie ensuite les colonnes de cette table For Each fld2 In tdb2.Fields 'on stocke le nom de cet entete dans une variable nom_colonne_tempo = fld2.Name 'et si l'une des colonnes du fichier ou on veut importer les données à le ^m entete que 'l'entete de la colonne du fichier à importer, alors le booléan devient "vrai". If nom_colonne_tempo = nom_colonne_import Then exact = True Else 'on récupère l'entete de la colonne du fichier à importer qui n'a pas trouvé de correpondant aa = nom_colonne_import End If Next End If Next 'Avant de relancer la boucle pour passer à la prochaine colonne, on regarde la valeur du booléan. 'Si le booléan est "vrai, on peut passer à la colonne suivante, ou passer à la partie 2 si on arrive sur 'la dernière colonne. 'Si le booléan est "faux", on affiche la colonne qui ne correspond pas aux cirtères recherchés 'on place le module (booléan) en faux, et on sort de ce module. If exact = False Then MsgBox ("l'entete " & aa & " n'est pas attendu dans le fichier importé mais s'y trouve.") verification = False Exit Function End If Next End If Next '------------------------------------------------------- '2nd PARTIE 'c'est la partie exactement inverse de la 1ère partie. on va regarder si chque entête de colonne a bien une 'correspondance dans la table que l'on va importer 'on recupère les tables qui sont dans la base de données For Each tdb In db.TableDefs 'on filtre sur la table ou l'on va importer les données If tdb.Name = "IMPORT_" & nom & "_" & x Then 'on balaie chaque colonne de cette table (qui représente la table où l'on va importer les données For Each fld In tdb.Fields 'MsgBox ("table : " & tdb.Name & " colonne : " & fld.Name) nom_colonne_import = fld.Name 'on place le booléan sur faux, et il deviendra vrai si on retrouve une entete du même nom dans la table exact = False 'on prend une 2ème table dans la base de donénes For Each tdb2 In db.TableDefs 'on trie sur la table qui nous interesse ("tempo") If tdb2.Name = "tempo" Then 'on balaie chaque colonne de cette table (qui représente la table que nous voulons importer For Each fld2 In tdb2.Fields nom_colonne_tempo = fld2.Name If nom_colonne_tempo = nom_colonne_import Then exact = True Else aa = nom_colonne_import End If Next End If Next 'Avant de relancer la boucle pour passer à la prochaine colonne, on regarde la valeur du booléan. 'Si le booléan est "vrai, on peut passer à la colonne suivante, ou sortir du module si on arrive sur 'la dernière colonne. 'Si le booléan est "faux", on affiche la colonne qui ne correspond pas aux cirtères recherchés 'on place le module (booléan) en faux, et on sort de ce module. If exact = False Then MsgBox ("l'entete " & aa & " est attendue dans le fichier importé mais ne s'y trouve pas.") verification = False Exit Function End If Next End If Next End Function Et enfin, le module ExistTable2 que je vous invite à chercher sur ce site (je n’ai aps changé le nom du module). Je m’en sers principalement donc un « if », pour continuer l’opération si la table existe, et ne pas faire d’opération sur une table qui n’existe pas (ce qui évite un débogage, parfois effrayant pour les utilisateurs. Function ExistTable2(ByVal strTabl As String) As Boolean Dim str As String 'fonction qui sert à reconnaitre l'existance des tables. On Error GoTo err01 'on recupère le nom de la table voulu dans la base actuelle (le fichier Access). str = CurrentDb.TableDefs(strTabl).Name 'on met ExistTable = true ExistTable2 = True Exit Function err01: 'Si il y a une erreur du type 3265 (la table n'existe pas), on met ExistTable sur False. Select Case Err.Number Case 3265 ExistTable2 = False End Select 'On renvoie donc la valeur du booléen. End Function

bonjourc · Answer

Bon, Je reviens car j'ai finalement réussi à contourner le problème. Je vais mettre ici comment j'y suis arrivé, mais je pense m'être vraiment compliqué la vie.

Je vais donc décrire comment j'ai procédé, puis mettre le code à disposition.
Je ne décrirais que 3 modules (dont un qui se trouve ailleurs sur le site).
un module qui vérifie l"existence d'une table
un module qui vérifie la structure d'une table (en comparant par rapport à une table deja existante le nom des colonnes)
un module qui va importer la feuille Excel en mettant toutes les données en texte.

Il y a qqexplications préliminaires:
la variable "z" est le nombre de fichier que l'on a selectionnée dans l'écran de saisie windows ou on selectionne l'adresse des fichiers. la multiselect est sur oui.
Comme le module d'import est commun à tous les fichiers que je veux importer, le paramètre est le nom de la table ou je veux importer les données.
J'ai aussi rangé avec le savesetting et le getsetting l'adresse et le nom de l'onglet en fonction du nom. Ainsi, je n'ai que un paramètre.

voici les modules.

bonjourc · Answer

Bon, je me rend compte que les couleurs n'ont pas été prise en compte, il faut donc copier ce code une feuille access pour voir ce que ca donne. Y'a qqlignes avant les modules qui ne sont pas en commentaires et qu'il ne faudra pas copier dans le module.

Importation de excel vers ACCESS

3 réponses

Votre réponse

Discussions similaires