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, Jan 10 '14 at 08: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, Jan 10 '14 at 08:38

score 0 · Answer 1 · answered Mar 02 '21 at 14:45

0

binaryformatter shoul not be used. It is dangerious.

https://aka.ms/binaryformatter

answered Mar 02 '21 at 14:45

Roushan

264
2
14

score -1 · Answer 2 · edited May 23 '17 at 12:13

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;
            }
        }
    }
}

Thanks for the reply....but unfortunately it is giving exception on below line of code. webClient.DownloadFile("http://sp2010sw127:2000/eur/Home/TestLibrary/Details.xlsx","C:\\Details.xlsx"); — Rameez, Jan 10 '14 at 09:48
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:\` — dash, Jan 10 '14 at 09:51
error is "An exception occured during a web client request" , aLSO I HAVE full permissions and have provided the address properly — Rameez, Jan 10 '14 at 10:00
See http://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? — dash, Jan 10 '14 at 10:02
Yes I am able to access using webClient.DownloadData("http://sp2010sw127:2000/eur/Home/TestLibrary/Details.xlsx"); — Rameez, Jan 10 '14 at 10:10
Are you able to access the file through a browser; if you are encountering an error, DownloadData will download the contents of the error page. — dash, Jan 10 '14 at 10:35

Converting Data from memory stream to Data table

2 Answers2