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
