import_xls.pl
Task: import excel file, containing information about the project into the database.
File has 2 worksheets:
- words
- project
At first we parse
project worksheet
| project |
|
google_domains |
|
| pages_to_check | $pages_to_check |
language | de |
| pages_to_save | $pages_to_save | country_code | de |
| competitor_benchmark | 150 | domain | http://google.de/search?q=%word%&pws=0 |
| domain | domain.de | |
project worksheet
The first row contains the names of the tables. Every other row contains column name and value for each table. These values must be inserted into the
database tables
| project |
| id |
domain |
pages_to_check |
pages_to_save |
competitor_benchmark |
| $project_id |
domain.de |
$pages_to_check |
$pages_to_save |
150 |
| google_domains |
| id |
domain |
language |
country_code |
| $google_domains_id |
http://google.de/search?q=%word%&pws=0 |
de |
de |
$project_id and $google_domains_id are automatically generated by MySQL and will be used later.
words worksheet
When we have the same word, belonging to the different group, it must only be added to words_in_group table, if it's missing there. Here the word 1_1 encounters in 2 rows with the same data, except for the group.
| KEY |
GLO |
LOC |
CPC |
GROUP |
| 1_1 |
1_2 |
1_3 |
1_4 |
1_5 |
| 1_1 |
1_2 |
1_3 |
1_4 |
2_5 |
words worksheet
database tables
| words |
| id |
word |
| $word_id |
1_1 |
| words_groups |
| id |
name |
| $group_id1 |
1_5 |
| $group_id2 |
2_5 |
| words_in_group |
| group_id |
word_id |
| $group_id1 |
1_5 |
| $group_id2 |
1_5 |
| project_words_domains |
| project_id |
domain_id |
word_id |
global_monthly_search |
local_monthly_search |
CPC |
| $project_id |
$google_domains_id |
$word_id |
1_2 |
1_3 |
1_4 |
Attaching words to the project and google domain, using project_words_domains table
$word_id, $group_id1, $group_id2 are generated automatically
Functions
- import_excel
- parse_header
- read_row
- get_group_id
- parse_domain_worksheet
- check_word
- add_to_words
- add_words_in_group
- insert_into_table