Import Comma Delimited data into Data Table

Question

What's the easiest way to import a CSV into a database via web application?

UPDATE:

Small example of CSV:

stop_id, stop_name, stop_desc, stop_lat, stop_lon, zone_id, stop_url
TR, Trenton, , 40.2177778, -74.7550000, 6, 
LVTN, Levittown-Tullytown, , 40.1402778, -74.8169444, 5, 
BRST, Bristol, , 40.1047222, -74.8547222, 4, 
CROY, Croydon, , 40.0936111, -74.9066667, 4, 
EDGT, Eddington, , 40.0830556, -74.9336111, 4, 
CORN, Cornwells Heights, , 40.0716667, -74.9522222, 3, 
TORR, Torresdale, , 40.0544444, -74.9844444, 3, 
HOLM, Holmesburg Jct, , 40.0327778, -75.0236111, 2, 

Answer 1

That's really going to depend on your situation. Is this an internal text file being loaded into SqlServer 2005 or later database (also internal)? If so, you can write an SSIS package (or, more simply, use SQL Server's "Import Data..." wizard) and it will just read it for you.

Is there reason to worry about unescaped commas (Say, a Name column with name formats in Last, First)? Then you'll need to compensate for that... (numerous question here on SO address this very issue).

Is the File not internal, that is, someone is uploading it and it needs to be read real-time? Then an SSIS package is still your best bet, and set up some code (Modern FTP Servers can often call executables or batch files) to kick off the SSIS package when the file arrives.

This is really a very broad kind of question, and without more detail, I'm not sure how specific the answers you're going to get will be...

Answer 2

If you take the Portable and Efficient Generic Parser for Flat Files and create a new Visual C# Console application that has a reference to it, the following code will load the contents of a CSV file into a DataTable:

using GenericParsing;
using System.Data;

namespace CsvToDataTable
{
    public class Program
    {
        static void Main(string[] args)
        {
            var pathOfCsvFile = @"C:\MyFile.csv";
            var adapter = new GenericParsing.GenericParserAdapter(pathOfCsvFile);
            DataTable data = adapter.GetDataTable();
        }
    }
}

The next step would be to save the data in data to your database server. This code assumes that the structure of the CSV file matches exactly that of a table called TableName in your database:

private static void SaveDataToDatabase(DataTable data)
{
    var connectionString = "CONNECTION STRING GOES HERE";
    var commandText = "INSERT INTO [databasename].[dbo].[TableName] " +
        "(stop_id, stop_name, stop_desc, stop_lat, stop_lon, zone_id, stop_url) " +
        "VALUES (@stop_id, @stop_name, @stop_desc, @stop_lat, @stop_lon, @zone_id, @stop_url)";

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        foreach (DataRow row in data.Rows)
        {
            using (var command = new SqlCommand(commandText, connection))
            {
                command.Parameters.AddWithValue("@stop_id", row["stop_id"]);
                command.Parameters.AddWithValue("@stop_name", row["stop_name"]);
                command.Parameters.AddWithValue("@stop_desc", row["stop_desc"]);
                command.Parameters.AddWithValue("@stop_lat", row["stop_lat"]);
                command.Parameters.AddWithValue("@stop_lon", row["stop_lon"]);
                command.Parameters.AddWithValue("@zone_id", row["zone_id"]);
                command.Parameters.AddWithValue("@stop_url", row["stop_url"]);

                command.ExecuteNonQuery();
            }
        }
    }
}

You'd then want to add a call to SaveDataToDatabase in your Main method. I think there's enough code here for you to tweak it to suit your purpose.