How to read a CSV file into an SQL table

Question

I'm trying to read a CSV file into a table that I have created in Visual Studio. I want to validate the values in the file to see if they are correct, if all the values pass the checks, it will go into the table. If any values are not correct, an error report will be created using a JSON file.

I have already got some test data ready but I'm not sure how to separate the correct data from the incorrect data after the checker are complete.

public partial class NHSBatchChecker : Form
{

    public NHSBatchChecker()
    {
        InitializeComponent();
    }

    public void button1_Click(object sender, EventArgs e)
    {

        OpenFileDialog openFileDialog1 = new OpenFileDialog();
        openFileDialog1.InitialDirectory = @"C:\Users\wy6282\Desktop\VS\NHSBATCHCHECKER\Test.txt"; // Start in C: drive
        openFileDialog1.Title = "Browse Text Files";
        openFileDialog1.RestoreDirectory = true;
        openFileDialog1.DefaultExt = "txt"; // Extension of file is txt only
        openFileDialog1.Filter = "Text|*.txt||*.*"; //Only text files allowed
        openFileDialog1.CheckFileExists = true; // Error message if file does not exist
        openFileDialog1.CheckPathExists = true; // Error message if invalid file path

        if (openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            string connectionstring;
            SqlConnection cnn;
            connectionstring = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\wy6282\Desktop\VS\NHSBATCHCHECKER\nhsBatchChecker\Results.mdf;Integrated Security=True";
            cnn = new SqlConnection(connectionstring);
            cnn.Open();

            SqlCommand command;
            SqlDataAdapter adaper = new SqlDataAdapter();
            string sql = "";

            sql = "Insert into Results(NHS Number, Date of Birth, First Name, Last Name, Title, Gender) values()";
            command = new SqlCommand(sql, cnn);

            adaper.InsertCommand = new SqlCommand(sql, cnn);
            adaper.InsertCommand.ExecuteNonQuery();

            command.Dispose();
            cnn.Close();

How do I add my valid records into the sql table?

What is your question? Nothing in the example code has anything to do with CSV files. — AlwaysLearning
– AlwaysLearning, Commented Aug 7, 2019 at 10:13

Andrei Dragotoniu · Accepted Answer · 2019-08-07 10:20:03Z

you're trying to do too much in one go.

rule number 1: always split your problem into manageable junks:

read data from CSV.
filter incorrect data
save filtered data to database.

Now you have 3 distinct pieces of work to focus on.

Reading data from a CSV is trivial, there are many libraries that can help with that. Do a bit of research and pick one.

Create a class which holds the properties you need for validation checks and also those you want saved in the database.

Your goal is to create a list of these objects, one per row in csv. Of course, you may not be able to read everything in one go depending on how much data your csv holds, but you can pick a library which can deal with whatever size you have.

Now you have a list of objects. Write an algorithm which determines what is valid and what not, based on the rules you need. Your goal here is to end up with a possibly smaller list of the same objects, chucking out the invalid ones.
Save whatever is left in the database.

You need to start thinking about how you organize you code, don't just throw everything into the Click event of a button. You can create a model class to hold your objects, maybe create a separate class library where you can put your csv reading method.

Another class library perhaps for your filtering algorithm(s).

Your Click event should be fairly slim and only call library methods when it needs to do something. This is separation of concerns or SOC, which is a Solid Principle.

ASH · Accepted Answer · 2019-08-12 11:38:40Z

I am not sure how you plan to validate the datapoints, but the code below shows how to pull data from a CSV and load it into a SQL Server table.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string server = "EXCEL-PC\\EXCELDEVELOPER";
            string database = "AdventureWorksLT2012";
            string SQLServerConnectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, database);


            string CSVpath = @"C:\Users\Ryan\Documents\Visual Studio 2010\Projects\Bulk Copy from CSV to SQL Server Table\WindowsFormsApplication1\bin"; // CSV file Path
            string CSVFileConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};;Extended Properties=\"text;HDR=Yes;FMT=Delimited\";", CSVpath);

            var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
            string File_Name = string.Empty;

            foreach (var file in AllFiles)
            {
                try
                {
                    DataTable dt = new DataTable();
                    using (OleDbConnection con = new OleDbConnection(CSVFileConnectionString))
                    {
                        con.Open();
                        var csvQuery = string.Format("select * from [{0}]", file.Name);
                        using (OleDbDataAdapter da = new OleDbDataAdapter(csvQuery, con))
                        {
                            da.Fill(dt);
                        }
                    }

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLServerConnectionString))
                    {
                        bulkCopy.ColumnMappings.Add(0, "MyGroup");
                        bulkCopy.ColumnMappings.Add(1, "ID");
                        bulkCopy.ColumnMappings.Add(2, "Name");
                        bulkCopy.ColumnMappings.Add(3, "Address");
                        bulkCopy.ColumnMappings.Add(4, "Country");
                        bulkCopy.DestinationTableName = "AllEmployees";
                        bulkCopy.BatchSize = 0;
                        bulkCopy.WriteToServer(dt);
                        bulkCopy.Close();
                    }

                }
                catch(Exception ex)
                     {
                         MessageBox.Show(ex.Message, "Warning!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                     }
            }
        }
    }
}

Thanks but it needs to be in C# as that is one of the requirements.

Collectives™ on Stack Overflow

How to read a CSV file into an SQL table

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related