231 votes

Importer un fichier CSV dans le serveur SQL

Je cherche de l'aide pour importer un .csv dans le serveur SQL en utilisant BULK INSERT et j'ai quelques questions de base.

Questions :

  1. Les données du fichier CSV peuvent avoir , (virgule) entre les deux (Ex : description), alors comment puis-je faire l'importation en manipulant ces données ?

  2. Si le client crée le CSV à partir d'Excel, les données comportant des virgules sont incluses dans le fichier. "" (guillemets) [comme dans l'exemple ci-dessous], comment l'importation peut-elle gérer cela ?

  3. Comment savoir si certaines lignes contiennent des données erronées, que l'importation ignore ? (l'importation ignore-t-elle les lignes qui ne sont pas importables ?)

Voici l'exemple de CSV avec l'en-tête :

Name,Class,Subject,ExamDate,Mark,Description
Prabhat,4,Math,2/10/2013,25,Test data for prabhat.
Murari,5,Science,2/11/2013,24,"Test data for his's test, where we can test 2nd ROW, Test."
sanjay,4,Science,,25,Test Only.

Et l'instruction SQL à importer :

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

1 votes

Peut être SSMS : Comment importer (copier/coller) des données depuis Excel ? peut aider (Si vous ne voulez pas utiliser BULK NSERT ou n'ont pas les autorisations pour le faire).

1 votes

Cela n'a rien à voir, mais votre exemple de fichier CSV devrait se charger sans problème dans MS Access.

198voto

mr_eclair Points 9503

Importation CSV basée sur SQL Server

1) Les données du fichier CSV peuvent avoir , (virgule) entre les deux (Ex : description), alors comment puis-je faire l'importation en manipulant ces données ?

Solution

Si vous utilisez , (virgule) comme délimiteur, il n'y a aucun moyen de faire la différence entre une virgule comme terminateur de champ et une virgule dans vos données. J'utiliserais un autre FIELDTERMINATOR comme || . Le code ressemblerait à ceci, qui gère parfaitement les virgules et les barres obliques simples.

2) Si le client crée le csv à partir d'Excel alors les données qui ont virgule sont enfermées dans " ... " (guillemets) [comme l'exemple ci-dessous exemple], comment l'importation peut-elle gérer cela ?

Solution

Si vous utilisez l'insertion en masse, il n'y a aucun moyen de gérer les guillemets doubles. données seront insérées avec des guillemets dans les lignes. Après avoir inséré les données dans la table, vous pouvez remplacer ces doubles guillemets par ' '.

update table
set columnhavingdoublequotes = replace(columnhavingdoublequotes,'"','')

3) Comment savoir si certaines lignes contiennent des données erronées, que l'importation ignore ? (l'importation saute-t-elle les lignes qui ne sont pas importables) ?

Solution

Pour gérer les lignes qui ne sont pas chargées dans la table en raison de données ou d'un format non valides, on pourrait traiter en utilisant Propriété ERRORFILE spécifier le nom du fichier d'erreur, il écrira les lignes ayant une erreur ayant une erreur dans le fichier d'erreur. Le code devrait ressembler à ceci.

BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

1 votes

Merci pour votre aide. Reg the Solution#1 : Pouvons-nous créer un fichier de valeurs séparées || à partir d'Excel ? Parce qu'environ 20 % des fichiers sources sont créés à l'aide d'Excel par le client.

0 votes

@Prabhat Comment chargez-vous des fichiers Excel dans le serveur SQL ?

0 votes

Ce ne sont pas des fichiers Excel que je charge. Le client utilise Excel pour créer des fichiers CSV (pour 20% des données sources que notre application importe). Et je me demandais, si nous créons des fichiers csv en utilisant Excel, comment nous pouvons avoir || comme séparateur de valeur de colonne ?

49voto

Zd8n8k Points 1

Vous devez d'abord créer une table dans votre base de données dans laquelle vous allez importer le fichier CSV. Une fois la table créée, suivez les étapes ci-dessous.

- Connectez-vous à votre base de données en utilisant SQL Server Management Studio

- Cliquez à droite sur votre base de données et sélectionnez Tasks -> Import Data...

- Cliquez sur le bouton Next > bouton

- Pour la source de données, sélectionnez Flat File Source . Utilisez ensuite le bouton Parcourir pour sélectionner le fichier CSV. Prenez le temps de configurer la manière dont vous souhaitez que les données soient importées avant de cliquer sur le bouton Next > bouton.

- Pour la destination, sélectionnez le fournisseur de base de données correct (par exemple, pour SQL Server 2012, vous pouvez utiliser SQL Server Native Client 11.0). Entrez le nom du serveur. Vérifiez le Use SQL Server Authentication bouton radio. Entrez le nom de l'utilisateur, le mot de passe et la base de données avant de cliquer sur le bouton Next > bouton.

- Dans la fenêtre Sélectionner les tables et vues sources, vous pouvez modifier les mappages avant de cliquer sur le bouton Next > bouton.

- Vérifiez le Run immediately et cliquez sur le bouton Next > bouton.

- Cliquez sur le Finish pour exécuter le paquet.

Ce qui précède a été trouvé sur ce site site web (Je l'ai utilisé et testé) :

47 votes

Il serait souhaitable que vous attribuiez la paternité du projet à la page où vous avez copié/collé cette réponse...

1 votes

Il n'est pas nécessaire de précréer la table, elle peut être créée au cours du processus d'importation.

2 votes

J'adore le fait qu'il suffise de faire un copier-coller d'une page web avec la ligne oh combien utile "Passez un peu de temps à configurer la façon dont vous voulez que les données soient importées". . C'était tout Je cherche : Je n'arrive pas du tout à le configurer !

34voto

Oleg Points 11

2) Si le client crée le csv à partir d'Excel, alors les données qui ont des virgule sont placées entre " ... "(guillemets doubles) [comme dans l'exemple exemple], comment l'importation peut-elle gérer cela ?

Vous devez utiliser les options FORMAT = 'CSV', FIELDQUOTE = '"' :

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

4 votes

Notez que le spécificateur FORMAT n'est disponible que depuis SQL Server 2017.

15voto

Sachin Kainth Points 7842

La meilleure façon, la plus rapide et la plus simple, de résoudre le problème des virgules dans les données est d'utiliser Excel pour enregistrer un fichier séparé par des virgules après avoir défini le paramètre de séparateur de liste de Windows sur autre chose qu'une virgule (comme un tube). Vous obtiendrez alors un fichier séparé par un pipe (ou autre) que vous pourrez ensuite importer. Cette méthode est décrite ci-dessous aquí .

7voto

jarvis24 Points 1

Comme ils n'utilisent pas l'assistant d'importation SQL, les étapes sont les suivantes :

enter image description here

  1. Cliquez à droite sur la base de données dans l'option tâches à importer données,

  2. Une fois que l'assistant est ouvert, nous sélectionnons le type de données à impliquer. Dans ce cas, ce serait le

Source du fichier plat

Nous sélectionnons le fichier CSV, vous pouvez configurer le type de données des tables dans le CSV, mais il est préférable de l'amener depuis le CSV.

  1. Cliquez sur Next et sélectionnez dans la dernière option qui est

Client SQL

En fonction de notre type d'authentification nous le sélectionnons, une fois ceci fait, une option très importante arrive.

  1. Nous pouvons définir l'id de la table dans le CSV (il est recommandé que les colonnes du CSV soient appelées de la même manière que les champs de la table). Dans l'option Edit Mappings nous pouvons voir l'aperçu de chaque table avec la colonne de la feuille de calcul, si nous voulons que l'assistant insère l'id par défaut nous laissons l'option décochée.

Activer l'insertion d'un identifiant

(généralement pas à partir de 1), au lieu de cela, si nous avons une colonne avec l'id dans le CSV, nous sélectionnons l'insertion de l'id, l'étape suivante est de terminer l'assistant, nous pouvons revoir les changements ici.

D'autre part, dans la fenêtre suivante peuvent apparaître des alertes, ou encore l'idéal est d'ignorer ces avertissements, mais seulement s'ils laissent des erreurs. il est nécessaire de faire attention.

Ce lien contient des images .

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X