Category: CODES

sorts out unique phone number from KoreaDaily comprehensive downloads:

SELECT MIN(ID) AS min_ID, field9
FROM combined_output_02
GROUP BY field9

selects Unique phone:

select * from combined_output_02 where ID in (select min_ID from unique_phone)

AreaCode addition:

select combined_output_02_unique_phone.* , AreaCode_TimeZone.StandardTimeZone from combined_output_02_unique_phone LEFT join AreaCode_TimeZone on combined_output_02_unique_phone.AreaCode = AreaCode_TimeZone.AreaCode

---

---

below code updates 한글 상호:

UPDATE combined_output_02
SET field8 = field7
WHERE field8 IS NULL;

---

copy phone TEL: xxx-xxx-xxx:

UPDATE combined_output_02
SET field9 = field5
where field5 like 'TEL:%' and length(field5) = 17

---

reformat phone TEL: (xxx) xxx-xxxx:

UPDATE combined_output_02
SET field9 = substr(field5, 1, 5) || substr(field5, 7, 3) || '-' || substr(field5, 12, 3) || '-' || substr(field5, -4) 
WHERE field5 like 'TEL%(%)%' and length(field5) = 19

---

reformat phone TEL: (xxx)xxx-xxxx:

UPDATE combined_output_02
SET field9 = substr(field5, 1, 5) || substr(field5, 7, 3) || '-' || substr(field5, 11, 3) || '-' || substr(field5, -4)
WHERE field5 like 'TEL%(%)%' and length(field5) = 18

---

repeat above for TEL: in field4 and field6

---

combines from list of file names in combine.txt:

#!/usr/bin/perl

use strict;
use warnings;
use File::Spec;

# Assume the current directory contains this script, combine.txt, and the files to be combined
my $folder_path = File::Spec->rel2abs('.');

# File containing the list of filenames to combine
my $list_file = "$folder_path/combine.txt"; # Path to combine.txt in the same folder

# Output file where the combined content will be stored
my $output_file = "$folder_path/combined_output_02.txt"; # Output file in the same folder

# Clear the contents of the output file before appending new content
open(my $clear_fh, '>', $output_file) or die "Cannot open $output_file for writing: $!";
close($clear_fh);

# Open the output file for appending
open(my $out_fh, '>>', $output_file) or die "Cannot open $output_file for appending: $!";

# Open and read the list file containing the filenames to combine
open(my $list_fh, '<', $list_file) or die "Cannot open $list_file for reading: $!";
my @files_to_combine = <$list_fh>;
chomp(@files_to_combine); # Remove newline characters from each filename
close($list_fh);

# Iterate through each file listed in combine.txt, read its content, and append to the output file
foreach my $file (@files_to_combine) {
    my $file_path = "$folder_path/$file"; # Construct the full path for each file
    open(my $in_fh, '<', $file_path) or die "Cannot open $file_path for reading: $!";
    while (my $line = <$in_fh>) {
        print $out_fh $line; # Append the content of the file to the output file
    }
    close($in_fh);
}

# Close the output file handle
close($out_fh);

print "Combined files listed in $list_file into $output_file\n";

select extract_TEL_single_line_distinct_semicolon.* , bizname_semi.field2 from extract_TEL_single_line_distinct_semicolon LEFT join bizname_semi on extract_TEL_single_line_distinct_semicolon.field1 = bizname_semi.field3 order by extract_TEL_single_line_distinct_semicolon.field1

---

each of 12 cities, 가 나 다 라 마 바 사 아 자 차 카 타 파 하 manual download through Firefox into text file.

KoreaDaily.pl / KoreaDaily_URL.pl / KoreaDaily_URL_biz.pl

mecha.pl

5SETS.sh in mecha for mecha6, in mecha6 for mecha11, in mecha11 for mecha16 …

• filename.pl – the largest file # update

PARSE.sh

above SQL code

combine.pl

---

Visit “KoreaDaily Yelllowpage” using Firefox

Choose {시애틀}

다운로드, “가 나 다 라 마 바 사 아 자 차 카 타 파 하” as TEXT file using Firefox.

US_Cities.txt

Atlanta
Chicago
Texas
Washington_DC
Denver
Hawaii
Los_Angeles
New_York
San_Diego
Seattle
San_Francisco
Las_Vegas

US_Cities.pl

#!/usr/bin/perl

use strict;
use warnings;

# Open US_Cities.txt for reading
open(my $input_fh, '<', 'US_Cities.txt') or die "Could not open US_Cities.txt: $!";
# Open US_Cities_14.txt for writing
open(my $output_fh, '>', 'US_Cities_14.txt') or die "Could not create US_Cities_14.txt: $!";

# Read each line from US_Cities.txt
while (my $city = <$input_fh>) {
    chomp $city;  # Remove newline character
    # Write the city name followed by numbers 01 to 14 to US_Cities_14.txt
    print $output_fh "$city\_01.txt\n";
    print $output_fh "$city\_02.txt\n";
    print $output_fh "$city\_03.txt\n";
    print $output_fh "$city\_04.txt\n";
    print $output_fh "$city\_05.txt\n";
    print $output_fh "$city\_06.txt\n";
    print $output_fh "$city\_07.txt\n";
    print $output_fh "$city\_08.txt\n";
    print $output_fh "$city\_09.txt\n";
    print $output_fh "$city\_10.txt\n";
    print $output_fh "$city\_11.txt\n";
    print $output_fh "$city\_12.txt\n";
    print $output_fh "$city\_13.txt\n";
    print $output_fh "$city\_14.txt\n";
    print $output_fh "\n";  # Add a blank line between cities
}

# Close the file handles
close $input_fh;
close $output_fh;

print "US_Cities_14.txt has been created.\n";

KoreaDaily.pl (this code extracts each business type URL at KoreaDaily Yellowpage)

#!/usr/bin/perl

use strict;
use warnings;

# Define the output file name
my $output_file = 'KoreaDaily.txt';

# Open the output file for writing
open(my $fh_out, '>', $output_file) or die "Could not open file '$output_file' for writing: $!";

# Loop through files Atlanta_01.txt to Atlanta_13.txt
for my $file_number (1..14) {
    my $input_file = "Seattle_" . sprintf("%02d", $file_number) . ".txt";
    
    # Open the current input file for reading
    open(my $fh_in, '<', $input_file) or die "Could not open file '$input_file': $!";

    # Iterate through each line of the current input file
    while (my $line = <$fh_in>) {
        # Check if the line contains 'cat_code='
        if ($line =~ /cat_code=/) {
            # Write the line to the output file
            print $fh_out $line;
        }
    }

    # Close the current input file handle
    close($fh_in);
}

# Close the output file handle
close($fh_out);

print "Extraction completed. Results saved in '$output_file'.\n";

KoreaDaily_URL.pl (this code removes < and > from KoreaDaily.txt)

#!/usr/bin/perl
use strict;
use warnings;

# Define the input and output file names
my $input_file = "KoreaDaily.txt";
my $output_file = "KoreaDaily_URL.txt";

# Open the input file for reading
open my $input_fh, '<', $input_file or die "Cannot open file $input_file: $!";

# Open the output file for writing
open my $output_fh, '>', $output_file or die "Cannot open file $output_file: $!";

# Loop through each line of the input file
while (my $line = <$input_fh>) {
    chomp $line; # Remove newline character
    my @matches = $line =~ /<([^>]+)>/g; # Extract content between < and > using regex

    # Write the extracted content to the output file
    foreach my $match (@matches) {
        print $output_fh "$match\n";
    }
}

# Close the files
close $input_fh;
close $output_fh;

print "Extraction completed. Extracted content saved in $output_file.\n";

KoreaDaily_URL_biz.pl (removes none biz name URL)

#!/usr/bin/perl

use strict;
use warnings;

# Input and output file names
my $input_file = 'KoreaDaily_URL.txt';
my $output_file = 'KoreaDaily_URL_biz.txt';

# Open input and output files
open(my $input_fh, '<', $input_file) or die "Could not open file '$input_file': $!";
open(my $output_fh, '>', $output_file) or die "Could not create file '$output_file': $!";

# Read input file line by line
while (my $line = <$input_fh>) {
    # Check if the line does not end with 'sort=N'
    unless ($line =~ /sort=N$/) {
        # Write the line to the output file
        print $output_fh $line;
    }
}

# Close file handles
close($input_fh);
close($output_fh);

print "Filtered URLs saved to '$output_file'.\n";

mecha.pl (this code mechanize each page)

#!/usr/bin/perl

use strict;
use warnings;
use Encode;  # To handle encoding issues
use WWW::Mechanize;

# Read URLs from the text file
my $filename = 'onlypage6_modified.txt';

# Create a new WWW::Mechanize object
my $mech = WWW::Mechanize->new();

# Open the text file
open(my $fh, '<', $filename) or die "Could not open file '$filename' $!";

# Initialize page number
my $page_num = 1;

# Loop through each URL in the file
while (my $url = <$fh>) {
    chomp $url;  # Remove newline character

    # Check if the URL contains /list/list.asp
    next unless $url =~ m|/list/list\.asp|;

    # Try to visit the URL, skip on failure
    eval {
        $mech->get($url);
    };
    if ($@) {
        warn "Failed to get $url: $@";
        next; # Skip to the next URL on failure
    }

    # Get the content of the current page
    my $content = $mech->content();

    # Save the content to a file with EUC-KR encoding
    my $filename = sprintf("%d.html", $page_num);
    open(my $fh_out, '>:encoding(EUC-KR)', $filename) or die "Could not open file '$filename' for writing: $!";
    print $fh_out $content;
    close $fh_out;

    # Find pagination links and click on them to navigate through pages
    my @pagination_links = $mech->find_all_links(url_regex => qr/page=/i);
    for my $link (@pagination_links) {
        # Try to visit the pagination link, skip on failure
        eval {
            $mech->get($link->url);
        };
        if ($@) {
            warn "Failed to get pagination link $link->url: $@";
            next; # Skip to the next pagination link on failure
        }

        my $content = $mech->content();
        $page_num++;
        my $filename = sprintf("%d.html", $page_num);
        open(my $fh_out, '>:encoding(EUC-KR)', $filename) or die "Could not open file '$filename' for writing: $!";
        print $fh_out $content;
        close $fh_out;
    }

    # Increment page number
    $page_num++;
}

# Close the file handle
close($fh);

filename.pl (come up with mechanized file list file)

#!/usr/bin/perl
use strict;
use warnings;

# Open the output file for writing
open my $output_fh, '>', "file_names.txt" or die "Cannot open file file_names.txt for writing: $!";

# Generate file names from 1.html to 481.html and write them to the output file
for my $i (1..300) {
    my $file_name = "$i.html";
    print $output_fh "$file_name\n";
}

# Close the file
close $output_fh;

print "File names generated and saved to file_names.txt\n";

page_extract.pl (extracts mechanizable file names on downloaded file)

#!/usr/bin/perl

use strict;
use warnings;

# Define the input and output filenames
my $input_filename = 'file_names.txt';
my $output_filename = 'ahrefextract.txt';

# Open the output file for writing
open(my $fh_out, '>', $output_filename) or die "Could not open file '$output_filename' $!";

# Open the input file containing the list of HTML files
open(my $fh_in, '<', $input_filename) or die "Could not open file '$input_filename' $!";

# Loop through each HTML file in the input list
while (my $html_file = <$fh_in>) {
    chomp $html_file;  # Remove newline character

    # Open the HTML file for reading
    open(my $fh_html, '<', $html_file) or die "Could not open file '$html_file' $!";

    # Read the HTML content from the file
    my $html_content = do { local $/; <$fh_html> };

    # Close the HTML file handle
    close($fh_html);

    # Extract URLs matching the specified pattern
    while ($html_content =~ m/<a\s+[^>]*?href="([^"]*\/list\/list\.asp[^"]*)"/ig) {
        my $url = $1;
        print $fh_out "URL: $url\n";  # Write the extracted URL to the output file
    }
}

# Close the file handles
close($fh_in);
close($fh_out);

print "Extraction complete. Extracted URLs saved in $output_filename\n";

page6extract.pl

#!/usr/bin/perl

use strict;
use warnings;

# Input and output filenames
my $input_filename = 'ahrefextract.txt';
my $output_filename = 'page_6_extract.txt';

# Open the input file for reading
open(my $fh_in, '<', $input_filename) or die "Could not open file '$input_filename' $!";

# Open the output file for writing
open(my $fh_out, '>', $output_filename) or die "Could not open file '$output_filename' $!";

# Loop through each line in the input file
while (my $line = <$fh_in>) {
    # Check if the line contains 'page=6'
    if ($line =~ /page=6/) {
        # Write the line to the output file
        print $fh_out $line;
    }
}

# Close the file handles
close($fh_in);
close($fh_out);

print "Extraction complete. Lines containing 'page=6' saved in $output_filename\n";

page6unique.pl (come up with unique lines from extrated)

#!/usr/bin/perl

use strict;
use warnings;

# Input and output filenames
my $input_filename = 'page_6_extract.txt';
my $output_filename = 'onlypage6.txt';

# Hash to store unique lines
my %unique_lines;

# Open the input file for reading
open(my $fh_in, '<', $input_filename) or die "Could not open file '$input_filename' $!";

# Loop through each line in the input file
while (my $line = <$fh_in>) {
    # Remove leading and trailing whitespace
    $line =~ s/^\s+|\s+$//g;

    # Add the line to the hash (keys are unique)
    $unique_lines{$line} = 1;
}

# Close the input file handle
close($fh_in);

# Open the output file for writing
open(my $fh_out, '>', $output_filename) or die "Could not open file '$output_filename' $!";

# Write unique lines to the output file
foreach my $unique_line (keys %unique_lines) {
    print $fh_out "$unique_line\n";
}

# Close the output file handle
close($fh_out);

print "Unique lines from $input_filename saved in $output_filename\n";

onlypage6_modified.pl (update url)

#!/usr/bin/perl

use strict;
use warnings;

# Input and output file names
my $input_file = 'onlypage6.txt';
my $output_file = 'onlypage6_modified.txt';

# Open input and output files
open(my $input_fh, '<', $input_file) or die "Could not open file '$input_file': $!";
open(my $output_fh, '>', $output_file) or die "Could not create file '$output_file': $!";

# Read input file line by line
while (my $line = <$input_fh>) {
    # Replace 'URL: /list' with 'http://yp.koreadaily.com/list'
    $line =~ s/URL: \/list/http:\/\/yp.koreadaily.com\/list/;
    # Write the modified line to the output file
    print $output_fh $line;
}

# Close file handles
close($input_fh);
close($output_fh);

print "URLs modified and saved to '$output_file'.\n";

run above again for page=11, page=16, page=21, etc.

and run mecha.pl accordingly again.