查询以对列表运行字符串并提取数据

I work in the IT department for a company, and one of the task that falls under my task list is regularly updating the phone dial plan(every 3 months or so) to ensure all local area codes and exchanges are up to date.

我在公司的IT部门工作,我的任务列表中的任务之一是定期更新电话拨号计划(每3个月左右),以确保所有本地区域代码和交换都是最新的。

I usually go to the following website which gets updated every month to get a list of area codes and exchanges and look manually through another list I have which has all the regions I need to check for. The list is formatted for CSV, so I could paste it in excel or a DB.

我通常会访问以下网站,该网站每个月都会更新,以获取区号和交换的列表,并手动查看我拥有的其他列表,其中包含我需要检查的所有区域。该列表的格式为CSV,因此我可以将其粘贴到Excel或DB中。

My question is, is there a query I can run that will take all the regions from list 2, compare it against list 1, and get the first and second column of list 1? I believe this can be achieved by SQL or Excel or perhaps another way using Powershell or Linux scripting. Since I will be doing this regularly, I would just want to paste the new list every month, run it against my list 2, and get all the new exchanges added.

我的问题是,是否有一个我可以运行的查询将从列表2中取出所有区域,将它与列表1进行比较,并得到列表1的第一列和第二列?我相信这可以通过SQL或Excel或者使用Powershell或Linux脚本的其他方式实现。由于我将定期执行此操作,因此我只想每月粘贴新列表,在列表2中运行它,并添加所有新的交换。

 Sample of List 1:

 http://cnac.ca/data/COCodeStatus_NPA613.txt
 613,200,8303,TELUS Mobility,In Service,Perth,
 613,201,920D,Westport Telephone Company Limited,In Service,Perth,
 613,202,8821,Rogers Communications Partnership (Wireless),In Service,Bancroft,
 613,203,6574,Bell Mobility,In Service,Ottawa-Hull,
 613,204,6574,Bell Mobility,In Service,Ottawa-Hull,
 613,205,2782,TELUS Integrated Communications,In Service,Smiths Falls,
 613,206,8303,TELUS Mobility,In Service,Smiths Falls,
 613,207,8303,TELUS Mobility,In Service,Smiths Falls,
 613,208,8377,Rogers Communications Partnership (Cable),In Service,Trenton,
 613,209,154E,Iristel Inc.,In Service,Cornwall,
 613,210,8377,Rogers Communications Partnership (Cable),In Service,Belleville,
 613,211,,,For Special Use,,Public Information and Referral Services
 613,212,2782,TELUS Integrated Communications,In Service,Ottawa-Hull,
 613,213,6574,Bell Mobility,In Service,Brockville,
 613,214,6574,Bell Mobility,In Service,Kingston,
 613,215,2782,TELUS Integrated Communications,In Service,Kemptville,
 613,216,8377,Rogers Communications Partnership (Cable),In Service,Ottawa-Hull,
 613,217,6574,Bell Mobility,In Service,Kingston,
 613,218,8821,Rogers Communications Partnership (Wireless),In Service,Ottawa-Hull,
 613,219,8821,Rogers Communications Partnership (Wireless),In Service,Ottawa-Hull,
 613,220,8821,Rogers Communications Partnership (Wireless),In Service,Ottawa-Hull,
 613,221,8051,Bell Canada,In Service,Ottawa-Hull,
 613,222,6574,Bell Mobility,In Service,Ottawa-Hull,
 613,223,8819,TELUS Mobility,In Service,Ottawa-Hull,
 613,224,8051,Bell Canada,In Service,Ottawa-Hull,
 613,225,8051,Bell Canada,In Service,Ottawa-Hull,
 613,226,8051,Bell Canada,In Service,Ottawa-Hull,
 613,227,8819,TELUS Mobility,In Service,Ottawa-Hull,
 613,228,8051,Bell Canada,In Service,Ottawa-Hull,
 613,229,8819,TELUS Mobility,In Service,Ottawa-Hull,
 613,230,8051,Bell Canada,In Service,Ottawa-Hull,
 613,231,8051,Bell Canada,In Service,Ottawa-Hull,
 613,232,8051,Bell Canada,In Service,Ottawa-Hull,
 613,233,8051,Bell Canada,In Service,Ottawa-Hull,
 613,234,8051,Bell Canada,In Service,Ottawa-Hull,
 613,235,8051,Bell Canada,In Service,Ottawa-Hull,

 Sample of List 2:
 Carleton Place, Ont
 Carp, Ont
 Casselman, Ont
 Chelsea, Que
 chesterville, Ont

EDIT: I would also like the script to verify column "Status" and make sure all numbers returned have the status "In service" but doesn't display "In Service" in the output. I would like the output to display just the area code followed by the exchange(EX. 613230)

编辑:我还希望脚本验证列“状态”并确保返回的所有数字都具有“在服务中”状态,但在输出中不显示“在服务中”。我希望输出只显示交换后的区号(EX.613230)

2 个解决方案

#1

You could easily do the matching (and downloading) in PowerShell. Here's a sample for you, which you can alter to your own liking:

您可以轻松地在PowerShell中进行匹配(和下载)。以下是您的样本,您可以根据自己的喜好进行更改:

Update: The answer now includes the added requirements from the question update.

更新:答案现在包括问题更新中添加的要求。

Given that a file named areasToInclude.txt contains a list of areas to include, one area per line, like so:

鉴于名为areasToInclude.txt的文件包含要包含的区域列表,每行一个区域,如下所示:

Carleton Place
Carp
Casselman
Chelsea
Chesterville
Brighton

The following script should do the filtering and selection as requested:

以下脚本应按要求进行过滤和选择:

function Get-AreaCodes
{
    PARAM (
        [string[]]$AreasToRetrieve,
        [string]$StatusFilter
    )

    $webRequestResults = Invoke-WebRequest -Uri "http://cnac.ca/data/COCodeStatus_NPA613.txt"
    $dataFromExternalSource = ConvertFrom-Csv $webRequestResults.Content

    foreach ($line in $dataFromExternalSource)
    {
        if ($AreasToRetrieve.Contains($line.'Rate Center') -AND $line.Status -eq $StatusFilter)
        {
            Write-Output $line
        }
    }
}

$areasToRetrieve = Get-Content "areasToInclude.txt"

#Calls the above defined function and then selects only the requested properties from each returned object
$areaCodes = Get-AreaCodes -AreasToRetrieve $areasToRetrieve -StatusFilter "In Service" | Foreach { $_.NPA + $_.'CO Code (NXX)' }

#Saves the area codes into a file
$areaCodes | Set-Content .\areas.txt

If you need to work with other encodings in the text files, other than the default, you can specify the encoding by adding the -Encoding <encoding to use> parameter to the Get-Content and Set-Content function calls respectively.

如果您需要使用文本文件中的其他编码(默认值除外),则可以通过分别将-Encoding 参数添加到Get-Content和Set-Content函数调用来指定编码。

#2

DB - SQL Option

DB - SQL选项

Create 2 tables in your db

在db中创建2个表

• data
• regions

Import data from your csv text into data table (so create 6 columns, one of which is City) Import regions into the other table (2 columns > city, region)

将csv文本中的数据导入数据表(因此创建6列,其中一列为City)将区域导入另一个表(2列>城市,区域)

The use a simple INNER JOIN

使用简单的INNER JOIN

SELECT t.Column1, t.Column2, t.City, r.Region
FROM table1 t
INNER JOIN regions r ON t.City = r.City

Excel Option

• Import both your list into Excel. Use Data -> Text To Columns to create columns from CSV format.

将您的列表导入Excel。使用数据 - >文本到列来创建CSV格式的列。

• Order A->Z Region table (important) : For the LOOKUP function to work correctly, the Lookup_vector must be sorted in ascending order (A to Z or smallest to largest for numbers)

订单A-> Z区域表(重要):为了使LOOKUP功能正常工作,Lookup_vector必须按升序排序(A到Z或从最小到最大的数字)

• LOOKUP function to look for region

LOOKUP功能寻找区域

eg.

P.S. be careful about LOOKUP function: If the function cannot find an exact match for the Lookup_value, it chooses the largest value in the Lookup_vector that is less than or equal in value to the Lookup_value. So it's safer to add an IF and check if the city exists in the region table.

附:注意LOOKUP函数:如果函数找不到Lookup_value的完全匹配,它会在Lookup_vector中选择一个小于或等于Lookup_value值的最大值。因此,添加IF并检查区域表中是否存在城市更安全。