Astuce : insérer un fichier excel dans une base MySQL

Il est parfois plus pratique de commencer sa base sous Excel et de vouloir ensuite l’importer dans MySQL. Voici quelques trucs et astuces que j’ai découverts au et à mesure de mes expériences …
Une base MySQL peut récupérer le contenu d’une feuille excel. Il faut pour cela transformer notre tableau excel en un fichier texte (via le format *.CSV). Si votre fichier excel contient plusieurs feuilles, il faudra créer un fichier *.CSV par feuille.
Pour enregistrer une feuille excel en *.CSV, vous n’avez qu’à choisir ce format dans la liste des types de documents (fenêtre “Enregistrer sous”).

Quelques pécautions à prendre
Voici quelques petites vérifications à faire pour éviter de perdre trop de temps après avec votre base

  • Assurez-vous que chaque enregistrement possède bien un n°ID dans votre fichier Excel (Colonne ID remplie).
    Si vous souhaitez ajouter des enregistrements avec votre fichier, vous n’avez qu’ à décaler les n°ID afin qu’ils viennent se placer après les ID déjà entrés.
  • Contrôlez bien que tous les champs soient bien au format “Texte” et que les colonnes ne contiennent pas de fonction.
  • Remplacez toutes les apostrophes par – \’ – (anti-slash+apostophe) et toutes les guillemets par – \” – (anti-slash+guillemet).
  • Supprimez la ligne des intitulés.

Une fois votre fichier Excel enregistré en *.CSV, ouvrez-le dans le bloc-note pour voir comment il est configuré.
Généralement, les champs sont séparés par ; et les textes ne sont pas séparés par des guillemets.

Dans PhpMyAdmin

Ouvrez PhpMyAdmin et sélectionnez votre table. Bien entendu, vous devez retrouver la même structure (nombre de colonnes) dans votre table que dans votre fichier.
Selon votre version de PHPMyAdmin, vous trouverez la fonction “Insérer des données depuis un fichier texte” soit en page d’accueil, soit dans un onglet.
Un formulaire s’ouvre :

  • 1°/ Emplacement du fichier texte : vous permet d’aller sélectionner votre fichier *.CSV
  • 2°/ Remplacer les données : permet de remplacer les données de la table par celles du fichier uniquement quand la clé primaire est identique. Pour que cette option soit lancée, vous devez la cocher.
  • 3°/ Champs terminés par : Préciser le signe qui sépare chaque champ dans le *.CVS. Généralement, il s’agit du ;
  • 4°/ Champs entourés par : N’est utile que si les champs sont texte sont entourés de simple ou double guillements. Souvent, le *.CSV créé par Excel ne met rien. Il suffit donc de supprimer les guillemets et de laisser vide
  • 5°/ caractère spécial : permet d’indiquer le signe qui indique qu’une apostrophe ou une guillemet appartient au texte.
  • 6°/ Lignes terminées par : indique les règles qui délimitent les enregistrements ( le retour chariot est utilisé par défaut).
  • 7°/ Nom des colonnes : permet de rentrer les noms de colonnes que l’on veut juste intégrer.

L’expérience m’a montré que souvent, vous pouviez rencontrer des problèmes si tous les champs de votre fichier Excel n’étaient pas remplis dans les règles de votre base. Exemple, sur un champ MySQL unique, vous ne pouvez insérer deux enregistrements non remplis dans cette colonne.
Il faut aussi que vous pensiez aux modifications que subit généralement votre enregistrement quand il passe via un formulaire PHP. Si vous avez insérer une fonction “htmlentities” par exemple, il va falloir penser à transformer tous les caractères spéciaux par leurs équivalents HTML.

Vos réactions

  1. ---

    bonjour

    je voulai savoir s\’il était possible que vous m\’aidiez à transformer le fichier excel en fichier CSV mais via une fonction en php.

    merci d\’avance

  2. ---

    j\’aimerais créer un fichier regroupant ts les utilisateurs, pourriez-vous me faire parvenir un fichier exemple, de ce façon je pourrai faire mon fichier avec un exemple.
    ce fichier, je devrais l\’integrer ds free eos
    je vous remercie par avance
    jcp

  3. ---

    Pour rappel, le poids d\’un fichier à importer dans PhpMyAdmin est limité. Vous ne devez pas dépasser les 2Mo. Il est parfois préférable de scinder votre fichier pour faire un import en plusieurs fois.

  4. ---

    responsable de quoi?

  5. ---

    bonjour
    j\’ai un probleme lorsque je veu inséré le fichier texte il me dit :
    \” Le fichier n\’a pu être lu /tmp/phpGUZ4W5 \”
    alors que cela marchai avec une version plus ancienne de phpmyadmin.
    celle que jutilise actuellement est la 2.6.3
    merci de vos reponse
    si vous pouviez me repondre sur mon email.
    zebbat1@hotmail.fr
    merci

  6. ---

    bonjour Mr Arnaud Menier,
    je tente desesperement de transferer ma base de donnée vers mysql a partir de openoffice calc(comme excel).g suivi procedure mai jme retrouve avec des caracteres speciaux?!?
    ya til une convertion a faire…
    aidez moi svp

  7. ---

    Je pense que le souci vient de la configuration de la base de données \”Utf-8\” ou non ?
    Il faut essayer de faire des tests avec d\’autres paramètres de langue de la base.

  8. ---

    bonjour!coment faire pour trouver les documents base de données en excel?

  9. ---

    bonjour, je cherche à importer un fichier qui est déja sous le format *.cvs dans une base de données MySql ?
    Comment dois-je procéder?
    Merci d\’avance pour votre réponse

  10. ---

    Utilise la fonction import de PHPMyAdmin. Si le fichier est trop lourd, il se peut que ça bloque, mais sinon, il n\’y a pas de raison.

  11. ---

    bonjour je cherche un astuce qui me permet de stocker une structure de donnée tel que liste , tableau de 2 dimension dans une base de donnée si vous pouver maider je serais très reconnaissante
    merci d’avance

  12. ---

    Bonjour,
    j’aimerais transferer des données en format csv qui contiennent des ‘retour-chariot’ à L’origine dans le fichier excel , mon problème est que les données importées comportent des \n à la place de faire un vrai retour chariot dans la colonne sur mysql, le but est de reproduire le contenu de la colonne excel dans mysql du genre 1- Paragraphe 1
    2- Paragraphe 2
    3- Paragraphe 3
    Merci de votre aide

  13. ---

    Merci pour l’info.
    Ps: Pour éviter les slashs devant les simple quote, utilisez la fonction stripslashes() de PHP.
    Bonne continuation

  14. ---

    Bonsoir,

    Importation d’un fichier CSV

    Que signifie le message : “L’importation s’est terminée avec succès, 0 requêtes exécutées” ?

    Où est le succès?

    Cordialement
    Michel

  15. ---

    Votre import n’a pas fonctionné dans ce cas.

  16. ---

    Salut,
    j’ai essayé le tuto l’importation des données du fichier .CVS marche mais pas lorsqu’il y a des contraintes de clé étrangère sur un champ dans la table.
    Je ne sais comment résoudre cela.
    Pouvez-vos m’aider?

  17. ---

    Je ne comprends pas trop votre demande. Envoyez moi plus d’informations via le formulaire de contact de ce blog et non en commentaire.
    Cordialement,

  18. ---

    je vous remercie pour votre publication
    Astuce : insérer un fichier excel dans une base MySQL
    qui nous a aidé beaucoup.

  19. ---

    je n’arrive pas à afficher par exemple 1000,23

  20. ---

    Bonjour
    Quel est le format de cellule à adopter sur excel pour les dates ?
    Si je passe en texte, j’ai un chiffre qui ne correspond à rien
    Mysql va t-il faire la traduction ?
    Merci

  21. ---

    Deux options à mon avis :
    1°/ Mettez vos dates au format américain YYYY-mm-dd qui est le format classique
    2°/ le mieux est de transformer les dates en timestamp.

  22. ---

    Bonjour
    Merci j’ai reussi à inserer mon fichier excel grace a vos explication
    mais pour les dates j’ai 11/03/2014 08:10 sur mon excel mais sur le phpmyadmin j’ai 0000-00-00 00:00:00
    je suis bloqué car les dates sont importantes pour le traimement

  23. ---

    Vous devez dans votre excel changer le format des dates pour avoir celui de PhpMyAdmin, c’est à dire AAAA-mm-dd hh:mm:ss

  24. ---

    Bonjour et merci
    j’ai réussi à modifier et maintenant c’est ok pour la date

  25. ---

    Exellent, merci !

Laissez un commentaire