Converting Data from memory stream to Data table

Question

I am trying to fetch a data from an excel file url on a sharepoint site and put it in a data datatable but getting this error:

The input stream is not a valid binary format. The starting contents (in bytes) are: 50-4B................

Here is the code which i am using :-

WebClient webClient = new WebClient();
webClient.Credentials = CredentialCache.DefaultNetworkCredentials;
byte[] data = webClient.DownloadData("http://sp2010sw127:2000/eur/Home/TestLibrary/Details.xlsx");
MemoryStream memoryStreamOfFile = new MemoryStream(data);
BinaryFormatter formatter = new BinaryFormatter();
memoryStreamOfFile.Seek(0, SeekOrigin.Begin);
DataTable dt = (DataTable)formatter.Deserialize(memoryStreamOfFile);

Please advice.

Is the downloaded data serialized using BinaryFormatter as well? — dburner
– dburner, Commented Jan 10, 2014 at 8:33
The data you are downloading is an Excel file - there isn't a straightforward cast straight to DataTable method. You'll need to download the Excel file, save it somewhere, then extract the data from it. — dash
– dash, Commented Jan 10, 2014 at 8:38

Community · Accepted Answer · 2017-05-23 12:13:07Z

0

The data you are downloading is an Excel spreadsheet; this is a completely different thing to a DataTable; when you try and do the following:

DataTable dt = (DataTable)formatter.Deserialize(memoryStreamOfFile);

Then you are trying to deserialize a stream of bytes that represent the Excel spreadsheet into a DataTable object which isn't possible.

Instead, you could download the file first:

WebClient webClient = new WebClient();
webClient.Credentials = CredentialCache.DefaultNetworkCredentials;
webClient.DownloadFile(
              "http://sp2010sw127:2000/eur/Home/TestLibrary/Details.xlsx",
              "C:\\Details.xlsx");

And then use the method below (which I've customized from my answer here):

DataTable myData = LoadFromExcelFile("C:\\Details.xlsx");

Note that you'll want to consider a more appropriate place to save the file, and you'll also need to look at the SELECT query to get the columns you want from the sheet (named [Sheet1$] in the example below).

public DataTable LoadFromExcelFile(string filePath)
{
    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel - you can query the sheet as if it were a sql table effectively
        using (OleDbCommand cmd = new OleDbCommand("SELECT [Column1],[Column2],[Column3] from [Sheet1$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                DataTable myData = new DataTable();
                myData.Load(dReader);
                return myData;
            }
        }
    }
}

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Jan 10, 2014 at 8:50

dash

91.7k4 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rameez Over a year ago

Thanks for the reply....but unfortunately it is giving exception on below line of code. webClient.DownloadFile("sp2010sw127:2000/eur/Home/TestLibrary/…:\\Details.xlsx");

dash Over a year ago

What's the exception? In your reply above, you are missing the http:// from the URL. Also check you have permission to save files to `C:`

Rameez Over a year ago

error is "An exception occured during a web client request" , aLSO I HAVE full permissions and have provided the address properly

dash Over a year ago

See msdn.microsoft.com/en-us/library/ez801hhe%28v=vs.110%29.aspx for how to use DownloadFile - are you able to access the file directly?

Rameez Over a year ago

Yes I am able to access using webClient.DownloadData("sp2010sw127:2000/eur/Home/TestLibrary/Details.xlsx");

|

Roushan · Accepted Answer · 2021-03-02 14:45:38Z

0

binaryformatter shoul not be used. It is dangerious.

https://aka.ms/binaryformatter

answered Mar 2, 2021 at 14:45

Roushan

2744 silver badges16 bronze badges

Collectives™ on Stack Overflow

Converting Data from memory stream to Data table

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related