How to get the Data in right format When reading an Excel file from FTP Server

Question

I am trying to read an excel file from an FTP Server and store the datas in a list. Excel file is reading from an FTP Server but data is coming

This is my code

 static void Main(string[] args)
    {
        //Listing all files from a folder

        string filename = getFileList("ftp://ftp.demosite.com/demoFolder/", "username", "password");


        //Here we know the file name
        FtpWebRequest reqFTP = (FtpWebRequest)FtpWebRequest.Create("ftp://ftp.demosite.com/demoFolder/"+filename);
        reqFTP.UsePassive = false;
        reqFTP.UseBinary = true;
        reqFTP.Credentials = new NetworkCredential("username", "password"); 
        reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
        reqFTP.Proxy = GlobalProxySelection.GetEmptyWebProxy();

        FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
        Stream responseStream = response.GetResponseStream();

        // for excelread
        StreamReader reader = new StreamReader(responseStream);
        string[] allLines = reader.ReadToEnd().Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
        // for textfile read
        //TextReader tmpReader = new System.IO.StreamReader(responseStream);
        //string fileContents = tmpReader.ReadToEnd();
        Console.WriteLine("Now writing from file....");
        foreach (var item in allLines)
        {
            Console.WriteLine(item);                
        }
        Console.WriteLine("all file content is printed....");
        Console.ReadKey();

    }

and the data is coming in this format: Current O/P Format

Please Help! Thank You.

Is the file a `.xls` file? Because that's a binary format. Even if it's `.xlsx` just splitting into lines isn't work. — stuartd, Sep 23 '16 at 11:30
You're going to need to read it using Excel or NPOI or something similar - what you're seeing is essentially what you get when you open a xls file in Notepad — stuartd, Sep 23 '16 at 11:37
that's right..So do i need to install that EXCEL or NPOI from nuget package mgr. and pass that StreamReader object? — Siladitya, Sep 23 '16 at 11:41
Maybe try reading the documentation? And i don't think Excel is on nuget. — stuartd, Sep 23 '16 at 11:41
Use BinaryReader like this : const int BUFFER_SIZE = 40; BinaryReader reader = new BinaryReader(responseStream); string data = string.Join("", reader.ReadBytes(BUFFER_SIZE).Select(x => x.ToString("2x"))); — jdweng, Sep 23 '16 at 12:41

score 1 · Accepted Answer · edited May 23 '17 at 11:33

1

As stuartd pointed out. You are reading an excel file as a text file. If you want to read an excel file you need to add the Office.Interop.Excel references to the project and then to your classes. If you have excel on your machine you can do this without a problem. If you do not have excel you will have to find a third party library. I recently posted an answer explaining how to do this if excel is on your machine.

Linking Excel In C#

Once the reference is there you can access excel files properly.

edited May 23 '17 at 11:33

Community

1
1

answered Sep 23 '16 at 16:42

JohnG

9,259
2
20
29

Assuming you are using an IDE. You first need to add the reference to your project. Right click on the project and add a reference under the COM section and look for Microsoft Excel Object Library. This will add the Office.Core and Interop.Excel references to your project. To use these library's in your classes...add a "using" statement to access the library. Then The class can open/create Excel files and do things like myWorkSheet.Cells[ARow, ACol] = "PF". That info can be found in the above link. Hope this helps. – JohnG Sep 24 '16 at 03:45
If you do not have Excel on your machine http://stackoverflow.com/questions/2654932/create-excel-files-from-c-sharp-without-office gives some options. – JohnG Sep 24 '16 at 04:01

How to get the Data in right format When reading an Excel file from FTP Server

1 Answers1