Solved

Reading Excel file to DataSet and writing it to SQL table in the look with verification.

Posted on 2007-11-14
5
2,485 Views
Last Modified: 2008-03-15
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.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ 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.OLEDB.4.0;Data Source=C:\\dataset\\exceltest.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
string SQLConnectionString = "Data Source=.;Initial Catalog=intranet_panda;Integrated Security=True";

OleDbDataAdapter myCommandExcel = new OleDbDataAdapter("SELECT * FROM [Dane$]",ExcelConnectionString);

DataSet myDataSet = new DataSet();
myCommandExcel.Fill(myDataSet, "ExcelInfo");

DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
DataGrid1.DataBind();
}
</script>

<html>
  <head></head>
  <body>
    <p><asp:Label id=Label1 runat="server">SpreadSheetContents:</asp:Label></p>
    <asp:DataGrid id=DataGrid1 runat="server"/>
  </body>
</html>
----------------------------------------------------------------------------------------------------------------

Thank you for the response,
Piotr Szablowski
0
Comment
Question by:piotr-szablowski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 20282596
Hi,

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
0
 

Author Comment

by:piotr-szablowski
ID: 20746942
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.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 20747980
Hi,

"... explain the staging table thing ... "
Often the external data contains duplicates and all that.

BCP and Bulk insert aren't particularly intelligent, and will totally fail for the most minor of things.

For instance, importing orders.
There might be a stuff up on the export and an order exported that has been transfered previously. Often can happen when attempting to recover from a crash.

So an insert into the destination orders table will fail.

If we have an intermediate table, with much the same structure as the orders table, but few constraints, then the insert will happen, and 'consume' the transfer file.

Now we can at our leisure copy the data from intermediate or staging table to the destination table. As this query allows us a lot more control, we can do things like this
insert destination_Orders
select *
from staging_orders so
left outer join destination_orders do
  on so.orderID = do.orderID -- you may or may not be bringing an ID across
where
  do.orderID is null -- that is, no row exists in destination.

HTH
  David
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21135859
Forced accept.

Computer101
EE Admin
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question