Download Ean Zip File Extract And Save To Computer

In this blog, I will show you how to download the Zip files that EAN use for the properties that they display. The code will check the date of the remote file before downloading to ensure that it is newer than the version on your computer, this will save bandwidth as there is no point in downloading the file if it has not changed since your last download.

After that check, we can download the zip files and extract the text file so that the data inside can be added to the database.

For this example, the locations of the files are stored in an XML file, you could store them in a database or text file if required, but for this example, an XML file is used.

XML File

In the XML file, I could have just entered file names and URLs separately, then used combine to join them together, but for this example, I have added the URL and file name in the same element.
The app.config file is used to store the location of the XML file and the framework version.

<?xml version="1.0" encoding="utf-8" ?>
<websites>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/ActivePropertyList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\ActivePropertyList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\ActivePropertyList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/AreaAttractionsList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\AreaAttractionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\AreaAttractionsList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/CountryList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\CountryList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\CountryList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/DiningDescriptionList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\DiningDescriptionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\DiningDescriptionList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/HotelImageList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\HotelImageList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\HotelImageList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/PolicyDescriptionList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\PolicyDescriptionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\PolicyDescriptionList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/PropertyDescriptionList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\PropertyDescriptionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\PropertyDescriptionList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/RecreationDescriptionList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\RecreationDescriptionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\RecreationDescriptionList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/SpaDescriptionList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\SpaDescriptionList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\SpaDescriptionList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
  <website>
    <RemoteUrl>https://www.ian.com/affiliatecenter/include/V2/WhatToExpectList.zip</RemoteUrl>
    <LocalUrl>F:\\EAN\\WhatToExpectList.zip</LocalUrl>
    <NameOfTextFile>F:\\EAN\\WhatToExpectList.txt</NameOfTextFile>
    <ExtractUrlFolder>F:\\EAN\\</ExtractUrlFolder>
  </website>
</websites>

App.Config Code

Next, we have the code for the application, as its just a small app, I have put all the code in one file. For larger applications, it would be best to split the code up into different files.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
  <appSettings>
    <add key="Urls" value="F:\DownloadEan\DownloadEan\Urls.xml"/>
  </appSettings>
</configuration>

Main Code

So now we have the data downloaded and extracted from the Zip files, how do we add the data to SQL server.
This is done by using a template that matches the schema of the data and database. I have added the code for one file below; all the templates can be found on Github with this code example.

using System;
using System.Configuration;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net;
using System.Xml.Linq;
namespace DownloadEan
{
    static class Program
    {
        static void Main(string[] args)
        {
            DataFile.EanDataList();
        }
    }
    internal class LocationOfFileAndWhereToSave
    {
        public string RemoteUrl         { get; set; }
        public string LocalUrl          { get; set; }
        public string ExtractUrlFolder  { get; set; }
        public string NameOfTextFile    { get; set; }
    }
    internal static class DataFile
    {
        public static void EanDataList()
        {
            if (!File.Exists(ConfigurationManager.AppSettings["URLs"])) return;
            var doc = XDocument.Load(ConfigurationManager.AppSettings["URLs"]);
            var xmlFile = doc.Descendants("website").Select(xe => new LocationOfFileAndWhereToSave
            {
                RemoteUrl           = xe.Element("RemoteUrl") != null ? xe.Element("RemoteUrl").Value : "",
                LocalUrl            = xe.Element("LocalUrl") != null ? xe.Element("LocalUrl").Value : "",
                ExtractUrlFolder    = xe.Element("ExtractUrlFolder") != null ? xe.Element("ExtractUrlFolder").Value : "",
                NameOfTextFile      = xe.Element("NameOfTextFile") != null ? xe.Element("NameOfTextFile").Value : ""
            }).ToList();
            //Need to add code on line below to prevent the following error > The request was aborted: Could not create SSL/TLS secure channel
            //Only needed if using https
            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
            foreach (var element in xmlFile)
            {
                HttpWebRequest request = WebRequest.Create(element.RemoteUrl) as HttpWebRequest;
                if (request != null)
                {
                    request.Method = "HEAD";
                    HttpWebResponse response = request.GetResponse() as HttpWebResponse;
                    if (response != null)
                    {
                        DateTime lastModifiedRemoteFile = Convert.ToDateTime(response.LastModified);
                        FileInfo zipFile = new FileInfo(element.LocalUrl);
                        if (zipFile.Exists)
                        {
                            DateTime lastModifiedLocalFile = Convert.ToDateTime(zipFile.LastWriteTime);
                            if (lastModifiedLocalFile < lastModifiedRemoteFile)
                            {
                             bool successfail = DownloadZipFile.ExtractFile(element.RemoteUrl, element.LocalUrl, element.ExtractUrlFolder, element.NameOfTextFile, lastModifiedRemoteFile);
                                if (successfail)
                                {
                                    Console.WriteLine("Local file updated: " + element.LocalUrl);
                                }
                                else
                                {
                                    Console.WriteLine("Local file not updated: " + element.LocalUrl);
                                }
                            }
                            else
                            {
                                Console.WriteLine("Local file is newer:" + element.LocalUrl);
                            }
                        }
                        else
                        {
                            Console.WriteLine("Zip file does not exist: " + element.LocalUrl);
                            bool successfail = DownloadZipFile.ExtractFile(element.RemoteUrl, element.LocalUrl, element.ExtractUrlFolder, element.NameOfTextFile, lastModifiedRemoteFile);
                            if (successfail)
                            {
                                Console.WriteLine("Local file created: " + element.LocalUrl);
                            }
                            else
                            {
                                Console.WriteLine("Local file not created: " + element.LocalUrl + " " + "continue to next url");
                            }
                        }
                        response.Dispose();
                    }
                    else
                    {
                        //No response back from server so throw error
                        throw new ApplicationException("No response header from remote server");
                    }
                }
            }
        }
    }
}
internal static class DownloadZipFile
{
    public static bool ExtractFile(string remoteUrl, string localUrl, string extractUrl, string nameOfFile, DateTime lastModifiedRemoteFile)
    {
        try
        {
            using (WebClient webClient = new WebClientWithTimeout())
            {
                webClient.Headers.Add("user-agent","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36");
                webClient.DownloadFile(remoteUrl, localUrl);
                FileInfo zipFile = new FileInfo(localUrl);
                //Check that the file has downloaded.
                if (zipFile.Exists)
                {
                    FileInfo destFile = new FileInfo(nameOfFile);
                    if (destFile.Exists)
                    {
                        destFile.Delete();
                    }
                    ZipFile.ExtractToDirectory(localUrl, extractUrl);
                    //Need to set lastwritetime of local file to that of downloaded file, so that date and time match.
                    File.SetLastWriteTime(localUrl, lastModifiedRemoteFile);
                    Console.WriteLine("File created: " + localUrl);
                    return true;
                }
                Console.WriteLine("Cannot find dowloaded zip file: " + localUrl);
                return false;
            }
        }
        catch (Exception)
        {
            Console.WriteLine("Error downloading file: " + remoteUrl);
            return false;
        }
    }
}
internal class WebClientWithTimeout : WebClient
{
    protected override WebRequest GetWebRequest(Uri address)
    {
        WebRequest wr = base.GetWebRequest(address);
        //If you have a timeout error, change timeout setting here.
        wr.Timeout = 10000;
        return wr;
    }
}

XML Template Code
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  
  <FIELD ID="01" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="02" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="03" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="246" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="04" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="05" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="256" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="06" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="01" NAME="CountryID" 		xsi:type="SQLINT" NULLABLE="YES"/>
  <COLUMN SOURCE="02" NAME="LanguageCode" 	xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="03" NAME="CountryName" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="04" NAME="CountryCode" 	xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="05" NAME="Transliteration" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="06" NAME="ContinentID" 				xsi:type="SQLNVARCHAR" NULLABLE="YES"/>
  </ROW>
</BCPFORMAT>

Stored Procedure

To get the data into the database, I use a stored procedure, all the table code and stored procedures are supplied on Github but for this example, I will show just the one.

USE [EAN]
GO
/****** Object:  StoredProcedure [dbo].[spInsertCountryList]    Script Date: 08/06/2017 12:11:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsertCountryList]
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
MERGE [dbo].[tblCountrylist] AS Target
USING (SELECT * FROM OPENROWSET(BULK 'F:\EAN\countrylist.txt',
	  FORMATFILE='F:\EAN\countrylist.xml', FIRSTROW = 2) AS BCP) AS Source
-- primary key to find matching records
ON Target.CountryID = Source.CountryID
-- UPDATE RECORD
WHEN MATCHED THEN UPDATE SET 
	 Target.LanguageCode		= Source.LanguageCode, 
	 Target.CountryName			= Source.CountryName,
	 Target.CountryCode			= Source.CountryCode,
	 Target.Transliteration		= Source.Transliteration,
	 Target.ContinentID			= Source.ContinentID
-- INSERT RECORD
WHEN NOT MATCHED BY Target 
	THEN INSERT(CountryID, 
				LanguageCode, 
				CountryName,
				CountryCode,
				Transliteration,
				ContinentID) 
	VALUES(	Source.CountryID, 
			Source.LanguageCode, 
			Source.CountryName,
			Source.CountryCode,
			Source.Transliteration,
			Source.ContinentID)
-- DELETE RECORD
WHEN NOT MATCHED BY Source THEN DELETE
-- report UPDATE, DELETE and INSERT operations
OUTPUT $action, 
DELETED.CountryID AS TargetCountryID,  
INSERTED.CountryID AS SourceCountryID;
SELECT @@ROWCOUNT;
END

Summary

This blog has shown how you can download EAN data to extract and save to your computer, I then showed you the templates for getting the data and adding to SQL server.

I hope you found this post helpful and if you have any questions or would like to leave a comment, please use the form below.

If you found this blog helpful, then please leave a comment.

Blog Form

 Please complete the required fields (*required)

 *
*