none
How to read excel file in document library and store excel content in sql table RRS feed

  • Question

  • Hello,

    Can anyone help me how to read the excel file present in document library and store the content inside excel into sql table?

    Please let me know the ways to acheive this. Feel free to give your suggestions.


    Thanks, Cool Developer
    Friday, February 12, 2010 9:24 AM

Answers

  • Hi!
    this code i have written becuase i donot find any soltions on net for this , u can try with this . :)

    System.Data.OleDb.

    OleDbConnection ExcelConnection = null;

     

    FileMode fileMode;

     

    string filePath = ConfigurationManager.AppSettings["TempLoaction"] + "\\" + fileName;

     

    using (SPSite _site = new SPSite(SPContext.Current.Web.Url))

    {

     

    using (SPWeb _web = _site.OpenWeb())

    {

     

    string docLibrary = ConfigurationManager.AppSettings["DocumentLibrary"];

     

    SPFile _file = _web.GetFile("/" + docLibrary + "/" + fileName);

    fileMode =

    FileMode.Create;

     

    byte[] byteArray = _file.OpenBinary();

     

    MemoryStream dataStream = new MemoryStream(byteArray);

     

    Stream stream = dataStream;

     

     

    using (FileStream fs = File.Open(filePath, fileMode))

    {

     

    byte[] buffer = new byte[4096];

     

    int bytesRead;

     

    while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0)

    {

    fs.Write(buffer, 0, bytesRead);

    }

    fs.Close();

    }

    }

    }

     

    //Create the Connection String

     

    try

    {

     

    string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;

    Data Source ='"

    + filePath + "'; Extended Properties=Excel 5.0";

     

    //Create the connection

    ExcelConnection =

    new System.Data.OleDb.OleDbConnection(ConnectionString);

     

    //create a string for the query

     

    string ExcelQuery;

     

    //Sheet1 is the sheet name

     

    //create the query:

     

    //read columns from the Excel file

    ExcelQuery =

    "Select * from [Sheet1$]"; // from Sheet1";

     

    //use "Select * ... " to select the entire sheet

     

    //create the command

    System.Data.OleDb.

    OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

     

    //Open the connection

    ExcelConnection.Open();

     

    //Create a reader

    System.Data.OleDb.

    OleDbDataReader ExcelReader;

    ExcelReader = ExcelCommand.ExecuteReader();

     

    //For each row after the first

     

    while (ExcelReader.Read())

    {
    }
    }
    }


    thanks,
    kshitij

    • Proposed as answer by Kshitijb Friday, February 12, 2010 12:23 PM
    • Unproposed as answer by Mike Walsh FIN Friday, February 12, 2010 4:54 PM
    • Marked as answer by Chengyi Wu Monday, February 15, 2010 2:16 AM
    Friday, February 12, 2010 12:23 PM

All replies

  • Hi!

    what u have to do is to downlaod that file from sharepoint doc. library to some temp location and read the file via
    sqloledbreader  and fire the sql query at the same time to store it in SQL server and then delete the file from temp location.

    let me know if any issues ...

    regards,
    kshitij bishnoi
    Friday, February 12, 2010 9:39 AM
  • Hi Kshitij,

    Thank you very much for your advice.

    I would be glad if you can share any links has sample code.

    Do we have any custom controls to achieve this through SSIS 2008?

    Thanks,
    Cool Developer
    Thanks, Cool Developer
    Friday, February 12, 2010 10:35 AM
  • Hi!
    this code i have written becuase i donot find any soltions on net for this , u can try with this . :)

    System.Data.OleDb.

    OleDbConnection ExcelConnection = null;

     

    FileMode fileMode;

     

    string filePath = ConfigurationManager.AppSettings["TempLoaction"] + "\\" + fileName;

     

    using (SPSite _site = new SPSite(SPContext.Current.Web.Url))

    {

     

    using (SPWeb _web = _site.OpenWeb())

    {

     

    string docLibrary = ConfigurationManager.AppSettings["DocumentLibrary"];

     

    SPFile _file = _web.GetFile("/" + docLibrary + "/" + fileName);

    fileMode =

    FileMode.Create;

     

    byte[] byteArray = _file.OpenBinary();

     

    MemoryStream dataStream = new MemoryStream(byteArray);

     

    Stream stream = dataStream;

     

     

    using (FileStream fs = File.Open(filePath, fileMode))

    {

     

    byte[] buffer = new byte[4096];

     

    int bytesRead;

     

    while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0)

    {

    fs.Write(buffer, 0, bytesRead);

    }

    fs.Close();

    }

    }

    }

     

    //Create the Connection String

     

    try

    {

     

    string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;

    Data Source ='"

    + filePath + "'; Extended Properties=Excel 5.0";

     

    //Create the connection

    ExcelConnection =

    new System.Data.OleDb.OleDbConnection(ConnectionString);

     

    //create a string for the query

     

    string ExcelQuery;

     

    //Sheet1 is the sheet name

     

    //create the query:

     

    //read columns from the Excel file

    ExcelQuery =

    "Select * from [Sheet1$]"; // from Sheet1";

     

    //use "Select * ... " to select the entire sheet

     

    //create the command

    System.Data.OleDb.

    OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);

     

    //Open the connection

    ExcelConnection.Open();

     

    //Create a reader

    System.Data.OleDb.

    OleDbDataReader ExcelReader;

    ExcelReader = ExcelCommand.ExecuteReader();

     

    //For each row after the first

     

    while (ExcelReader.Read())

    {
    }
    }
    }


    thanks,
    kshitij

    • Proposed as answer by Kshitijb Friday, February 12, 2010 12:23 PM
    • Unproposed as answer by Mike Walsh FIN Friday, February 12, 2010 4:54 PM
    • Marked as answer by Chengyi Wu Monday, February 15, 2010 2:16 AM
    Friday, February 12, 2010 12:23 PM
  • As requested to your earlier, Kshitijb, please do not propose your own posts.
    FAQ sites: (SP 2010) http://wssv4faq.mindsharp.com; (v3) http://wssv3faq.mindsharp.com and (WSS 2.0) http://wssv2faq.mindsharp.com
    Complete Book Lists (incl. foreign language) on each site.
    Friday, February 12, 2010 4:55 PM
  • You can use the Excel Interop dlls to read excel files without having to download a copy of the excel document to a local path.

     

    Array ExcelValues = null;

    web.AllowUnsafeUpdates =

    true;
    string workbookPath = "http://server:8080/DocLibrary/Doc1.xls";

     

    ApplicationClass excel = new ApplicationClass();
    excel.Visible =
    false;
    Workbook excelWorkbook = excel.Workbooks.Open(workbookPath, 0, true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

     

    Sheets sheets = excelWorkbook.Worksheets;
    Worksheet worksheet = (Worksheet)sheets.get_Item(1);
    Range range = worksheet.get_Range("A1" , "M100" );

    ExcelValues = (System.

    Array)range.Cells.Value2;
    excel.Workbooks.Close();




    You can also use excel services in MOSS to read the excel file from the document library.


    Karla.

    Friday, February 12, 2010 6:59 PM
  • I'm looking for an alternative that does not use Interop (because they require Excel to installed in the server) and preferably do not involve saving the file to a temporary physical storage. 

    Isn't it possible to read the Excel file in a Document Library as a stream? My tests were unsuccessful due stupid casting issues between SPFileStream and FileStream.

     

    Thank you


    Half Abude Scheidl
    Monday, November 1, 2010 10:16 AM
  • IS THERE ANY WAY WE CAN READ AN EXCEL FILE UPLOADED IN A DOCUMENT LIBRARY THROUGH AN EVENT RECEIVER IN A SANDBOX SOLUTION?I GET A SECURITY EXCEPTION IF OLEDBCONNECTION OBJECT IS USED.

    Tuesday, March 20, 2012 10:24 PM
  • IS THERE ANY WAY WE CAN READ AN EXCEL FILE UPLOADED IN A DOCUMENT LIBRARY THROUGH AN EVENT RECEIVER?
    Friday, August 17, 2012 7:16 AM
  • Dear kshitijb,

    I tried the above code sample in vs2010 sandbox solution but i am getting the following error  at line  

    using (FileStream fs = File.Open(savepath, fileMode))

    here savepath="C:\\Uploads\\Data.xlsx";

    Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    The trust level is Full in the web.config file

    kindly help me to fix this..

    Thanks in advance


    TARUN


    • Edited by TARUN00197 Monday, May 5, 2014 10:06 AM detailed explanation
    Monday, May 5, 2014 10:04 AM