[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Write data from excel file to a table in SQL Database

Posted on 2006-04-13
6
Medium Priority
?
307 Views
Last Modified: 2010-05-18
Andy,

The next step is to take the info from the Excel file and write it to a table in SQL DB.
0
Comment
Question by:GoldenJag
  • 2
  • 2
5 Comments
 

Author Comment

by:GoldenJag
ID: 16448985
This is officially what my code looks like:

private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
{
      int fileid = Convert.ToInt32(DataGrid1.SelectedItem.Cells[1].Text);
      com.solu.webservices.Crypt ws = new com.solu.webservices.Crypt();
      byte[] test = ws.DecryptStoredFile(pm.sessionKey,fileid);
                  
      //WRITE TO TEMP FILE
      //string FileName = Path.GetTempFileName();
      string FileName = @"C:\temp.xls";
      FileStream File_Stream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
      try
      {
            File_Stream.Write(test,0,test.Length);
      }
      catch(Exception ex)
      {
            Response.Write(ex.Message.ToString());
      }
      finally
      {
            File_Stream.Close();
      }

                                                  //NEXT STEP:
                  //WRITE to info from Excel file to SQL  Table via ODBC

0
 
LVL 12

Expert Comment

by:AGBrown
ID: 16449678
OK, cool.

Is this the new code? You should be careful with the FileStream object ... the using {} block we had is a better way of doing it as it ensures that the stream is both flushed and disposed once you've finished.

Other than that, you now want to write from Excel to SQL, unfortunately I haven't had much success with this in the past. ODBC and Excel is a bit of a nightmare - the data-type detection is very dodgy.

However, I can point you to this place to get you started: http://www.codeproject.com/csharp/Excel_using_OLEDB.asp

Hopefully that will help, or someone else will join in this question to add some more advice. Feel free to ask for more details as you go.

Andy
0
 

Author Comment

by:GoldenJag
ID: 16488359
I found the solution: it is

string strConn;

                  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Data Source= C:\\temp.xls;" +
                        "Extended Properties=Excel 8.0;";
                  //You must use the $ after the object you reference in the spreadsheet
                  OleDbConnection objConn = new OleDbConnection(strConn);
            objConn.Open();
                  OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",objConn);
                  
                  OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

                  objAdapter1.SelectCommand = myCommand;

                  DataSet myDataSet = new DataSet();

                  objAdapter1.Fill(myDataSet, "XLData");
                  DataGrid1.DataSource = myDataSet.Tables[0].DefaultView;
                  DataGrid1.DataBind();
0
 
LVL 12

Expert Comment

by:AGBrown
ID: 16529008
Fine by me
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16529678
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 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