Tuesday, November 6, 2012

Upload Data from CSV file to Sql Server

1.FileUploader1 is a fileUploadControl
2.name,fathername,address are the columns which same as csv file name and sql server database 
3.G:\\FileName is your local computer path where CSV file save.
           

FileUploader1.SaveAs("G:\\FileName\\" + FileUploader1.FileName);

                // 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=" + "G:\\FileName\\" + ";Extensions=asc,csv,tab,txt;Persist Security Info=False";


                string Query;

                DataSet ds = new DataSet();
                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

                //Open the connection
                conn.Open();


                //Fetch records from CSV
                Query = "select * from [" + FileUploader1.FileName.ToString() + "]";

                System.Data.Odbc.OdbcDataAdapter oledaObj = new System.Data.Odbc.OdbcDataAdapter(Query, conn);

                //Fill dataset with the records from CSV file
                oledaObj.Fill(ds);


                int j = 0;
                if (ds.Tables[0].Rows.Count > 0)
                {

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        try
                        {

                            DataSet ds2 = new DataSet();
                            SqlDataAdapter adap = new SqlDataAdapter("INSERT INTO TableDetails (name,fathername,address)  VALUES('" + ds.Tables[0].Rows[i]["name"].ToString() + "','" + ds.Tables[0].Rows[i]["fathername"].ToString() + "','" + ds.Tables[0].Rows[i]["address"].ToString() + "')", ConfigurationManager.ConnectionStrings["Conn"].ConnectionString.ToString());
                            adap.Fill(ds);
                            j++;
                        }
                        catch (Exception ex)
                        {

                        }
                    }


                    LblMsg.Text = "File Uploaded Successfully, No. of record save - " + System.Convert.ToString(j);
                    LblMsg.Visible = true;


                }
                conn.Close();

0 comments:

Post a Comment