Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦

825 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