Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

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,email1@email.com
2,Jennifer,Cortes,email2@email.com
3,Xhaiden,Durano,email3@email.com
4,Angel,Santos,emai4@email.com
5,Kier,Binks,emai5@email.com
6,Erika,Bird,emai6@email.com
7,Vianne,Durano,emai7@email.com
8,Lilibeth,Tree,emai8@email.com
9,Bon,Bolger,email9@email.com
10,Brian,Jones,email10@email.com


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: ,,,,
Posted on Monday, January 3, 2011 5:03 PM ADO.NET , ASP.NET , C# | Back to top


Comments on this post: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Thanks for the great article. But have you ever used LinqToExcel?
It works great with .CSV files.
Left by Shuaib Rameh on Jan 27, 2011 12:02 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@Shuaib Rameh,

Thanks, Nope haven't explored on LinqToExcel yet but that sounds interesting.. Thanks for the info.
Left by Vinz on Jan 27, 2011 12:18 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Hi. I'm new to all this, so I have some questions. If I already have the database table created, and I just want to add more records to it, does your code still work? Or is there something I have to change? What I'm trying to do is import the information from the CSV file into the already made table.
Left by Krista on Jan 28, 2011 12:24 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@Krista,

In that case, just Load the csv to your database directly. Just modify the codes in LoadToDatabase() method and modify the sql query by specifying the table name
Left by Vinz on Jan 28, 2011 4:57 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Awesome!! Thanks!!!
Left by Krista on Jan 28, 2011 10:03 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
plz tell me where should we create the schema.ini.whether we create in database or in Visual studio
Left by sridar on Feb 04, 2011 1:58 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@sridar,

You can create the schema.ini in a notepad and just save it as a .ini file instead of .txt.
Left by Vinz on Feb 04, 2011 3:35 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
How can I add only some columns from a csv file into my database. Can someone give me a clue
Left by Shaai on Mar 14, 2011 6:06 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@Shaai

You can alter the CreateDatabaseTable method and validate the fields that you only want to create in the database.
Left by Vinz on Mar 14, 2011 9:23 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Thanks, but I am not creating any table. Instead I am adding the values directly in already created table. I also can't do anything with csv file. As the user gets it online and he does not want to change it all the time. Can I do something in "LoadDataToDatabase". Thanks
Left by Shaai on Mar 15, 2011 1:39 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Can I do the same in windows application also?
Actually what I want to do is to read a CSV from one system and save the contents in database on another system.
Left by Shivi on Apr 27, 2011 2:32 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@Shivi,

I'm sure it's doable. For more help on windows app related query then i'd suggest you to post your questions at the forums to get more help from the experts there: http://windowsclient.net/
Left by Vinz on Apr 27, 2011 2:46 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Hello,

when i tried this code, i kept getting this error: --
Could not bulk insert because file 'C:\Users\Jumix\Documents\Visual Studio 2010\WebSites\Try_CSV\UploadedCSVFiles\Employee.csv' could not be opened. Operating system error code 3(The system cannot find the path specified.). Can anyone help please.

Seun.
Left by Seun on May 23, 2011 10:14 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Can any one tell me ...What is the major difference b/w webApplication and website building in asp.net however we do programming i.e c# in both...
then what is the diffffffffff............Reply me at azeem_ravi@yahoo.com.........:) thanks a lot
Left by ravi on May 23, 2011 10:20 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
@Ravi,

There are tons of information that talks about tier different. So go ahead and find it at google: http://www.google.com/#sclient=psy&hl=en&biw=1116&bih=813&source=hp&q=website+vs+web+application+project&aq=1&aqi=g5&aql=&oq=&pbx=1&bav=on.2,or.r_gc.r_pw.&fp=6af0f014ae16db6c
Left by Vinz on May 24, 2011 5:10 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
thanks a lot......................:)
Left by ravi on May 31, 2011 9:01 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
What do I need to change to allow excel format?
Left by David on Aug 17, 2011 3:51 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
can i get the full code in VB, I am using Visual web developer 2010 and code lang VB only, Thanks
Left by Paul Adeleke on Sep 05, 2011 1:44 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Thnx........

But this code can not work for huge data-points ie(100000) ...

can i get solution at earliest......
Left by Jeevan Jadhav on Sep 07, 2011 3:29 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
i used this code but i got the error "The connection to localhost was interrupted."
: The connection was reset.
plz give me solution urgently.
i used visual studio 2008 asp.net c#
Left by Aruna Gade on Oct 05, 2011 6:49 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
When I try to execute the line:
111: adapter.FillSchema(dtCSV, SchemaType.Mapped);
I get the error:
Could not find installable ISAM.

What did I miss?

Thanks
Left by Pete Anderson on Oct 29, 2011 12:08 AM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Thanks Vinz its a great article and the code is working fine for me.

I have a question regarding the .CSV file name does it have to be same as it is in Schema.ini file. Can make it name indipendent as I am getting error while uploading if the .CSV file name is different from the name given in Schema.ini.

Please Help

Thanks in Advance.
Left by Rizwan on Dec 01, 2011 2:27 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
Sir thanks for your code ... when i am trying to run this code i alwz gt this error..
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Id).
Please if you can resolve my problem...

second this i hve to convert my own csv file in to the database how i can do it .. because when i am uploading that file with your code it alwz give an error which is
Incorrect syntax near the keyword 'From'

Please help me because i have to submitted an assignment on monday...
plzzzz and i am using visual studio 2010 nd server 2008...

hope for positive response.. if u need a copy of ur project please forward me sir ..

thanks vikul
Left by vikul on Dec 16, 2011 8:12 PM

# nice
Requesting Gravatar...
I found your this post while searching for some related information on blog search...Its a good post..keep posting and update the information.
Left by find hosting on Jan 04, 2012 8:08 PM

# nice
Requesting Gravatar...
This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
Left by find hosting on Jan 04, 2012 8:09 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
This is a great inspiring article,found your this post while searching for some related information on blog search
Left by air max bw on Jan 07, 2012 11:07 PM

# re: Uploading and Importing CSV file to SQL Server in ASP.NET WebForms
Requesting Gravatar...
I will gear this review to 2 kinds of folks: present Zune entrepreneurs who are considering an upgrade, and people trying to choose involving a Zune and an iPod. (You will discover other players price taking into consideration on the market, just like the Sony Walkman X, nike tnbut I hope this provides you sufficient info for making an informed decision of the Zune vs gamers apart from the iPod line at the same time.)
Left by Aaasiotnt on Jan 07, 2012 11:17 PM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net