Few weeks ago I was working with
a small internal project that involves importing CSV
file to
Sql Server database and thought I'd share the simple implementation that I did on the project.
In this post I will demonstrate how to upload and import CSV
file to
SQL Server database. As some may have already know, importing CSV
file to
SQL Server is easy and simple but difficulties arise when the CSV
file contains, many columns with different data types. Basically, the provider cannot differentiate data types between the columns or the rows, blindly it will consider them as
a data type based on first few rows and leave all the data which does not match the data type. To overcome this problem, I used schema.ini
file to define the data type of the CSV
file and allow the provider to read that and recognize the exact data types of each column.
Now what is schema.ini?
Taken from the documentation: The Schema.ini is
a 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: http://msdn.microsoft.com/en-us/library/ms709353%28VS.85%29.aspx
Points to remember before creating schema.ini:
1. The schema information
file, must always named as '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.
Here's an example of how the schema looked like:
[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 lets's go
a head and create
a simple blank database. Just for the purpose of this demo I created
a database called TestDB.
After creating the database then lets go
a head and fire up Visual Studio and then create
a new WebApplication project.
Under the root application create
a folder called 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
in the project and set up the HTML mark up and add one (1) FileUpload control one(1)Button and three (3) Label controls.
After that we can now proceed with the codes for uploading and importing the CSV
file to
SQL Server database. Here are the full code blocks below:
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: using System.Data.OleDb;
5: using System.IO;
6: using System.Text;
7:
8: namespace WebApplication1
9: {
10: public partial class CSVToSQLImporting : System.Web.UI.Page
11: {
12: private string GetConnectionString()
13: {
14: return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
15: }
16: private void CreateDatabaseTable(DataTable dt, string tableName)
17: {
18:
19: string sqlQuery = string.Empty;
20: string sqlDBType = string.Empty;
21: string dataType = string.Empty;
22: int maxLength = 0;
23: StringBuilder sb = new StringBuilder();
24:
25: sb.AppendFormat(string.Format("CREATE TABLE {0} (", tableName));
26:
27: for (int i = 0; i < dt.Columns.Count; i++)
28: {
29: dataType = dt.Columns[i].DataType.ToString();
30: if (dataType == "System.Int32")
31: {
32: sqlDBType = "INT";
33: }
34: else if (dataType == "System.String")
35: {
36: sqlDBType = "NVARCHAR";
37: maxLength = dt.Columns[i].MaxLength;
38: }
39:
40: if (maxLength > 0)
41: {
42: sb.AppendFormat(string.Format(" {0} {1} ({2}), ", dt.Columns[i].ColumnName, sqlDBType, maxLength));
43: }
44: else
45: {
46: sb.AppendFormat(string.Format(" {0} {1}, ", dt.Columns[i].ColumnName, sqlDBType));
47: }
48: }
49:
50: sqlQuery = sb.ToString();
51: sqlQuery = sqlQuery.Trim().TrimEnd(',');
52: sqlQuery = sqlQuery + " )";
53:
54: using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
55: {
56: sqlConn.Open();
57: SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
58: sqlCmd.ExecuteNonQuery();
59: sqlConn.Close();
60: }
61:
62: }
63: private void LoadDataToDatabase(string tableName, string fileFullPath, string delimeter)
64: {
65: string sqlQuery = string.Empty;
66: StringBuilder sb = new StringBuilder();
67:
68: sb.AppendFormat(string.Format("BULK INSERT {0} ", tableName));
69: sb.AppendFormat(string.Format(" FROM '{0}'", fileFullPath));
70: sb.AppendFormat(string.Format(" WITH ( FIELDTERMINATOR = '{0}' , ROWTERMINATOR = '\n' )", delimeter));
71:
72: sqlQuery = sb.ToString();
73:
74: using (SqlConnection sqlConn = new SqlConnection(GetConnectionString()))
75: {
76: sqlConn.Open();
77: SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
78: sqlCmd.ExecuteNonQuery();
79: sqlConn.Close();
80: }
81: }
82: protected void Page_Load(object sender, EventArgs e)
83: {
84:
85: }
86: protected void BTNImport_Click(object sender, EventArgs e)
87: {
88: if (FileUpload1.HasFile)
89: {
90: FileInfo fileInfo = new FileInfo(FileUpload1.PostedFile.FileName);
91: if (fileInfo.Name.Contains(".csv"))
92: {
93:
94: string fileName = fileInfo.Name.Replace(".csv", "").ToString();
95: string csvFilePath = Server.MapPath("UploadedCSVFiles") + "\\" + fileInfo.Name;
96:
97: //Save the CSV
file in the Server inside 'MyCSVFolder'
98: FileUpload1.SaveAs(csvFilePath);
99:
100: //Fetch the location of CSV
file
101: string filePath = Server.MapPath("UploadedCSVFiles") + "\\";
102: string strSql = "SELECT * FROM [" + fileInfo.Name + "]";
103: string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";" + "Extended Properties='text;HDR=YES;'";
104:
105: // load the data from CSV to DataTable
106:
107: OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString);
108: DataTable dtCSV = new DataTable();
109: DataTable dtSchema = new DataTable();
110:
111: adapter.FillSchema(dtCSV, SchemaType.Mapped);
112: adapter.Fill(dtCSV);
113:
114: if (dtCSV.Rows.Count > 0)
115: {
116: CreateDatabaseTable(dtCSV, fileName);
117: Label2.Text = string.Format("The table ({0}) has been successfully created to the database.", fileName);
118:
119: string fileFullPath = filePath + fileInfo.Name;
120: LoadDataToDatabase(fileName, fileFullPath, ",");
121:
122: Label1.Text = string.Format("({0}) records has been loaded to the table {1}.", dtCSV.Rows.Count, fileName);
123: }
124: else
125: {
126: LBLError.Text = "
File is empty.";
127: }
128: }
129: else
130: {
131: LBLError.Text = "Unable to recognize
file.";
132: }
133:
134: }
135: }
136: }
137: }
The code above consists of three (3) private methods which are the GetConnectionString(), CreateDatabaseTable() and LoadDataToDatabase(). The GetConnectionString() is
a method that returns
a string. This method basically gets the connection string that is configured
in the web.config
file. The CreateDatabaseTable() is method that accepts two (2) parameters which are the DataTable and the filename. As the method name already suggested, this method automatically create
a Table to the database based on the source DataTable and the filename of the CSV
file. The LoadDataToDatabase() is
a method that accepts three (3) parameters which are the tableName, fileFullPath and delimeter value. This method is where the actual saving or importing of data from CSV to
SQL server happend.
The codes at BTNImport_Click event handles the uploading of CSV
file to the specified location and at the same time this is where the CreateDatabaseTable() and LoadDataToDatabase() are being called. If you notice I also added some basic trappings and validations within that event.
Now to test the importing utility then let's create
a simple data
in a CSV format. Just for the simplicity of this demo let's create
a CSV
file and name it as "Employee" and add some data on it. Here's an example below:
1,VMS,Durano,
[email protected]
2,Jennifer,Cortes,
[email protected]
3,Xhaiden,Durano,
[email protected]
4,Angel,Santos,
[email protected]
5,Kier,Binks,
[email protected]
6,Erika,Bird,
[email protected]
7,Vianne,Durano,
[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.
Okay let's
run the application and browse the CSV
file that we have just created. Take
a look at the sample screen shots below:
After browsing the CSV
file.
After clicking the Import Button
Now if we look at the database that we have created earlier you'll notice that the Employee table is created with the imported data on it. See below screen shot.
That's it! I hope someone find this post useful!
Technorati Tags: ASP.NET,CSV,
SQL,C#,ADO.NET
