Importer un fichier CSV dans le serveur SQL

Question

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
)

Answer 1

Vous devez d'abord importer le fichier CSV dans le tableau des données.

Ensuite, vous pouvez insérer des rangs en vrac en utilisant SQLBulkCopy

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

Answer 2

Voici comment je résoudrais le problème :

1. Enregistrez simplement votre fichier CSV comme une feuille XLS dans Excel (en faisant cela, vous n'aurez pas à vous soucier des délimiteurs. Le format de feuille de calcul d'Excel sera lu comme une table et importé directement dans une table SQL)

2. Importer le fichier en utilisant SSIS

3. Écrivez un script personnalisé dans le gestionnaire d'importation pour omettre/modifier les données que vous recherchez (ou exécutez un script maître pour examiner les données que vous cherchez à supprimer).

Bonne chance.

Answer 3

Je sais que ce n'est pas la solution exacte à la question ci-dessus, mais pour moi, c'était un cauchemar lorsque j'essayais de Copie des données d'une base de données située sur un serveur séparé vers mon serveur local.

J'essayais de faire ça en commençant par exporter des données du serveur vers CSV/txt et ensuite import sur ma table locale.

Les deux solutions : avec l'écriture de la requête à importer CSV ou en utilisant le SSMS Importer des données L'assistant produisait toujours des erreurs (les erreurs étaient très générales, disant qu'il y a un problème d'analyse syntaxique). Et même si je ne faisais rien de spécial, simplement exporter a CSV et ensuite essayer de import CSV au local DB les erreurs étaient toujours là.

J'essayais de regarder la section cartographie et l'aperçu des données, mais il y avait toujours un grand désordre. Et je sais que le principal problème venait de l'un des éléments suivants . table qui contenait JSON y SQL L'analyseur syntaxique le traitait mal.

J'ai fini par trouver une autre solution et je veux la partager au cas où quelqu'un d'autre aurait un problème similaire.

---

Ce que j'ai fait, c'est que j'ai utilisé l'option Assistant d'exportation sur le serveur externe.

Voici les étapes à suivre pour répéter le même processus :
1) Cliquez avec le bouton droit de la souris sur la base de données et sélectionnez Tasks -> Export Data...

2) Lorsque l'assistant s'ouvre, choisissez Suivant et à la place de "Data Source :" choisissez "SQL Server Native Client".

Dans le cas d'un serveur externe, vous devrez très probablement choisir "Use SQL Server Authentication" pour le "Authentication Mode :".

3) Après avoir frappé Suivant vous devez sélectionner le Désinfection .
Pour cela, sélectionnez à nouveau "SQL Server Native Client".
Cette fois-ci, vous pouvez fournir vos données locales (ou d'autres données externes). DB ) DB .

4) Après avoir cliqué sur le bouton Suivant, vous avez deux options : soit copier l'ensemble du tableau d'une table à l'autre. DB vers une autre ou écrire la requête pour spécifier les données exactes à copier. Dans mon cas, je n'avais pas besoin de la table entière (elle était trop grande), mais juste d'une partie de celle-ci, j'ai donc choisi "Écrire une requête pour spécifier les données à transférer".

Je vous suggère d'écrire et de tester la requête sur un éditeur de requêtes séparé avant de passer à l'assistant.

5) Et enfin, vous devez spécifier la table de destination où les données seront sélectionnées.

Je suggère de le laisser comme [dbo].[Query] ou d'un autre produit personnalisé Table si vous rencontrez des erreurs lors de l'exportation des données ou si vous n'êtes pas sûr des données et souhaitez les analyser plus en profondeur avant de passer à la table exacte que vous souhaitez.

Et maintenant, allez directement à la fin de l'Assistant en appuyant sur Suivant/Fini boutons.

Answer 4

Toutes les réponses fournies ici fonctionnent très bien si vos données sont "propres" (pas de violations de contraintes de données, etc.) et si vous avez accès à la mise en place du fichier sur le serveur. Certaines des réponses fournies ici s'arrêtent à la première erreur (violation de PK, erreur de perte de données, etc.) et vous donnent une erreur à la fois si vous utilisez la tâche d'importation intégrée de SSMS. Si vous voulez rassembler toutes les erreurs en une fois (au cas où vous voudriez dire à la personne qui vous a donné le fichier .csv de nettoyer ses données), je recommande la réponse suivante. Cette réponse vous donne également une flexibilité totale puisque vous "écrivez" le SQL vous-même.

Remarque : je suppose que vous utilisez un système d'exploitation Windows et que vous avez accès à Excel et à SSMS. Si ce n'est pas le cas, je suis sûr que vous pouvez adapter cette réponse à vos besoins.

1. Avec Excel, ouvrez votre fichier .csv. Dans une colonne vide, vous écrirez une formule qui construira les données individuelles de l INSERT des déclarations comme =CONCATENATE("INSERT INTO dbo.MyTable (FirstName, LastName) VALUES ('", A1, "', '", B1,"')", CHAR(10), "GO") où A1 est une cellule qui contient les données du prénom et A2 celles du nom de famille, par exemple.

   • CHAR(10) ajoute un caractère de nouvelle ligne au résultat final et GO nous permettra d'exécuter cette INSERT et passer à la suivante même s'il y a des erreurs.

2. Mettez en surbrillance la cellule avec votre =CONCATENATION() formule

3. Shift + End pour mettre en évidence la même colonne dans le reste de vos lignes.

4. Dans le ruban > Accueil > Édition > Remplissage > Cliquez vers le bas

   • Cette formule s'applique tout au long de la feuille, ce qui vous évite de devoir copier-coller, faire glisser, etc. des milliers de lignes à la main.

5. Ctrl + C pour copier le SQL formulé INSERT déclarations

6. Coller dans SSMS

7. Vous remarquerez qu'Excel, probablement de manière inattendue, a ajouté des guillemets doubles autour de chacun de vos INSERT y GO des commandes. Il s'agit d'une "fonctionnalité" ( ?) de copier des valeurs multi-lignes à partir d'Excel. Vous pouvez simplement rechercher et remplacer "INSERT y GO" con INSERT y GO respectivement pour nettoyer ça.

8. Enfin, vous êtes prêt à exécuter votre processus d'importation

9. Une fois le processus terminé, vérifiez l'absence d'erreurs dans la fenêtre des messages. Vous pouvez sélectionner tout le contenu (Ctrl + A) et le copier dans Excel et utiliser un filtre de colonne pour supprimer tous les messages réussis et il vous reste toutes les erreurs.

Ce processus sera certainement plus long que les autres réponses ici, mais si vos données sont "sales" et pleines de violations SQL, vous pouvez au moins rassembler toutes les erreurs en une seule fois et les envoyer à la personne qui vous a donné les données, si tel est votre scénario.

Answer 5

Importez le fichier dans Excel en ouvrant d'abord Excel, puis en allant dans DONNEES, importer à partir d'un fichier TXT, choisissez l'extension csv qui préservera les valeurs préfixées 0, et enregistrez cette colonne en tant que TEXTE parce qu'Excel laissera tomber le 0 de tête sinon (NE PAS double-cliquer pour ouvrir avec Excel si vous avez des données numériques dans un champ commençant par un 0 [zéro]). Ensuite, enregistrez le tout dans un fichier texte délimité par des tabulations. Choisissez TEXTE pour que les guillemets au milieu d'une chaîne de caractères dans un champ comme VotreCompagnie,LLC soient également préservés...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

J'aimerais pouvoir utiliser les fonctionnalités FORMAT et Fieldquote, mais elles ne semblent pas être prises en charge par ma version de SSMS.