Can you help me out on how i can give a feature to user to browse excel or csv from their end and upload data to database upon clicking upload button after brwosing their file.
You may iterate throuh the excel file the below code & and in the foreach loop, you may just perform the insert operations for your SQL server, using a code something like this :
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Database\\Book81.xlsx;Extended Properties=Excel 8.0;";
OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString);
ExcelConnection.Open();
string select = @"SELECT * FROM [Sheet1$]";
OleDbCommand SelectCommand = new OleDbCommand(select, ExcelConnection);
OleDbDataAdapter DataAdapter = new OleDbDataAdapter(SelectCommand);
DataTable table = new DataTable();
DataAdapter.Fill(table);
ExcelConnection.Close();
ExcelConnection.Dispose();
//SQL Server setup
const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>";
SqlConnection Connection = new SqlConnection(ConnectionString);
Connection.Open();
SqlCommand InsertCommand = Connection.CreateCommand();
InsertCommand.commandText = @"INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2, COLUMN_N) VALUES (@col1, @col2, @colN)";
foreach (DataRow row in table.Rows)
{
InsertCommand.Parameters.Clear();
InsertCommand.Parameters.AddWithValue("col1",row[0].ToString());
InsertCommand.Parameters.AddWithValue("col2",row[1].ToString());
InsertCommand.Parameters.AddWithValue("colN",row[2].ToString());
//Now execute the insert command the standard way ... using InsertCommand.ExecuteNonQuery();
//A good practice is to embed the InsertCommand.ExecuteNonQuery() in the try-catch-finally block
}
For reading data from a CSV file you may just read that file using standard file operation that you use to read from a .txt file from C#, Or here is the another way
sree1248
Member
4 Points
33 Posts
Upload CSV or Excel data to Database
Dec 09, 2012 10:34 AM|LINK
Hie all,
Can you help me out on how i can give a feature to user to browse excel or csv from their end and upload data to database upon clicking upload button after brwosing their file.
Regards
Sree
Milind986
Participant
766 Points
161 Posts
Re: Upload CSV or Excel data to Database
Dec 09, 2012 02:48 PM|LINK
hiiiiie
check out this link and source code.
http://www.gigasters.com/post.aspx?postid=35
My Blog
aarsh
Participant
1543 Points
426 Posts
Re: Upload CSV or Excel data to Database
Dec 09, 2012 05:06 PM|LINK
You may iterate throuh the excel file the below code & and in the foreach loop, you may just perform the insert operations for your SQL server, using a code something like this :
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Database\\Book81.xlsx;Extended Properties=Excel 8.0;"; OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString); ExcelConnection.Open(); string select = @"SELECT * FROM [Sheet1$]"; OleDbCommand SelectCommand = new OleDbCommand(select, ExcelConnection); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(SelectCommand); DataTable table = new DataTable(); DataAdapter.Fill(table); ExcelConnection.Close(); ExcelConnection.Dispose(); //SQL Server setup const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>"; SqlConnection Connection = new SqlConnection(ConnectionString); Connection.Open(); SqlCommand InsertCommand = Connection.CreateCommand(); InsertCommand.commandText = @"INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2, COLUMN_N) VALUES (@col1, @col2, @colN)"; foreach (DataRow row in table.Rows) { InsertCommand.Parameters.Clear(); InsertCommand.Parameters.AddWithValue("col1",row[0].ToString()); InsertCommand.Parameters.AddWithValue("col2",row[1].ToString()); InsertCommand.Parameters.AddWithValue("colN",row[2].ToString()); //Now execute the insert command the standard way ... using InsertCommand.ExecuteNonQuery(); //A good practice is to embed the InsertCommand.ExecuteNonQuery() in the try-catch-finally block }For reading data from a CSV file you may just read that file using standard file operation that you use to read from a .txt file from C#, Or here is the another way
string strBasePath = @"C:\Database"; string strFilename = @"GridDatabase.csv"; string CSVConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + strBasePath + @"';Extended Properties='text; HDR=Yes;FMT=Delimited';"; using (OleDbConnection CSVConnection = new OleDbConnection(CSVConnectionString)) { try { CSVConnection.Open(); string strSelectCommand = @"SELECT * FROM " + strFilename ; // Perform your operations ... } catch (Exception ex) { Console.WriteLine("CSV Database Err: " + ex.Message); } finally { if (!CSVConnection.State.Equals(ConnectionState.Closed)) CSVConnection.Close(); } }http://wildclick.wordpress.com/codelib/codelib-c/ for more ...