How to import data from a ZIP file stored on FTP server to database in C#

Question

This code imports the void.dat file from the FTP server. Now void.dat is under archive.zip file. So how can I extract and import data void.dat from the archive.zip in my database?

WebClient request = new WebClient();
string url = "ftp://agk.com/Files/" + "Void.dat";
request.Credentials = new NetworkCredential("user", "password");

byte[] newFileData = request.DownloadData(url);
string[] lines = System.Text.Encoding.UTF8.GetString(newFileData).Split('\r');
for (int i = 2; i < lines.Length-1; i++)
{
    int count = lines[i].Split('\t').Count();

        DateTime? Date              = Convert.ToDateTime((lines[i].Split('\t')[0]) == null || (lines[i].Split('\t')[0]) == "" || (lines[i].Split('\t')[0]) == "N" ? (lines[i].Split('\t')[2]) = null : (lines[i].Split('\t')[0])).Date;
        string   Time               = ((lines[i].Split('\t')[1]) == null || (lines[i].Split('\t')[1]) == "" || (lines[i].Split('\t')[1]) == "N" ? (lines[i].Split('\t')[1]) = null : (lines[i].Split('\t')[1]));
        int      Shift              = Convert.ToInt32((lines[i].Split('\t')[2]) == null || (lines[i].Split('\t')[2]) == "" || (lines[i].Split('\t')[2]) == "N" ? (lines[i].Split('\t')[2]) = null : (lines[i].Split('\t')[2]));
        int      EmployeeID         = Convert.ToInt32((lines[i].Split('\t')[3]) == null || (lines[i].Split('\t')[3]) == "" || (lines[i].Split('\t')[3]) == "N" ? (lines[i].Split('\t')[3]) = null : (lines[i].Split('\t')[3]));
        double   Amount             = Convert.ToDouble((lines[i].Split('\t')[4]) == null || (lines[i].Split('\t')[4]) == ""|| (lines[i].Split('\t')[4]) == "N" ? (lines[i].Split('\t')[4]) = null : (lines[i].Split('\t')[4]));
        double   Items              = Convert.ToDouble((lines[i].Split('\t')[5]) == null || (lines[i].Split('\t')[5]) == "" || (lines[i].Split('\t')[5]) == "N" ? (lines[i].Split('\t')[5]) = null : (lines[i].Split('\t')[5]));
        int      DrawerOpen         = Convert.ToInt32((lines[i].Split('\t')[6]) == null || (lines[i].Split('\t')[6]) == "" || (lines[i].Split('\t')[6]) == "N" ? (lines[i].Split('\t')[6]) = null : (lines[i].Split('\t')[6]));
        int      Postpone           = Convert.ToInt32((lines[i].Split('\t')[7]) == null || (lines[i].Split('\t')[7]) == "" || (lines[i].Split('\t')[7]) == "N" ? (lines[i].Split('\t')[7]) = null : (lines[i].Split('\t')[7]));
        int      LocationID         = Convert.ToInt32((lines[i].Split('\t')[8]) == null || (lines[i].Split('\t')[8]) == "" || (lines[i].Split('\t')[8]) == "N" ? (lines[i].Split('\t')[8]) = null : (lines[i].Split('\t')[8]));
        
        _context.Database.ExecuteSqlCommand(@"Insert into VOIDS (Date,Time,Shift,EmployeeID,Amount,Items,DrawerOpen,Postpone,LocationID)
    Values({0},{1},{2},{3},{4},{5},{6},{7},{8})", Date, Time, Shift, EmployeeID, Amount, Items, DrawerOpen, Postpone, LocationID);
        _context.SaveChanges();
    
}
return "Successful Import";

Martin Prikryl · Accepted Answer · 2021-02-26T10:29:27.170

To read a string contents from a file located in a ZIP archive on FTP server, use:

var request = WebRequest.Create("ftp://ftp.example.com/remote/path/archive.zip");
request.Credentials = new NetworkCredential("username", "password");
request.Method = WebRequestMethods.Ftp.DownloadFile;

string data;
using (Stream ftpStream = request.GetResponse().GetResponseStream())
using (var archive = new ZipArchive(ftpStream, ZipArchiveMode.Read))
{
    using (Stream entryStream = archive.GetEntry("void.dat").Open())
    using (var reader = new StreamReader(entryStream, Encoding.UTF8))
    {
        data = reader.ReadToEnd();
    }
}

string[] lines = data.Split('\r');
// rest of your code

As you split the string into lines anyway, more efficient would be to read the stream directly by lines:

    using (var reader = new StreamReader(entryStream, Encoding.UTF8))
    {
        string line;
        int i = 0;
        while ((line = reader.ReadLine()) != null)
        {
            // skip the first two lines, as your original code does
            if (i >= 2)
            {
                int count = line.Split('\t').Count();

                // rest of your code for parsing individual lines
                // (with 'lines[i]' replaced with 'line')
            }
            i++;
        }
    }

Note that the code downloads whole archive, even if it reads only one specific file from the archive in the end.

Your related question about an opposite operation:
How to upload zip and upload a string data to an FTP server in C#

Hi there, for your answer we get the archive from FTP Now how to save this data in my database like this code. in this code I am saving data in the database, so how your function works with this. byte[] newFileData = request.DownloadData(url); string[] lines = System.Text.Encoding.UTF8.GetString(newFileData).Split('\r'); for (int i = 2; i < lines.Length-1; i++) { string Time = ((lines[i].Split('\t')[1]) _context.Database.ExecuteSqlCommand(@"Insert into VOIDS (Date....) Values({0},{1}.....)", ); _context.SaveChanges(); } — NumanCS, Feb 26 '21 at 07:46
I believe that's pretty obvious. But, ok, see my updated answer. — Martin Prikryl, Feb 26 '21 at 08:01
Hi there, for the split, there is an error in your code stream-reader that does not contain the definition of split(). — NumanCS, Feb 26 '21 at 09:45

How to import data from a ZIP file stored on FTP server to database in C#

1 Answers1

Linked