Solved

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

Posted on 2007-11-14
5
2,482 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
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now