Създаване на списък с линкове

Това е третата част от серията статии как да сваляме данни от интернет в Ексел с помощта на Power Query. В първата ви показах как можем да изтеглим данните от една страница. В случая текстът на една песен. Във втората част видяхме как можем да превърнем тази заявка във функция и след това да свалим наведнъж данните от много еднотипни страници. В случая – текстовете на всички песни на Васил Найденов. За тази цел ни беше необходим списък с връзките (линковете) към тези страници, който тогава използвах на готово. В тази статия ще ви покажа как сравнително лесно може да си направите такъв списък.

Отново ще използваме текстовете на песните на Васил Найденов, публикувани на сайта www.textove.com. Въвеждам името на изпълнителя в търсачката на сайта

И се зарежда списък с неговите песни

Двоен клик върху всяко едно заглавие ни отваря страницата със съответния текст, от която можем да си вземем съответния адрес

Но отварянето на десетки линкове и ръчното копиране е неефективно и крие риск от грешки. Как можем да автоматизираме тази задача?

На първо време, забелязваме, че линковете имат една обща структура:

Най-общо състоят се от две части – първата е адресът на сайта, а втората се състои от името на изпълнителя, името на песента и завършва с „думата“ tekst.

Адресът на сайта е ясен, остава да се сдобием с втората част. И ето тук ще ви издам един малък трик, който вероятно е добре известен на хората, запознати с HTML. Когато сме в някаква страница, клавишната комбинация CTRL + U извежда нейния HTML код. Той изглежда така:

Не се притеснявайте, ако не разбирате нищо. Аз също не разбирам. Но това, което знам е, че всичко, което виждаме на дадена страница е описано във въпросния код. Просто скролваме надолу докато намерим заглавието на първата песен (Тишина). За по-лесно и бързо можем да използваме и CTRL + F (Find).

И наистина, някъде сред купищата неразбираеми команди се намира информацията, която ни трябва – заглавието на песните и втората част от адреса към тях:

Остава само да я обработим по някакъв начин и да я подредим таблично. За целта, вече може би се досещате, ще използваме Power Query. Този инструмент всъщност за това е измислен – да подреждаме с него неподредени в таблица данни.

Първата стъпка е да копираме частта от HTML кода, в която са описани заглавията на песните и техните адреси. Става по същия начин, по който го правим в Word или от някоя интернет страница – селектираме текста и CTRL + C заCopy.След това най-удобно е да го запишем в Note Pad като txt файл.

Обръщам ви внимание, че е възможно списъкът да е на повече от една страница, както ни показва тази стрелкичка

За това трябва да повторим процедурата по отваряне на HTML кода, намиране на частта с адресите на песните, копиране и пействане в Note Pad, колкото е необходимо. За съжаление това си е изцяло ръчен процес, за който автоматизация не зная.

След като сме сглобили един .txt файл, трябва да се анализира логиката на кода, за да се намери начин, по който да се извлекат адресите. Това е строго специфично за всеки един сайт, няма единни правила, но със сигурност мога да ви кажа, че винаги има някаква логика. В нашия случай, текстът с адреса винаги се намира след такъв стринг: =“url“ content =

Като имаме предвид тази информация, вече може да започнем да изграждаме нашата заявка, използвайки инструментите на Power Query. Действията, които следват по-долу са строго специфични за този сайт и трябва да се възприемат като демонстрация на възможностите на този инструмент.

Т.к. записахме данните в .txt файл, започваме заявката с From File > From Text (за версия Ексел 2013)

В следващия прозорец даваме Edit

Зареждат се няколко колони, но данните, които ни трябват са само в първата. Затова можем да премахнем останалите. Селектираме колоната с данните, десен бутон и избираме Remove other columns.

Следващата ни стъпка е да филтрираме само тези редове, които съдържат магическото словосъчетание =“url“ content =“

И ето – нашият списък започва да придобива вид. Имаме вече само редове, в които се съдържа втората част от адреса на линка с текста на песните.

Следващата задача, която трябва да разрешим е как да отстраним текста, който не ни трябва. Начините за това са много, но първо трябва да установим някаква логика, повторяемост в него и след това да я използваме. Най-простото в случая е да „разцепим“ колоната на няколко като използваме за разделител интервал.

Получаваме 32 колони, но тази, която ни трябва е последната, затова я маркираме, десен бутон върху нея и избираме Remove other columns. Така премахваме колоните, които не ни трябват и остава само една.

С Replace Values отстраняваме знака „=“

След това отново разделяме колоната, но този път използваме Custom разделител – “>”

Получаваме две колони. Втората съдържа само текста „</meta>“. Маркираме я, десен клик и избираме Remove

Вече имаме една колона, която съдържа текста, който ни е необходим.

За да няма неприятни изненади, добре е да я пипнем още малко с Trim и Clean. Trim изчиства излишните интервали в даден текст, а Clean – т.нар. non-printable символи (такива, които не се разпечатват). Не е задължително да има такива интервали и символи, но страх лозе пази.

Вече сме почти на финала. Имаме списък с втората част от адреса за всяка песен. Знаем, че първата е винаги https://textove.com/. Остава само да ги съединим. За целта добавяме нова колона, в която записваме простата формула: „https://textove.com/“&[Column1.32.1].

Т.е обединяваме https://textove.com/ с това, което е записано в първата колона. Същото е като формулата CONCATENATE.

Нашият списък е вече готов. Остава само да го заредим в Ексел с бутона Close&Load

И сме готови. Имаме си списък с точните адреси на всички текстове на песни:

Списъкът в този си вид е напълно достатъчен, за да свалим текстовете на всички песни в Ексел. Но с малко повече играчка и кликане по менютата на Power Query можем да си направим и такава таблица:

И накрая, ако се интересуваме не само от песните на Васил Найденов, но и на други изпълнители – винаги можем да ги добавим в txt файла, от който черпи данни заявката ни в Power Query.

Статията е част от поредица за сваляне и анализ на данни от интернет, чрез Power Query. Ето и връзките към другите статии:

  1. Сваляне на данни от Интернет с Power Query
  2. Сваляне на данни от много линкове в Интернет
  3. Създаване на списък с линкове
  4. Анализ на текст чрез Ексел
  5. Word clouds в Excel
Публикувано в PowerExcel с етикети , . Постоянна връзка.