AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Importing CSV file into Database with Schema.ini
Posted by Moderator1 on 8/30/2008 3:32:35 AM Category: Asp.Net 2.0
Total Views : 96851
Add to my favorites
Email to friend
  
Introduction
In real time web applications, there come several situations to import data from an external data sources such as Excel file, CSV file, text file etc. Asp.Net Framework provides a simple solution to import the data from the external sources to the database with the Microsoft.Jet.OLEDB provider. But difficulties arise when the external data 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, we use schema.ini file to define the data type of the CSV or text file and allow the provider to read that and recognize the exact data types of each column. And this article mainly concentrates on how to import a CSV file with different data types into database, using Microsoft Jet Database Engine and also with Schema.ini.


What is Schema.ini file?

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.

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.

For demonstration, consider a CSV file with 5 columns such as Date, First Name, Last Name, Age and Salary. The data structure of these columns are given below

Column Name Data Type Width Format
Date DateTime   dd-MMM-yyyy
First Name Text 100  
Last Name Text 100  
Age Long    
Salary Double    

Let us consider that we need to upload the CSV file in two different scenarios, with same CSV file name and with different CSV file name whenever we upload.

Import a CSV file every time with the SAME CSV file name.

If you want to import a CSV file every time with same file name, just do the following steps.

1. Open a Notepad file.

2. copy and paste the below content in the notepad and replace 'YourCSVFileName.csv' with your CSV file name.

[YourCSVFileName.csv]
ColNameHeader=True
Format=CSVDelimited
DateTimeFormat=dd-MMM-yyyy
Col1=A DateTime
Col2=B Text Width 100
Col3=C Text Width 100
Col4=D Long
Col5=E Double

3. Save the Notepad file as 'schema.ini' in the same location of your CSV file.

4. In your web page, place a FileUpload control, an Upload button and a GridView control. In the Upload button's click event write the following code.

if (filUpload.HasFile)
{
  FileInfo fileinfo = new FileInfo(filUpload.PostedFile.FileName); 

  string strCsvFilePath = Server.MapPath("MyCSVFolder") + "\\" + fileinfo.Name; 

  //Save the CSV file in the Server inside 'MyCSVFolder' 
  filUpload.SaveAs(strCsvFilePath); 

  //Fetch the location of CSV file 
  string strFilePath = Server.MapPath("MyCSVFolder") + "\\"; 

  string strSql = "SELECT * FROM [" + fileinfo.Name + "]"; 

  string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" + "Extended Properties='text;HDR=YES;'"; 

  // load the data from CSV to DataTable 

  OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString); 
  DataTable dtbCSV = new DataTable(); 
  oleda.Fill(dtbCSV); 

  //Display data in a GridView control 
  GridView1.DataSource = dtbCSV; 
  GridView1.DataBind();
}

5. Now you can insert the data in the DataTable into the database in your own way.

Import a CSV file every time with DIFFERENT CSV file name or
Dynamically creating Schema.ini File

If you want to upload a CSV file with different CSV file name every time, then you have to create the schema.ini file dynamically using the FileStream and StreamWriter object before you import the CSV file. To know how, follow the simple steps below.

In your web page, place a FileUpload control, an Upload button and a GridView control. In the Upload button's click event write the following code.

if (filUpload.HasFile)
{
  FileInfo fileinfo = new FileInfo(filUpload.PostedFile.FileName); 

  string strCsvFilePath = Server.MapPath("MyCSVFolder") + "\\" + fileinfo.Name; 

  filUpload.SaveAs(strCsvFilePath); 

  string strFilePath = Server.MapPath("MyCSVFolder"); 

  using (FileStream filestr = new FileStream(strFilePath + "\\schema.ini", 
      FileMode.Create, FileAccess.Write)) 
  { 
      using (StreamWriter writer=new StreamWriter(filestr)) 
      { 
          writer.WriteLine("[" + fileinfo.Name + "]"); 
          writer.WriteLine("ColNameHeader=True"); 
          writer.WriteLine("Format=CSVDelimited"); 
          writer.WriteLine("DateTimeFormat=dd-MMM-yy"); 
          writer.WriteLine("Col1=A DateTime"); 
          writer.WriteLine("Col2=B Text Width 100"); 
          writer.WriteLine("Col3=C Text Width 100"); 
          writer.WriteLine("Col4=E Long"); 
          writer.WriteLine("Col5=F Double"); 
          writer.Close(); 
          writer.Dispose(); 
      } 
      filestr.Close(); 
      filestr.Dispose(); 
  } 


  string strSql = "SELECT * FROM [" + fileinfo.Name + "]"; 
  string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" 
    + "Extended Properties='text;HDR=YES;'"; 

  OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString); 
  DataTable dtbBankStmt = new DataTable(); 
  oleda.Fill(dtbBankStmt); 

  GridView1.DataSource = dtbBankStmt; 
  GridView1.DataBind();

}


The above code uses FileInfo object to get the file name of the CSV file to write in the schema.ini file. Using the FileUpload control, we save the CSV file in the server’s directory “MyCSVFolder”. The schema.ini file has also to be created in this same directory. By using the FileStream object, we create the schema.ini file, and then by using StreamWriter object, we write the content of the schema.ini file. Rest everything is simple to read the CSV file, with the Microsoft.Jet.OLEDB provider, load the content into a DataTable, then data manipulation can be done. For this article purpose, we displayed the data in a GridView control.

Performance tips to import data is, pass the CSV file’s work sheet name between square brackets (“[]”) in the SELECT statement. This will avoid the problem if the CSV file’s work sheet name has two or more words separated by spaces.

Understanding the Schema.ini file line by line

Line 1: Name of the CSV file enclosed between square brackets (‘[‘ and ‘]’ ).
Line 2: Specify the CSV file contains column header in first row. If column header exists, then specify ColNameHeader as True, otherwise False.
Line 3: Specify the CSV file delimited format. It can be CSVDelimited, TabDelimited, Delimited(), FixedLength.
Line 4: If your CSV file contains any date column, then specify the format of the Date value. Furthermore, if the CSV file contains any other columns that has to be formatted properly before data manipulation, then it can be specified here.
Line 5 and above: Specify each column’s Name, Data type, Width if applicable. The general syntax is

Col(n)=<column name> <data type> <Width width>
 

Where n is the position of the column in the CSV file, and Width is mandatory only for Text.

The main concept of this article is to provide the knowledge of reading, importing or exporting a CSV file with the help of schema.ini file. The scope the schema.ini file can be extendable to any limit.


To learn more about Schema.ini, please refer
http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
Viewer's Comments
Posted by Newbie on 9/26/2008 3:05:28 AM
How to insert the data from a csv file into sql server2005 using asp.net vb code? Thank you!!
 
Posted by Jason on 11/5/2008 11:42:57 AM
Hi, I'm a newb in Asp.Net. I saw your code and it is exactly what I'm trying to create, however I'm having problem converting you code to Visual Basic language. Would you be able to help with this?
 
Posted by sejal on 1/7/2009 2:06:21 AM
Very Helpful Article...
 
Posted by A. Carlos Mendoza on 1/10/2009 10:04:54 AM
Excellent!
 
Posted by Maggie on 1/18/2009 10:13:43 PM
This is great~ Don't know there's a schema.ini in vb.net that can help to solve the datatype problem~ Many Thanks~
 
Posted by abbe on 1/23/2009 6:59:11 AM
There is problem if you have lots of columns for example > 4000 that i have, i have to use it this way. And then if you dont have time to write an schema.ini file! How could create an columnlist from that csv or txt file with oledb or odbc?
 
Posted by Mark J on 2/26/2009 4:48:19 AM
Excellent article - helped me out of a spot!
 
Posted by Faisal on 3/18/2009 2:44:41 AM
Excellent
 
Posted by Kire on 4/10/2009 4:59:00 PM
Hi, I'm new in VB.Net. I would like to ask how to import csv file in datagridview control using desktop application. Also after that i wold like to save this dates in sql server 2005.I use vb.net 2008 and connection with sql server 2005 is make with linq Best regard
 
Posted by Malik on 5/11/2009 7:59:49 AM
I would like to ask how to import csv file in datagridview control using C# language.Is there Anyone to help??
 
Posted by milind on 6/18/2009 6:13:05 AM
really excellent article... very helpful
 
Posted by erik on 6/21/2009 3:03:39 AM
Very useful information. Thank you for sharing.
 
Posted by Felix on 7/20/2009 1:05:54 PM
Exactly what I needed, thanks.
 
Posted by Gilson on 7/29/2009 9:03:42 AM
very helpful, for excel too.
 
Posted by Somnath Kadam on 8/3/2009 4:21:13 AM
Very Nice Information, But I have one question, If i want Schema.ini file is in another location for that condition what are changes are required?
 
Posted by sasha on 8/21/2009 9:39:31 AM
excellent!!
 
Posted by Rajashree on 9/17/2009 7:39:07 AM
Excellent article. Exactly what I was searching for. Saved me lot of coding.
 
Posted by Rajashree on 9/17/2009 7:41:50 AM
Execellent article. Exactly what I was searching for. thanks a lot.
 
Posted by ravi on 4/13/2010 6:27:09 AM
Thanks..excellent my csv file import date type problem solved..
 
Posted by pejoter on 5/16/2010 3:02:20 PM
Thanks. Excellent article, it was very, very helpfull :)
 
Posted by Donatelas on 5/25/2010 6:31:03 AM
I want to say thank you. I had never thought, that text files can be read this way.
 
Posted by Aggie on 11/25/2010 1:05:22 AM
Excellent!!Very very helpful!
 
Posted by Tracy on 12/8/2010 2:19:06 PM
This example was very clear. I thank you for providing the information that allowed me to quickly solve my data import issue.
 
Posted by Rick on 12/29/2010 10:12:16 AM
What happens if the column headers fo the csv file are multi words (IE last name) My schema keeps booming on them
 
Posted by prashant on 9/7/2011 1:43:06 AM
Excellent!!Very very helpful!
 
Posted by madghbxcd on 10/29/2011 12:44:27 AM
may be it is use ful for some one with me.
 
Posted by Chuck on 12/15/2011 11:05:19 AM
Thanks, Excellent, Used the different CSV section in my program and it solved a major problem.
 
Posted by kafka on 10/4/2012 3:58:21 AM
quick question: why do you write Col1=A DateTime instead of Col1=Date DateTime ?
 
Posted by SqlBulkCopy from CSV files on 1/31/2013 12:48:30 PM
how should I use the schema.ini file with SqlBulkCopy? what about with uniqueidentifier types? I have a problem with this data type. Thanks in advance.
 
Posted by mahalakshmi on 3/13/2013 2:34:40 AM
Thank u so much. I tried 2 days,now got it.
 
Posted by vinay on 8/23/2013 8:16:53 AM
what if i have more than 50 columns and only specific column size is more than 255
 
Posted by satyanarayana P on 9/30/2013 8:36:05 AM
how should i update one column data in csv file?
 
Posted by Veena on 2/20/2014 3:17:36 PM
Hi, Is there any way we can read the schema file from dirrent directoty?I dont want to place the schema file under the same direcory of datafiles.
 
Posted by Veena on 2/20/2014 3:18:59 PM
My Bad! Typos Hi, Is there any way we can read the schema file from different directory?I dont want to place the schema file under the same directory of datafiles.
 
 Rating & Comments
A word 'Excellent' means lot to the author of this article. You can give comments about this article but not the author.
Rate this Article:
Name:
Email Id:  
We never display your email id anywhere.
Comment/Question: Max. 500 letters
Ninety minus Three Equals To
 
 
Sponsored by
The JavaScript Anthology: 101 Essential Tips, Tricks & Hacks - Free 158 Page Preview
Introduction to Web Applications Development
Simply SQL - Free 111 Page Preview!