Dev Faq
11
votes

Créer une table SQL avec les types de colonne corrects de CSV

J'ai regardé un nombre de questions sur ce site et je ne trouve pas de réponse à la question suivante: Comment créer plusieurs nouvelles tables dans une base de données (dans mon cas, j'utilise PostgreSQL) à partir de plusieurs fichiers source CSV , où les nouvelles colonnes de table de base de données reflètent avec précision les données dans les colonnes CSV?

Je peux écrire la syntaxe de la table Create Table, et je peux lire les lignes / valeurs d'un fichier CSV, mais une méthode existe-t-elle déjà pour inspecter le ou les fichiers CSV et déterminer avec précision le type de colonne? Avant de construire le mien, je voulais vérifier si cela existait déjà.

Si cela n'existe pas déjà, mon idée serait d'utiliser le module Python, CSV et le module PYCOPG2 pour construire un script Python qui ferait:

  1. Lire le (s) fichier (s) CSV (s).
  2. Sur la base d'un sous-ensemble d'enregistrements (10-100 rangées?), Inspectez de manière itérative chaque colonne de chaque ligne pour déterminer automatiquement le type de colonne de droite des données de la CSV. Par conséquent, si la rangée 1, la colonne A avait une valeur de 12345 (INT), mais la rangée 2 de la colonne A comptait une valeur d'ABC (Varchar), le système déterminerait automatiquement qu'il devrait être un format varchar (5) en fonction de la combinaison des données qu'il a trouvées dans les deux premières passes. Ce processus pourrait aller autant de fois que l'utilisateur se sentait nécessaire pour déterminer le type et la taille probables de la colonne.
  3. Construisez la requête Créer une table telle que définie par l'inspection de la colonne de la CSV.
  4. Exécutez la requête Create Table.
  5. Chargez les données dans la nouvelle table.

    Est-ce qu'un outil comme celui-ci existe déjà dans SQL, PostgreSQL, Python ou existe une autre application que je devrais être utilisée pour accomplir ceci (semblable à PGADMIN3)?

11 commentaires

Essayez-vous d'automatiser la création de tables comme celle-ci pour tenir compte de plusieurs sources de CSV? Chaque source aura sa propre table? Sinon, je pense que le meilleur dispositif d'inspection de données serait la marque I oculaire.

Kungfoo a raison. La seule autre option de SQL serait l'assistant d'importation / exportation et à l'aide du bouton "Suggérer des types", il prendra un échantillon et fera de son mieux pour déterminer les colonnes.

La plupart des programmes qui traitent de l'importation de données dans une base de données ont une implémentation de types de suggestions. Si vous allez écrire cela vous-même, je suggère de regarder comment Excel / Accès / Tableau / etc Faire cela. (Tableau regarde les 16 premières lignes d'un fichier pour déterminer le type.)

@iknowkungfoo, oui, j'ai plusieurs fichiers source CSV téléchargés à partir de plusieurs sources que j'aimerais automatiser la création de table pour.

@Kreativitea, vous avez raison, je pourrais aller dans la route d'accès / Excel, mais j'espérais éviter de devoir passer trop de pas intermédiaires. Je préférerais juste partir de CSV à SQL dans 1 étape / application. L'assistant d'importation / exportation serait correct, j'ai juste beaucoup de tables à importer.

Eh bien, comme d'autres l'ont dit: c'est relativement inutile. pourrait vous sauver une frappe, mais vous devez toujours comprendre la signification des colonnes. Et les fichiers CSV ont souvent des noms de colonnes plus ou moins de signification. Et: Même si l'importation était automatique, vous devez toujours comprendre la signification du contenu et comment elle concerne vos tables existantes. Normalement, vous gagnez ces connaissances tout en massant les fichiers d'entrée dans la forme que vous souhaitez. Le travail n'est pas dans la frappe, mais dans la compréhension.

Vérifiez les outils ETL comme Pentaho - ils ont tendance à avoir des fonctionnalités d'analyse de données.

Serait une option aussi? R 'CODE> READ.CSV Est-ce que certaines détection de type, et il y a RPY pour l'intégrer avec Python si vous avez besoin.

Même si les fichiers CSV sont automatiquement chargés dans les tables de création automatique, l'application devra se rendre compte du fait, reconnaître la présence de nouvelles relations et commencer à les utiliser. Sauf si c'est Skynet, je dirais qu'une intervention humaine est toujours inévitable ...

@wildplasser: S'il y a 1000 colonnes dans le CSV (le CSV a une en-tête aussi), quelle option peut-elle a demandé une autre aidera certainement parce que vous n'aurez pas à saisir des noms de colonne 1000 sur deux types de données de juger. Cela vous ferait en fait économiser beaucoup de taper.

Tout le monde avec un fichier .csv avec 1000 colonnes a un problème. Ce problème n'est pas sur l'effort de frappe, mais sur la sémantique.

3 Réponses :

1
votes

Il semble que vous ayez besoin de connaître la structure à l'avance. Il suffit de lire la première ligne pour savoir combien de colonnes vous avez eues.

CSV ne porte aucune information de type. Il faut donc déduire du contexte des données. P>

Amélioration de la réponse légèrement incorrecte avant, vous pouvez créer une table temporaire em> avec x nombre de colonnes de texte, remplissez-le avec des données, puis traiter les données. P> 

BEGIN;
CREATE TEMPORARY TABLE foo(a TEXT, b TEXT, c TEXT, ...) ON COMMIT DROP;
COPY foo FROM 'file.csv' WITH CSV;
<do the work>
END;

3 commentaires

C'est mieux. :) BTW, vous ne voudrez peut-être pas limiter la durée de vie de la table TEMP à une transaction. Il meurt de toute façon à la fin de la session. De plus, pour une utilisation répétée, vous pouvez créer un type persistant ou une table factice et Créer une table temporaire FOO (comme template_tbl) .

Oui, ma réponse précédente a été tapé trop rapidement sans vérification. Vous n'avez pas eu à me voler quelques points de quelques points, commentaire était suffisant pour que je réalise que c'était un FSCK UP;)

Naaa, le bowvote était tout aussi mérité que mon uppote maintenant. Vous ne devriez pas "taper trop vite sans vérification" pour commencer. C'est ce que sont les bowvotes pour: avertir les gens que ce n'est pas bon. Jamais personnel. :)

7
votes

J'ai eu affaire à quelque chose de similaire et j'ai fini par écrire mon propre module pour renifler des fichiers de données en inspectant le fichier source. Il y a une certaine sagesse parmi tous les Naysayers, mais il peut également y avoir des raisons pour des raisons pour lesquelles cela vaut la peine d'être fait, en particulier lorsque nous n'avons pas de contrôle du format de données d'entrée (par exemple, travailler avec les données ouvertes du gouvernement), voici donc certaines choses que j'ai apprises Dans le processus:

  1. Même si cela prend beaucoup de temps, cela vaut la peine de courir dans l'ensemble du fichier plutôt que d'un petit échantillon de lignes. Plus de temps est gaspillé en ayant une colonne signalée comme numérique qui s'avère avoir du texte toutes les quelques milliers de lignes et ne parvient donc pas à importer.
  2. En cas de doute, échec vers un type de texte, car il est plus facile de les jeter à numériques ou à la date / fois plus tard que d'essayer de déduire les données perdues dans une mauvaise importation.
  3. Vérifiez que des zéros de premier plan dans ce qui apparaissent autrement pour être des colonnes entier et les importer comme texte s'il y en a un problème commun avec les numéros ID / Compte.
  4. Donnez-vous une façon de remplacer manuellement les types détectés automatiquement pour certaines colonnes, de sorte que vous puissiez mélanger une conscience sémantique avec les avantages de taper automatiquement la plupart d'entre eux.
  5. Les champs Date / Heure sont un cauchemar et, dans mon expérience, nécessitent généralement un traitement manuel.
  6. Si vous ajoutez plus tard des données à ce tableau, n'essayez pas de répéter la détection de type - Obtenez les types de la base de données pour assurer la cohérence.

    Si vous pouvez éviter de faire la détection de type automatique, il convient de l'éviter, mais ce n'est pas toujours pratique, alors j'espère que ces conseils sont d'une aide.

1 commentaires

c'est la vraie sagesse ici

0
votes

Bien que ce soit une vieille question, il ne semble pas avoir une réponse satisfaisante et je me suis lutté avec la question exacte de Samen. Avec l'arrivée de SQL Server Management Studio 2018 Edition - et probablement quelque peu avant - une très bonne solution a été offerte par Microsoft.

  1. en SSMS sur un nœud de base de données dans l'explorateur d'objet, cliquez avec le bouton droit de la souris, sélectionnez "Tâches" et choisissez "Données d'importation";
  2. Choisissez "Fichier plat" comme source et, dans la section Général, accédez à votre fichier .csv. Une note importante ici: Assurez-vous qu'il n'y a pas de table dans votre serveur SQL cible correspondant au nom des fichiers;
  3. Dans la section avancée, cliquez sur "Suggérer des types" et dans la boîte de dialogue suivante, entrez de préférence le nombre total de lignes dans votre fichier ou, si c'est trop, un nombre suffisant pour couvrir toutes les valeurs possibles (cela prend une tandis que);
  4. Cliquez sur Suivant et dans l'étape sous-associée, connectez-vous à votre serveur SQL. Maintenant, chaque marque a sa propre saveur de types de données, mais vous devriez obtenir un bon ensemble de pointeurs pertinents pour votre goût plus tard. J'ai testé cela à l'aide du client Native Server SQL Server 11.0. S'il vous plaît laissez vos commentaires pour les autres fournisseurs en tant que réponse à cette solution;
  5. ici il vient ... cliquez sur 'Modifier les mappages' ...;
  6. Cliquez sur 'Modifier SQL' et Voila, une belle instruction SQL avec tous les types de données découverts;
  7. Cliquez sur à la fin, sélectionnez "Exécuter immédiatement" pour voir toutes vos colonnes Créées .csv créées avec des types Appâstes dans votre serveur SQL.

    Extra: Si vous exécutez deux fois les étapes ci-dessus, exactement de la même manière avec le même fichier, la première boucle utilisera la relève «Create Table ...», mais la deuxième fois sautera la création de table. Si vous enregistrez la deuxième exécution sous forme de fichier SSIS (Integration Services), vous pouvez réorganiser ultérieurement toute la configuration sans numériser le fichier .csv.

0 commentaires

Articles qui pourrait vous intéresser :

Laravel whereRaw - comment échapper à un '?'
Pgadmin ne se charge pas
Interroger les données de PostgreSQL à partir de deux tables
Erreur: impossible d'insérer plusieurs commandes dans une instruction préparée