?
Solved

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

Posted on 2007-11-14
5
Medium Priority
?
2,491 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
  • 2
4 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 2000 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

Independent Software Vendors: 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

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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 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