Retrieve data from top-1000-company site

Introduction

This Solution is used to retrieve data from site http://top1000.dk/ and then import to Catglobe system

Solution

We have

• Server Path: \catproc\Share\CatGlobe Teams\Production\Top 1000\
• Use Tool Top1000dk.exe to get raw data from http://top1000.dk/ site from server path
• Customize data, import to Excel file
• Run Macro (get Macro source code from from server path

Step 1 - Generate files by Internal Tool

• Run Top1000dk.exe Tool, input index of page. Generate all pages. Note: run only one instance of this tool at a time.

<Top1000Tool_1 Image>

• Go to your local C:\ to get exported files

Step 2 - Customize raw data

• Merge all files we get from step1 to one file
• Replace character "~" as "$$" and "|" as "$" (the purpose is separating into columns when importing these files to Excel)

<Customize data Image>

• Save file as txt format

Step 2 - Import to Excel

• Open Excel application -> select Data tab -> Get External data -> From Text
• Browse the txt file we get from step2
• Follow step 1: Choose Delimited option

  <Import 1 Image>

• Follow step 2: Unselect "Tab" option, Select "Other" option = @

  <Import 2 Image>

Step 3 - Customize Excel data

Because of limitation of the original site http://top1000.dk/, we encounter some problems with Unicode and long names which are displayed as "..."

• Open file "Unicode name" from server path to get list of incorrect names and replacements.
• Replace all incorrect unicode character in file we get from Step2 by correct ones. Tips: we use IFERROR and VLOOKUP functions

<Vlookup Image>

• Similarly, replace three-dot companies from the file 3-dot companies. We get the list of replacements of three-dot companies from file "3-dot companies"

Step 3 - Run Macro

• Get Macro source code from server path
• Orderly run these functions:

- Run Macro "top1000One"
- Run Macro "top1000Two"
- Run Macro "top1000Three"
- Run Macro "top1000Four"