Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Article

Importing CSV Data and saving it in database

Rate me:
Please Sign up or sign in to vote.
4.45/5 (57 votes)
25 Aug 20052 min read 791.8K   28.2K   211   78
This article shows how to import CSV data and store it in database.

Image 1

Introduction

Nowadays it is common in applications to have the functionality of reading the CSV data. My current project needed one. Even after searching for long, I could not get one which could satisfy my requirements. But after doing considerable amount of study, I came up with the following tool. CSV files stand for Comma Separated Value files. They are common text files with comma delimited values. Though the default delimiter is comma (,), we can specify other characters as delimiters like the semi-colon (;), colon (:), asterisk (*). But you cannot specify double quotes (") as a delimiter. I have used Microsoft Text Drivers for reading the CSV data. You have to use ODBC connection for accessing the CSV data. You can either create a DSN or use the connection string. If you create a DSN, the schema.ini file gets created automatically in the folder where all your CSV files reside. But if you use connection string, you have to create schema.ini file on your own. We are going to see the latter approach.

Schema.ini File (Text File Driver)

When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file, which is always named schema.ini and always kept in the same directory as the text data source, provides the IISAM with information about the general format of the file, the column name and data type information, and a number of other data characteristics.

Using the demo application

For successfully running the application you need Test.csv file and a database with a table having three columns. But all this is provided in the demo application. So you need not worry. Follow these steps to run the demo application:

  1. First run DBI.exe application.
  2. The screen shown below will appear.
  3. Fill the required details and click the button "Install".
  4. Make sure that a folder named "Test" is created in "D:" drive with the Test.csv file in it.
  5. Now run our main application i.e. FinalCSVReader.exe.
  6. Keep the default folder and file path as it is.
  7. First click "Import CSV data" to import the CSV data.
  8. Now click "Save", to save the data in the database.

Image 2

Using the source code

Some important parts of the code are discussed below

Create schema.ini

This is a function writeSchema(). It creates the schema.ini file dynamically.

C#
/*Schema.ini File (Text File Driver)

 When the Text driver is used, the format of the
 text file is determined by using a schema information
 file. The schema information file, which is always named
 Schema.ini and always kept in the same directory as the
 text data source, provides the IISAM with information
 about the general format of the file, the column name
 and data type information, and a number of other data 
 characteristics*/

private void writeSchema()
{
 try    
    {
        FileStream fsOutput = 
             new FileStream (txtCSVFolderPath.Text+"\\schema.ini", 
                                 FileMode.Create, FileAccess.Write);
        StreamWriter srOutput = new StreamWriter (fsOutput);
        string s1, s2, s3,s4,s5;
        s1="["+strCSVFile+"]";
        s2="ColNameHeader="+bolColName.ToString ();
        s3="Format="+strFormat;
        s4="MaxScanRows=25";
        s5="CharacterSet=OEM";
        srOutput.WriteLine(s1.ToString()+'\n'+s2.ToString()+
                                    '\n'+s3.ToString()+'\n'+
                                    s4.ToString()+'\n'+s5.ToString());
        srOutput.Close ();
        fsOutput.Close ();                    
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
    }

Function for importing the CSV Data

This function ConnectCSV (string filetable) takes the .csv file name as argument and returns the dataset containing the imported data.

C#
public DataSet ConnectCSV (string filetable)
{
  DataSet ds = new DataSet ();
  try
   {        
       /* You can get connected to driver either by using
       DSN or connection string. Create a connection string
       as below, if you want to use DSN less connection.
       The DBQ attribute sets the path of directory which 
       contains CSV files*/

       string strConnString=
             "Driver={Microsoft Text Driver (*.txt;*.csv)};
             Dbq="+txtCSVFolderPath.Text.Trim()+";
             Extensions=asc,csv,tab,txt;
             Persist Security Info=False";

       string sql_select;                                
       System.Data.Odbc.OdbcConnection conn;        
        
       //Create connection to CSV file
       conn = new System.Data.Odbc.OdbcConnection(
                                    strConnString.Trim ());

       // For creating a connection using DSN, use following line
       //conn = new System.Data.Odbc.OdbcConnection(DSN="MyDSN");
    
       //Open the connection 
       conn.Open ();
       //Fetch records from CSV
       sql_select="select * from ["+ filetable +"]";
                
       obj_oledb_da=new System.Data.Odbc.OdbcDataAdapter(
                                                sql_select,conn);
       //Fill dataset with the records from CSV file
       obj_oledb_da.Fill(ds,"Stocks");
                
       //Set the datagrid properties
                
       dGridCSVdata.DataSource=ds;
       dGridCSVdata.DataMember="Stocks";
       //Close Connection to CSV file
       conn.Close ();                
   }
   catch (Exception e) //Error
   {
       MessageBox.Show (e.Message);
   }
   return ds;
}

Code for inserting the data

This is a code written in the button's click event btnUpload_Click. This actually inserts the data in the database.

C#
private void btnUpload_Click(object sender, 
                                System.EventArgs e)
{
 try
  {
    // Create an SQL Connection
    // You can use actual connection 
    // string instead of ReadConFile()

    SqlConnection  con1=
         new SqlConnection(ReadConFile().Trim());
    SqlCommand cmd = new SqlCommand();
    SqlCommand cmd1 = new SqlCommand();

    // Create Dataset                    
    DataSet da = new DataSet();

    /* To actually fill the dataset,
    Call the function ImportCSV and   assign 
    the returned dataset to new dataset as below */

    da=this.ConnectCSV(strCSVFile);    

    /* Now we will collect data from data table
    and insert it into database one by one.
    Initially there will be no data in database 
    so we will insert data in first two columns 
    and after that we will update data in same row
    for remaining columns. The logic is simple.
    'i' represents rows while 'j' represents columns*/

    cmd.Connection=con1;
    cmd.CommandType=CommandType.Text;
    cmd1.Connection=con1;
    cmd1.CommandType=CommandType.Text;
                    
    con1.Open();
    for(int i=0;i<=da.Tables["Stocks"].Rows.Count-1;i++)
    {                        
      for(int j=1;j<=da.Tables["Stocks"].Columns.Count-1;j++)
      {
        cmd.CommandText= 
          "Insert  into Test(srno,
             "+da.Tables["Stocks"].Columns[0].ColumnName.Trim()+")
          values("+(i+1)+",
             '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(0)+"')";
        
        /* For UPDATE statement, in where clause you
        need some unique row identifier. We are using 
        ‘srno’ in WHERE clause. */

        cmd1.CommandText=
          "Update Test set "
              +da.Tables["Stocks"].Columns[j].ColumnName.Trim()+"
              = '"+da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j)+
          "' where srno ="+(i+1);                            
        cmd.ExecuteNonQuery();
        cmd1.ExecuteNonQuery();                            
      }
    }
    con1.Close();
  }
  catch(Exception ex)
  {
      MessageBox.Show(ex.Message);
  }
  finally
  {
      btnUpload.Enabled=false;
  }
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
India India
*****

Comments and Discussions

 
GeneralNot working on XP !?!?! Pin
pierrecenti18-Jul-07 2:56
pierrecenti18-Jul-07 2:56 
QuestionDo recognize specified string to decimal type in ODBC. Pin
gyseven5-Jul-07 17:30
gyseven5-Jul-07 17:30 
GeneralCSV Validation Pin
Teguh Eko27-Jun-07 21:39
Teguh Eko27-Jun-07 21:39 
QuestionNeed help to import in VB.NET Pin
Jats_4ru19-Apr-07 19:13
Jats_4ru19-Apr-07 19:13 
Generaldelimitor for the csv file as single quote Pin
indian14319-Apr-07 3:23
indian14319-Apr-07 3:23 
Questionwhat if i want to import the whole text? Pin
sareerden15-Apr-07 7:08
sareerden15-Apr-07 7:08 
AnswerRe: what if i want to import the whole text? Pin
Mukund Pujari15-Apr-07 18:09
Mukund Pujari15-Apr-07 18:09 
GeneralNot Getting all Column Data into Database [modified] Pin
JMO11122-Jan-07 3:14
JMO11122-Jan-07 3:14 
I have a CSV file with 15 columns in it. I know the sample project was setup for just 2 columns. I am only getting 2 columns of data showing. If I edit the 14 in this line...for (int j = 1; j <= da.Tables["Stocks"].Columns.Count - 14; j++) to 13 I gain an additional column of data but the rows duplicate once. As I decrease the 14 one by one, each number I decrease by is how many duplicates of the rows I get. Can someone help me out on this?

// Now we will collect data from data table and insert it into database one by one
// Initially there will be no data in database so we will insert data in first two columns
// and after that we will update data in same row for remaining columns
// The logic is simple. 'i' represents rows while 'j' represents columns

cmd.Connection = con1;
cmd.CommandType = CommandType.Text;
cmd1.Connection = con1;
cmd1.CommandType = CommandType.Text;

con1.Open();
for (int i = 0; i <= da.Tables["Stocks"].Rows.Count - 1; i++)
{

for (int j = 1; j <= da.Tables["Stocks"].Columns.Count - 14; j++)
{

cmd.CommandText = "Insert into Test (srno, " + da.Tables["Stocks"].Columns[0].ColumnName.Trim() + ") values(" + (i + 1) + ",'" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(0) + "')";

// For UPDATE statement, in where clause you need some unique row
//identifier. We are using ‘srno’ in WHERE clause.
cmd1.CommandText = "Update Test set " + da.Tables["Stocks"].Columns[j].ColumnName.Trim() + " = '" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j) + "' where srno =" + (i + 1);
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();

}
}

Thanks,
JMO


-- modified at 9:33 Monday 22nd January, 2007
GeneralRe: Not Getting all Column Data into Database Pin
andyharman22-Jan-07 4:38
professionalandyharman22-Jan-07 4:38 
GeneralRe: Not Getting all Column Data into Database Pin
JMO11122-Jan-07 5:08
JMO11122-Jan-07 5:08 
GeneralRe: Not Getting all Column Data into Database Pin
hyndavi1-Apr-07 5:01
hyndavi1-Apr-07 5:01 
QuestionERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Pin
shail nigam1-Jan-07 17:48
shail nigam1-Jan-07 17:48 
AnswerRe: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Pin
Nishanth Nair4-Apr-07 19:38
Nishanth Nair4-Apr-07 19:38 
AnswerRe: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Pin
Andrew de Jonge5-Feb-08 19:47
Andrew de Jonge5-Feb-08 19:47 
AnswerRe: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Pin
Timshel Knoll-Miller11-Feb-09 18:30
Timshel Knoll-Miller11-Feb-09 18:30 
GeneralChanging drive path Pin
samuk8113-Nov-06 23:51
samuk8113-Nov-06 23:51 
GeneralFile missing Pin
VC Sekhar Parepalli28-Aug-06 13:30
VC Sekhar Parepalli28-Aug-06 13:30 
GeneralHeader Column containg "." between column names Pin
Xkaliber16-Aug-06 20:49
Xkaliber16-Aug-06 20:49 
Generalconnection string.txt Pin
dmrulez8-Aug-06 2:13
dmrulez8-Aug-06 2:13 
Questionsome problems of csv data inporting in dataset? Pin
kermy7-Jun-06 2:01
kermy7-Jun-06 2:01 
QuestionCorruption of imported data ? Pin
minnie mouse15-May-06 18:17
minnie mouse15-May-06 18:17 
GeneralIIS not starting. plz help me Pin
abasitt18-Apr-06 8:19
abasitt18-Apr-06 8:19 
GeneralNice but... Pin
enjoycrack2-Apr-06 22:45
enjoycrack2-Apr-06 22:45 
Generalnice Pin
AnasHashki21-Mar-06 21:45
AnasHashki21-Mar-06 21:45 
QuestionProblem on importing line with double quote Pin
armandocafaro20-Mar-06 3:48
armandocafaro20-Mar-06 3:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.