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:
- The schema information file must always named "schema.ini".
- The schema.ini file must be kept in the same directory where the CSV file exists.
- The schema.ini file must be created before reading the CSV file.
- 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:
<%@ 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:
<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.
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{
}
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;
FileUpload1.SaveAs(csvFilePath);
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);
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
After Importing
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:
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.
