piotr-szablowski
asked on
Reading Excel file to DataSet and writing it to SQL table in the look with verification.
Hi Expert,
I am trying to create a web page in C# that will perform the following:
1. Connects to Excel file and read all the information into DataSet - DONE
2. Display the DataGrid with the DataSet - DONE
3. Insert DataSet into the defined table in the SQL database except of the first header row
4. There will be additional fields in the SQL table that will need to be filled with the Variables read from another table in the SQL database (like the Batch No. - which will be different each time the data is inserted) before writing it to the SQL table.
I am able to read the Excel file and bind it to the datagrid to display on the webpage. I need to insert this rows in a LOOP into the desired SQL table. There is about 200 rows on average to insert every day.
In the loop described before inserting the row to SQL table, I would like to check the value of the one filed in dataset with the field in the different SQL table (like Account number)
I am struggling with points 3 and 4. Could you help me there please.
So far my code looks like that:
--------------------------
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.Ole
<%@ Import Namespace="System.Data.Sql
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>
<script language="C#" runat="server">
protected void Page_Load(Object Src, EventArgs E)
{
string myQuery;
string ExcelConnectionString = @"Provider=Microsoft.Jet.O
string SQLConnectionString = "Data Source=.;Initial Catalog=intranet_panda;Int
OleDbDataAdapter myCommandExcel = new OleDbDataAdapter("SELECT * FROM [Dane$]",ExcelConnectionSt
DataSet myDataSet = new DataSet();
myCommandExcel.Fill(myData
DataGrid1.DataSource = myDataSet.Tables["ExcelInf
DataGrid1.DataBind();
}
</script>
<html>
<head></head>
<body>
<p><asp:Label id=Label1 runat="server">SpreadSheet
<asp:DataGrid id=DataGrid1 runat="server"/>
</body>
</html>
--------------------------
Thank you for the response,
Piotr Szablowski
I am trying to create a web page in C# that will perform the following:
1. Connects to Excel file and read all the information into DataSet - DONE
2. Display the DataGrid with the DataSet - DONE
3. Insert DataSet into the defined table in the SQL database except of the first header row
4. There will be additional fields in the SQL table that will need to be filled with the Variables read from another table in the SQL database (like the Batch No. - which will be different each time the data is inserted) before writing it to the SQL table.
I am able to read the Excel file and bind it to the datagrid to display on the webpage. I need to insert this rows in a LOOP into the desired SQL table. There is about 200 rows on average to insert every day.
In the loop described before inserting the row to SQL table, I would like to check the value of the one filed in dataset with the field in the different SQL table (like Account number)
I am struggling with points 3 and 4. Could you help me there please.
So far my code looks like that:
--------------------------
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.Ole
<%@ Import Namespace="System.Data.Sql
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>
<script language="C#" runat="server">
protected void Page_Load(Object Src, EventArgs E)
{
string myQuery;
string ExcelConnectionString = @"Provider=Microsoft.Jet.O
string SQLConnectionString = "Data Source=.;Initial Catalog=intranet_panda;Int
OleDbDataAdapter myCommandExcel = new OleDbDataAdapter("SELECT * FROM [Dane$]",ExcelConnectionSt
DataSet myDataSet = new DataSet();
myCommandExcel.Fill(myData
DataGrid1.DataSource = myDataSet.Tables["ExcelInf
DataGrid1.DataBind();
}
</script>
<html>
<head></head>
<body>
<p><asp:Label id=Label1 runat="server">SpreadSheet
<asp:DataGrid id=DataGrid1 runat="server"/>
</body>
</html>
--------------------------
Thank you for the response,
Piotr Szablowski
ASKER
Hi David,
Looks like an idea to me. Could you expand your thought a little bit more. Your answer is a little to sophisicated to me.
Could you explain in more detail the following part?:
"Bulk insert of BCP into a staging table, then join to the second table, then finally insert into the destination table. Now the app or web page can display the data ..."
SQL server will manage to deal with the file as it will always have the same name and can be saved on the network drive that is physically located on the SQL server. That solves the problem. I can schedule a task to move the file from that folder to the archive folder, or even delete it on the nightly basis to prepare emptly folder for the new file to save there.
Thank you for your answer.
Looks like an idea to me. Could you expand your thought a little bit more. Your answer is a little to sophisicated to me.
Could you explain in more detail the following part?:
"Bulk insert of BCP into a staging table, then join to the second table, then finally insert into the destination table. Now the app or web page can display the data ..."
SQL server will manage to deal with the file as it will always have the same name and can be saved on the network drive that is physically located on the SQL server. That solves the problem. I can schedule a task to move the file from that folder to the archive folder, or even delete it on the nightly basis to prepare emptly folder for the new file to save there.
Thank you for your answer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
Just a thought, but given that you have cross posted into SQL, this would be how a DBA would do it - Bulk insert of BCP into a staging table, then join to the second table, then finally insert into the destination table. Now the app or web page can display the data ...
Just a thought.
And the catch is that the path to the excel file needs to be from the SQL servers perspective ...
HTH
David