Uploading and Importing CSV File to SQL Server

ASP.NET:  Automatic Database Table Creation

A few 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 here in C# Corner.

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, I used 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.

schema.ini

Taken from the documentation: 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.

Points to remember 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

To get started, let's go ahead and create a simple blank database. Just for the 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 WebApplication project.

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

Now add a 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: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" />

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.

using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
namespace WebFormsDemo
{
    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;
            int maxLength = 0;
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(string.Format("CREATE TABLE {0} (", tableName));
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                dataType = dt.Columns[i].DataType.ToString();
                if (dataType == "System.Int32")
                {
                    sqlDBType = "INT";
                } else if (dataType == "System.String")
                {
                    sqlDBType = "NVARCHAR";
                    maxLength = dt.Columns[i].MaxLength;
                }
                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 = "SELECT * FROM [" + fileInfo.Name + "]";
                    string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
                    // 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 is configured from the web.config file.

CreateDatabaseTable() is method that accepts two (2) parameters that are the DataTable and the filename. This method is where the automatic creation of the database table is done based on the source DataTable and the filename of the CSV file.

LoadDataToDatabase() is a method that accepts three (3) parameters that are 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 notice, I also added some basic trappings and validations within that method.

Now let's create some simple data in a CSV format to test this importing utility. Just for the simplicity of this demo, let's create a CSV file and name it "Employee" and add the following data to it (take note of the format).

1. Vincent Maverick,Durano,[email protected]
2. Vianne,Durano,[email protected]
3. Xhaiden,Durano,[email protected]
4. Angel Christine,Durano,[email protected]
5. Kurt,Durano,[email protected]
6. Erika,Bird,[email protected]
7. Michelle,Lorenzana,[email protected]
8. Lilibeth,Tree,[email protected]
9. Bon,Bolger,[email protected]
10. Brian,Jones,[email protected]

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

Running the page will display something like the following in the browser.

After browsing the CSV file



After importing



Now if we look at the database that we created earlier you'll notice that the Employee table has been successfully created with the imported data on it. See the following screenshot.



That's it! I hope you'll find this article useful.