Click here to Skip to main content
15,867,895 members
Articles / Web Development / ASP.NET

ASP.NET WebForms: Uploading and Importing CSV File to SQL Server

Rate me:
Please Sign up or sign in to vote.
4.70/5 (7 votes)
18 Aug 2016CPOL4 min read 46.2K   5K   16   6
A quick demo that highlights how to upload and import CSV file to SQL Server database in ASP.NET WebForms.

Introduction

Many years ago, I was working with a small internal project that involves uploading and importing CSV files to a SQL Server database and thought I'd write an article that demonstrates my simple implementation for the project as a reference to others who might need this feature.

As some may already know, importing a CSV file into SQL Server is easy and simple but difficulties arise when the CSV file contains many columns with various data types. Basically, the provider cannot differentiate the data types of the columns or the rows, so it blindly considers them as a data type based on the first few rows and ignores all the data that does not match the data type. To overcome this problem, we will use a schema.ini file to define the data type stored within the CSV file, and allow the provider to read that and recognize the exact data types of each column. 

Using the code

Schema.ini

Taken from this post: The Schema.ini is an information file, used to define the data structure and format of each column that contains data in the CSV file. If schema.ini file exists in the directory, Microsoft.Jet.OLEDB provider automatically reads it and recognizes the data type information of each column in the CSV file. Thus, the provider intelligently avoids the misinterpretation of data types before inserting the data into the database. For more information see: Schema.ini File (Text File Driver)

Points to note before creating schema.ini:

  1. The schema information file must always named "schema.ini".
  2. The schema.ini file must be kept in the same directory where the CSV file exists.
  3. The schema.ini file must be created before reading the CSV file.
  4. The first line of the schema.ini, must the name of the CSV file, followed by the properties of the CSV file and then the properties of the each column in the CSV file.

The following is an example of how the schema should look:

[Employee.csv] 
ColNameHeader = False 
Format = CSVDelimited 
DateTimeFormat = dd-MMM-yyyy 
Col1 = EmployeeID Long 
Col2 = EmployeeFirstName Text Width 100 
Col3 = EmployeeLastName Text Width 50 
Col4 = EmployeeEmailAddress Text Width 50

Just for the simplicity, we will be using the schema above for this demo. Now, let's go ahead and create a simple blank database. You can definitely name your database to whatever you like, but for the sole purpose of this demo, I created a database called DemoDB.

After creating the database, go ahead and fire up Visual Studio and then create a new WebForm's Web Application project. 

Under the root application create a folder and name it as “UploadedCSVFiles” and then place the schema.ini on that folder. The uploaded CSV files will also be stored in this folder after the user imports the file.

ASPX Markup

Now add a new WebForm page to the project. Create the HTML markup with the following server controls.

  • 1 FileUpload
  • 1 Button
  • 3 Labels

The HTML markup should look something like this:

ASP.NET
<%@ Page Language="C#" AutoEventWireup="true" 
         CodeBehind="CSVToSQL.aspx.cs" 
         Inherits="CSVToSQL.CSVToSQL" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Importing CSV to SQL Server Demo</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:FileUpload ID="FileUpload1" runat="server" />  
        <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="btnImport_Click"/>  
        <br />  
        <asp:Label ID="Label1" runat="server" ForeColor="Blue" />  
        <br />  
        <asp:Label ID="Label2" runat="server" ForeColor="Green" />  
        <br />  
        <asp:Label ID="lblError" runat="server" ForeColor="Red" /> 
    </div>
    </form>
</body>
</html>

Web.Config

Next is, we need to define the connection string for us to communicate with our database. Here's an example of a connection string configuration defined within <configuration> node in web.config file:

XML
  <connectionStrings>
    <add name="DBConnection" 
         connectionString="Data Source=(localdb)\MSSQLLocalDB;
                          Initial Catalog=DemoDB;
                          Integrated Security=True;
                          Connect Timeout=30;
                          Encrypt=False;
                          TrustServerCertificate=True;
                          ApplicationIntent=ReadWrite;
                          MultiSubnetFailover=False"
         providerName="System.Data.SqlClient"/>
  </connectionStrings>

Code Behind

After that, we can now proceed with the codes for uploading and importing the CSV file to the SQL Server database. The following is the full code.

C#
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

namespace CSVToSQL
{
    public partial class CSVToSQL : System.Web.UI.Page
    {
        private string GetConnectionString(){
            return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        }

        private void CreateDatabaseTable(DataTable dt, string tableName){
            string sqlQuery = string.Empty;
            string sqlDBType = string.Empty;
            string dataType = string.Empty;
          
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(string.Format("CREATE TABLE {0} (", tableName));

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                int maxLength = 0;
                dataType = dt.Columns[i].DataType.ToString();
                if (dataType == "System.Int32"){
                    sqlDBType = "INT";
                }
                else if (dataType == "System.String"){
                    sqlDBType = "NVARCHAR";
                    maxLength = dt.Columns[i].MaxLength;
                }
                else{
                    //do something else
                }

                if (maxLength > 0)
                    sb.AppendFormat(string.Format("{0} {1} ({2}), ", dt.Columns[i].ColumnName, sqlDBType, maxLength));
                else
                    sb.AppendFormat(string.Format("{0} {1},", dt.Columns[i].ColumnName, sqlDBType));
            }

            sqlQuery = sb.ToString();
            sqlQuery = sqlQuery.Trim().TrimEnd(',');
            sqlQuery = sqlQuery + " )";

            using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn))
                {
                    sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();
                }
            }
        }

        private void LoadDataToDatabase(string tableName, string fileFullPath, string delimeter)
        {
            string sqlQuery = string.Empty;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(string.Format("BULK INSERT {0} ", tableName));
            sb.AppendFormat(string.Format(" FROM '{0}'", fileFullPath));
            sb.AppendFormat(string.Format(" WITH ( FIELDTERMINATOR = '{0}' , ROWTERMINATOR = '\n' )", delimeter));
            sqlQuery = sb.ToString();

            using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn))
                {
                    sqlCmd.ExecuteNonQuery();
                    sqlConn.Close();
                }
            }
        }

        private void UploadAndProcessFile()
        {
            if (FileUpload1.HasFile)
            {
                FileInfo fileInfo = new FileInfo(FileUpload1.PostedFile.FileName);
                if (fileInfo.Name.Contains(".csv"))
                {
                    string fileName = fileInfo.Name.Replace(".csv", "").ToString();
                    string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;

                    //Save the CSV file in the Server inside 'UploadedCSVFiles'   
                    FileUpload1.SaveAs(csvFilePath);

                    //Fetch the location of CSV file   
                    string filePath = Server.MapPath("UploadedCSVFiles") + "\\";

                    string strSql = string.Format("SELECT * FROM [{0}]", fileInfo.Name);
                    string strCSVConnString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='text;HDR=YES;'", filePath);

                    // load the data from CSV to DataTable   
                    DataTable dtCSV = new DataTable();
                    DataTable dtSchema = new DataTable();
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString))
                    {
                        adapter.FillSchema(dtCSV, SchemaType.Mapped);
                        adapter.Fill(dtCSV);
                    }

                    if (dtCSV.Rows.Count > 0)
                    {
                        CreateDatabaseTable(dtCSV, fileName);
                        Label2.Text = string.Format("The table ({0}) has been successfully created to the database.", fileName);
                        string fileFullPath = filePath + fileInfo.Name;
                        LoadDataToDatabase(fileName, fileFullPath, ",");
                        Label1.Text = string.Format("({0}) records has been loaded to the table {1}.", dtCSV.Rows.Count, fileName);
                    }
                    else
                    {
                        lblError.Text = "File is empty.";
                    }
                }
                else
                {
                    lblError.Text = "Unable to recognize file.";
                }
            }
        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
            UploadAndProcessFile();
        }
    }
}

The code above consists of the following four (4) private methods:

GetConnectionString() is a method that returns a string. This method basically gets the connection string that we have configured from the web.config file.

CreateDatabaseTable() is method that accepts two (2) parameters: a DataTable that holds the data and the file name. This method is where the automatic creation of the database table is done based on the parameters provided.

LoadDataToDatabase() is a method that accepts three (3) parameters: the tableName, fileFullPath and the delimeter value. This method is where the actual importing of data from the CSV to the SQL Server database table happens.

UploadAndProcessFile() handles the uploading of the CSV file to a specified location. This is also where the CreateDatabaseTable() and LoadDataToDatabase() are being called. If you have noticed, we also added some basic trappings and validations within that method.

Now let's create some simple data in a CSV format to test the importing utility. Let's create a CSV file and name it "Employee.csv", and then add the following data to it (take note of the format):

1,Vincent Maverick,Durano,email1@email.com
2,Vianne Maverich,Durano,email2@email.com
3,Vynn Markus,Durano,email3@email.com
4,Michelle,Lorenzana,email4@email.com
5,Xhaiden,Durano,email5@email.com
6,Angel Christine,Durano,email6@email.com
7,Kurt,Durano,email7@email.com
8,Lilibeth,Durano,email8@email.com
9,Warrior,Martinez,email9@email.com
10,Rhamz,Lao,email10@email.com

Now save the newly created CSV file in some location in your hard drive. 

Output

Running the page will result something this:

After browsing the CSV file

Image 1

After Importing

Image 2

Now if we look at the database that we have created earlier, you'll notice that the Employee table has been successfully created with the imported data just like the figure shown below:

Image 3

Summary

In this article, we've learned how to create a simple web utility that allow us to upload and import CSV file to SQL Server database.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
A code monkey who loves to drink beer, play guitar and listen to music.

My Tech Blog: https://vmsdurano.com/
My Youtube Channel: https://www.youtube.com/channel/UCuabaYm8QH4b1MAclaRp-3Q

I currently work as a Solutions Architect and we build "cool things" to help people improve their health.

With over 14 years of professional experience working as a Sr. Software Engineer specializing mainly on Web and Mobile apps using Microsoft technologies. My exploration into programming began at the age of 15;Turbo PASCAL, C, C++, JAVA, VB6, Action Scripts and a variety of other equally obscure acronyms, mainly as a hobby. After several detours, I am here today on the VB.NET to C# channel. I have worked on Web Apps + Client-side technologies + Mobile Apps + Micro-services + REST APIs + Event Communication + Databases + Cloud + Containers , which go together like coffee crumble ice cream.

I have been awarded Microsoft MVP each year since 2009, awarded C# Corner MVP for 2015, 2016,2017 and 2018, CodeProject MVP, MVA, MVE, Microsoft Influencer, Dzone MVB, Microsoft ASP.NET Site Hall of Famer with All-Star level and a regular contributor at various technical community websites such as CSharpCorner, CodeProject, ASP.NET and TechNet.

Books written:
" Book: Understanding Game Application Development with Xamarin.Forms and ASP.NET
" Book (Technical Reviewer): ASP.NET Core and Angular 2
" EBook: Dockerizing ASP.NET Core and Blazor Applications on Mac
" EBook: ASP.NET MVC 5- A Beginner's Guide
" EBook: ASP.NET GridView Control Pocket Guide

Comments and Discussions

 
QuestionSkip firs two lines Pin
Member 135049128-Nov-17 19:34
Member 135049128-Nov-17 19:34 
Hi,

Is there any way to skip first to lines? my csv files look like this:

,,,,, Files Processed Report,,,,,,,
,,,,,VIC,,,,
,,,,,"Thursday, 21 September 2017",,,,
Box_Number,Batch_Number,File_Number,

Many thanks
Generalelse if statement for datetime Pin
Fergus Tay7-Jun-17 22:23
Fergus Tay7-Jun-17 22:23 
GeneralRe: else if statement for datetime Pin
Vincent Maverick Durano10-Jun-17 18:17
professionalVincent Maverick Durano10-Jun-17 18:17 
BugElse if statement for DateTime Pin
Fergus Tay7-Jun-17 21:59
Fergus Tay7-Jun-17 21:59 
GeneralRe: Else if statement for DateTime Pin
Vincent Maverick Durano10-Jun-17 18:18
professionalVincent Maverick Durano10-Jun-17 18:18 
GeneralRe: Else if statement error Pin
Fergus Tay10-Jun-17 22:20
Fergus Tay10-Jun-17 22:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.